Don't have a suitable database to test on but I'm pretty sure standard SQL allows you to join on a subselect that limits its result to one.

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

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.