A Tale of Two CTEs: A Postgresql Adventure

This will be a jaunty frolic through two SQL queries, diving deeply into the single most useful and reliable component in our modern software stack. On the way, we will sample a smorgasboard of postgresql tricks I found useful to do a thing I was trying to do.

• JSON querying
• Lateral joins
• date_trunc
• percentile_disc
• distinct on
• Non-recursive Common Table Expressions (CTEs), which are very different from…
• Recursive CTEs
• Loose index scans and
• A very simple look at how the query planner plans its queries

Of course, we can also talk about event sourcing since that's what everyone else is talking about too.