SQL
Conditional Aggregation, Pivot and Unpivot
Turning rows into columns and back again with portable SQL. Conditional aggregation, the PIVOT operator where it exists, and unpivoting with UNION ALL...
Advanced SQL
Top N per group, duplicates, gaps and islands, pivots and hierarchies.
6 notes
Notes
SQL
Top N Per Group
Three ways to get the best row in each group - window function, correlated subquery and lateral join - and how to pick between them.
SQL
Finding and Removing Duplicates
Detect duplicate rows on any definition of "duplicate", inspect them safely, delete all but one, and add the constraint that stops them coming back.
SQL
Gaps and Islands, and Comparing Adjacent Rows
Finding missing values in a sequence and grouping consecutive runs together - the pattern behind streak counting, session detection and attendance rep...
SQL
Hierarchical Data and Recursive Queries
Storing and querying trees: the adjacency list, walking it with a recursive CTE, and the materialised path and closure table alternatives.
SQL
Advanced Date Based Analysis
Period over period comparison, cohort retention, complete date ranges with no missing buckets, and rolling windows over time.