Is there a performance advantage to using a lateral join over CTEs for this type of analysis?
Lateral joins are able to use "downstream" lateral join queries to optimize "upstream" ones?
Beyond that, LATERAL JOINs are really nice way to write out a subquery without a ton of nesting, as shown at https://ddrscott.github.io/blog/2017/what-the-sql-lateral/. (LATERAL can be applied to things in FROM! Especially useful given some set-returning functions can only be used in FROM.)
I think even though you can do most of these things using subqueries or CTEs, LATERAL makes it ergonomic to do them.
I'm hoping someone a little more knowledgable than I replies to this comment with some more details, as I know my knowledge on this particular subject is spotty.
That said laterals can be very useful, and it's still worth reading the article I'd you're not familiar with them.