SQL Window Functions for Analysts

Window functions are the skill that most reliably separates a strong data analyst from a beginner. GROUP BY answers "what is the total per region" but destroys the individual rows. Window functions answer a different, richer class of question: "rank each customer within their region", "show every order next to a running total", "compare each sale to its region's average" — all while keeping every original row on screen. Dashboards depend on them, and interviews probe them hard.

This tutorial assumes you know GROUP BY and HAVING and are comfortable with CTEs, which you will use to filter window results. It sits near the top of the SQL for analytics path.

The core idea: summarize without collapsing

Here is the sample data:

-- orders
order_id | customer | region | amount
---------+----------+--------+-------
1001     | Aarti    | South  | 1200
1002     | Bhaskar  | South  | 8500
1003     | Chitra   | West   | 1200
1004     | Devan    | West   | 2100
1005     | Esha     | South  | 600

A GROUP BY region collapses these five rows into two. A window function instead adds a column to all five rows. To show each order next to its region's total:

SELECT customer, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM orders;

customer | region | amount | region_total
---------+--------+--------+-------------
Aarti    | South  | 1200   | 10300
Bhaskar  | South  | 8500   | 10300
Esha     | South  | 600    | 10300
Chitra   | West   | 1200   | 3300
Devan    | West   | 2100   | 3300

Every row survives, and each carries its region's total. The OVER (PARTITION BY region) clause is the whole trick: it tells the aggregate to compute per region but not to collapse the rows. From here you can compute each order's share of its region — amount / region_total — something GROUP BY alone cannot produce in one pass.

Ranking functions

The ranking family assigns positions within each partition. "Rank orders by amount within each region":

SELECT customer, region, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num,
       RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rnk
FROM orders;

The three differ only on ties. ROW_NUMBER always gives distinct numbers. RANK gives ties the same number then skips (1, 1, 3). DENSE_RANK ties without skipping (1, 1, 2). Choosing the right one is a common interview point: for "top 3 per group" where ties should all count, DENSE_RANK behaves differently from ROW_NUMBER, and picking wrong changes the report.

Top N per group

Window functions solve the question LIMIT cannot: "the top 2 orders in each region". Because you cannot filter a window function in WHERE, wrap it in a CTE and filter outside:

WITH ranked AS (
    SELECT customer, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM orders
)
SELECT customer, region, amount
FROM ranked
WHERE rn <= 2;

This CTE-plus-window pattern is one of the most valuable shapes in analytics. A plain ORDER BY ... LIMIT gives the global top 2; only a partitioned window gives the top 2 within each group.

Running totals and moving averages

Add ORDER BY inside OVER and the window accumulates. A running total of revenue by date:

SELECT order_id, amount,
       SUM(amount) OVER (ORDER BY order_id) AS running_total
FROM orders;

order_id | amount | running_total
---------+--------+--------------
1001     | 1200   | 1200
1002     | 8500   | 9700
1003     | 1200   | 10900
1004     | 2100   | 13000
1005     | 600    | 13600

Each row's total includes itself and everything before it. Add PARTITION BY customer to get a per-customer running total. Moving averages use the same mechanism with an explicit frame, such as AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) for a 3-row moving average — the backbone of trend smoothing in time-series dashboards.

Comparing rows: LAG and LEAD

LAG and LEAD reach to the previous or next row, which is how month-over-month change is computed:

SELECT order_id, amount,
       LAG(amount) OVER (ORDER BY order_id) AS prev_amount,
       amount - LAG(amount) OVER (ORDER BY order_id) AS change
FROM orders;

This gives each order's amount alongside the prior one and the difference — the pattern behind "growth versus last period", one of the most-requested dashboard metrics.

Common mistakes

• Filtering a window function in WHERE. Windows are computed after WHERE, so WHERE row_num = 1 fails. Wrap the query in a CTE or subquery and filter there.
• Confusing RANK and ROW_NUMBER on ties. Using ROW_NUMBER for "top N" arbitrarily breaks ties, hiding legitimately tied rows. Use RANK or DENSE_RANK when ties should share a position.
• Forgetting PARTITION BY. Without it the window spans the entire result set, so your "per region" calculation silently becomes an overall one.
• Omitting ORDER BY in a running total. A windowed SUM without ORDER BY returns the full partition total on every row, not an accumulating figure.

In interviews

Window functions are the deciding round of many analyst SQL interviews. Expect "top 3 products per category", "running monthly revenue total", "each employee's salary rank within their department" and "month-over-month growth using LAG". The near-universal follow-up is "why can't you filter ROW_NUMBER in WHERE?" — the answer is execution order, and the fix is a CTE. Demonstrating the CTE-plus-ROW_NUMBER pattern for top-N-per-group signals genuine competence.

Where this fits in your learning path

Window functions extend GROUP BY and HAVING by summarizing without collapsing rows, and they pair inseparably with CTEs for filtering ranked results. They also do the fair "top N per group" ranking that ORDER BY and LIMIT cannot. Mastering them is often the final SQL milestone on the data analyst roadmap.