[singron]

You probably want to use LATERAL JOIN, which will compute the subquery for each left hand row. Otherwise it's computed just once.

[cess11]

With SQL there are commonly several options available, and tradeoffs might not be obvious until it's tested against a real data set. Sometimes even an EXPLAIN that looks good still has some drawback in production, though in my experience it's very rare.

I like that though, I have a preference for REPL style development and fleshing things out interactively. Enough so that I build Java like that, hacking things out directly in tests.