I basically have a private office right now (WFH, lol) and I'm the only person on the project right now, so for me it works. But previously I've always worked in teams, 3 - 8 people. The noises from one's own team is almost never noise, because it's pertinent to the project you all are working on (if everyone is working on something else you're not an actual team). The noises from other teams though, ugh.

Last job I had, we had "team rooms"; thankfully I was able to provide input and managed to steer them away from doing an open office. That is ideal, I think, if you've got teams working on different things.

I've worked in a fully open office with about 8 teams, that was too much.

I've worked in a half-open office which I was really sceptical about at first (we moved into it while I was working there), it wasn't just an open office but it was three floors with an open atrium in the center. But it worked, somehow; there wasn't too much noise coming from the atrium, and every team had their own area cordoned off with half-walls (whiteboard walls) so you could walk around them, but there was no visual distraction and noise was muted / indirect. I think that's the best way to do open offices. Wouldn't have minded if they put some noise insulation in between the whiteboard walls though.