SQL Window Functions Mastery
Go from basic aggregations to advanced analytical queries. Window functions are asked in almost every DE interview — master them here.
Your first window function: SUM() OVER (ORDER BY date) — the foundation for all running calculations.
MIN() OVER (PARTITION BY customer) — partition clause basics before combining with ORDER BY.
SUM() OVER (PARTITION BY region) — running totals within groups, the partitioned window pattern.
SUM() OVER () without PARTITION — grand total in denominator to compute % share per row.
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY date DESC) — the standard pattern to get the latest row per group.
ROW_NUMBER() minus date — the classic gap-and-island setup using row number subtraction to detect streaks.
RANK() vs DENSE_RANK() vs ROW_NUMBER() — know the difference and when each is correct.
Classic interview problem: use DENSE_RANK() to handle ties correctly instead of LIMIT/OFFSET.
Combine SUM() window with percentage calculation — common in product analytics interviews.
Frame clauses: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — precise window sizing.
RANK() OVER (PARTITION BY region ORDER BY spend DESC) + outer filter — the partitioned top-N pattern.
LAG() OVER (ORDER BY month) — access the previous row to compute growth rates without a self-join.
NTILE() and PERCENT_RANK() — rank users/products into buckets across groups.
LAG() / LEAD() for comparing adjacent rows — the building block for all gap-and-island problems.
DENSE_RANK() inside a CTE — filtering the top N per partition using a ranked subquery.
DENSE_RANK() with PARTITION BY — extend the single-table rank pattern to partitioned groups.
PERCENTILE_CONT/DISC and multiple window frames in a single query — hard-level composition.
LAG() with PARTITION BY year + percentage formula — multi-level window composition in a real business metric.
PERCENTILE_CONT(0.5) WITHIN GROUP — ordered-set aggregate functions, a distinct syntax from standard windows.
Gap-and-island with ROW_NUMBER() + GROUP BY island — the advanced streak detection pattern asked at Meta.
Cumulative SUM() over a flag column — advanced sessionization using window over boolean to assign session IDs.
Real-world retention analysis: self-join + window functions together. Asked at Amazon, Google, Meta.