Assuming we start from the position of having a unique, never-before-seen password for each account (which is what we should all be doing, right?), rotating doe

Assuming we start from the position of having a unique, never-before-seen password for each account (which is what we should all be doing, right?), rotating doesn't do anything. And if we accept that most people don't use unique, never-before-seen passwords and that a password has been included in a plaintext dump, rotating passwords periodically doesn't even protect from password spray attacks, since someone has likely used that password before and you'll still be vulnerable.