Topic 40 of 52
Analytical Window Separation
Overview
Window functions perform calculations across a set of rows related to the current row (a 'window'), without collapsing them into groups like GROUP BY does. Each row retains its individual identity while gaining access to aggregate calculations over a partition.
Syntax
sql
-- Window function syntax
function_name() OVER (
PARTITION BY column -- divides rows into groups (like GROUP BY)
ORDER BY column -- defines row order within each partition
ROWS/RANGE BETWEEN ... -- defines the window frame
)
-- Contrast with GROUP BY:
-- GROUP BY collapses rows → fewer rows in result
-- OVER (PARTITION BY) keeps all rows → same count as input
-- Ranking functions
ROW_NUMBER() OVER (...) -- unique sequential (1, 2, 3, 4...)
RANK() OVER (...) -- with gaps for ties (1, 2, 2, 4...)
DENSE_RANK() OVER (...) -- no gaps for ties (1, 2, 2, 3...)
NTILE(4) OVER (...) -- divide into N equal groups
-- Value functions
LAG(col, n) OVER (...) -- value from n rows before
LEAD(col, n) OVER (...) -- value from n rows after
FIRST_VALUE(col) OVER (...)
LAST_VALUE(col) OVER (...)
-- Aggregate as window
SUM(col) OVER (PARTITION BY cat ORDER BY date)Common Pitfalls
- Window functions are computed AFTER WHERE, GROUP BY, and HAVING — you cannot filter on window function results in WHERE (use a subquery or CTE).
- PARTITION BY divides rows into independent windows — like GROUP BY but without collapsing rows.
- Interview tip: Window functions are one of the most powerful SQL features and appear in virtually every data engineering and analyst interview.
Real-World Example
Sales dashboard with window functions showing rank, running total, and comparison:
example
sql
-- Complete sales analysis with window functions
SELECT
s.name AS salesperson,
o.region,
DATE_TRUNC('month', o.sale_date) AS month,
SUM(o.amount) AS monthly_sales,
-- Rank within region for each month
RANK() OVER (
PARTITION BY o.region, DATE_TRUNC('month', o.sale_date)
ORDER BY SUM(o.amount) DESC
) AS regional_rank,
-- Running total for the year per salesperson
SUM(SUM(o.amount)) OVER (
PARTITION BY s.id
ORDER BY DATE_TRUNC('month', o.sale_date)
) AS ytd_sales,
-- Compare to previous month
LAG(SUM(o.amount)) OVER (
PARTITION BY s.id
ORDER BY DATE_TRUNC('month', o.sale_date)
) AS prev_month_sales
FROM orders o
JOIN salespeople s ON o.salesperson_id = s.id
GROUP BY s.id, s.name, o.region, DATE_TRUNC('month', o.sale_date)
ORDER BY month, regional_rank;