LATERAL, CTEs, generate_series

Three tools solve what plain GROUP BY cannot: LATERAL runs a subquery or function per row, recursive CTEs walk hierarchies, and generate_series makes rows the data lacks, such as days on which nothing sold.

Subsections

• LATERAL Table Functions
• Top-N per Group
• Recursive CTE Rollups
• Calendars and Time Buckets
• Gap-Filling Time Series
• WITH ORDINALITY
• A Date Dimension