Thanks for confirming, @d.s.cobb! That's exactly the faulty behavior I had reported via Apple Feedback.
Here, it's a Mac Mini M2 Pro with a Dell UltraSharp 40" monitor (U4021QW). And that turned out to be the problem.
As suggested by Dell, I had installed Dell's DDPM software, and allowed it to open automatically on startup. That software has a feature called EasyArrange which allows, among other things, window tiling just like in Sequoia. I've never used it, though, and wasn't aware it was seemingly turned on (maybe it wasn't). For more than a year, it hasn't caused any issues.
Now that changed when updating to Sequoia. Don't ask me how this is possible, but the new macOS window tiling feature seems to have enabled the DDPM EasyArrange feature, with no way of disabling it.
From there on, fixing the issue was easy: Quit DDPM, and the nasty behavior immediately goes away. Go to Settings, Login Items & Extensions, Open at Login, and remove DDPM, so that it doesn't load on startup. Problem solved. This also explains why most people in this thread didn't encounter the problem the way I did.
If you don't use a Dell monitor, it might be your monitor vendor's software that macOS Sequoia plays fancy tricks with. Good luck sorting it out! (I'll post an addendum on Apple Feedback so they're aware of this problem.)