SQL
Advanced Practice: Subqueries, CTEs and Window Functions
Twelve harder exercises - correlated subqueries, recursive CTEs, ranking, running totals and top N per group - with solutions and the reasoning behind...
#Window Functions
Calculations across a window of rows.
8 notes tagged
SQL
Advanced Date Based Analysis
Period over period comparison, cohort retention, complete date ranges with no missing buckets, and rolling windows over time.
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
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
Frames, Running Totals and Moving Averages
The frame clause decides which rows each calculation sees. Learn ROWS versus RANGE, running totals, moving averages, and when a window beats GROUP BY.
SQL
LAG, LEAD, FIRST_VALUE and LAST_VALUE
Reading other rows from the current row: the previous value, the next value, and the first or last in the window - with the LAST_VALUE frame trap.
SQL
ROW_NUMBER, RANK, DENSE_RANK and NTILE
The four ranking functions, how each handles ties, and the top N per group pattern that makes ROW_NUMBER the most used window function in SQL.
SQL
OVER, PARTITION BY and Window ORDER BY
Window functions compute across related rows without collapsing them. Learn the OVER clause, PARTITION BY, window ORDER BY, and how they differ from G...