Go beyond the basics with subqueries, CTEs, window functions and the analytics patterns analysts use every day, practised in your browser.

SQL for Analysis

## Why it matters

"Top 3 products in each category." "Each customer's most recent order." "Remove duplicate rows but keep the newest one." These are some of the most common requests an analyst gets, and `GROUP BY` cannot answer them, because grouping collapses rows and you lose the detail. **Window functions** compute a value across a set of related rows while keeping every row. Ranking functions are the place to start.

## The core idea: OVER (...)

A window function is any function followed by `OVER (...)`. The brackets describe the "window" of rows it looks at:

- `PARTITION BY col` splits rows into groups (like `GROUP BY`, but rows are not collapsed).
- `ORDER BY col` sets the order inside each partition.

```sql
SELECT category, name, revenue,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
FROM products;
```

| category | name | revenue | rn |
|---|---|---|---|
| Footwear | Road Shoe | 22100 | 1 |
| Footwear | Trail Shoe | 18400 | 2 |
| Footwear | Hiking Boot | 18400 | 3 |
| Footwear | Sandal | 6300 | 4 |
| Gear | Day Pack | 13900 | 1 |
| Gear | Headlamp | 7600 | 2 |

The numbering restarts at 1 for each category. Every product row is still there.

## ROW_NUMBER, RANK and DENSE_RANK

The three differ only in how they treat **ties** (Trail Shoe and Hiking Boot both have 18,400):

| name | revenue | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| Road Shoe | 22100 | 1 | 1 | 1 |
| Trail Shoe | 18400 | 2 | 2 | 2 |
| Hiking Boot | 18400 | 3 | 2 | 2 |
| Sandal | 6300 | 4 | 4 | 3 |

- `ROW_NUMBER()` always gives distinct numbers. Which tied row gets 2 and which gets 3 is **arbitrary** unless you add a tie-breaker, such as `ORDER BY revenue DESC, name`.
- `RANK()` gives ties the same rank and then **skips** (1, 2, 2, 4), like a race where two runners share silver and nobody gets bronze.
- `DENSE_RANK()` gives ties the same rank **without gaps** (1, 2, 2, 3).

Choose by the business rule: "exactly one row per group" means `ROW_NUMBER`; "top 2, and include ties" means `RANK` or `DENSE_RANK`.

## Filtering on a rank: use a CTE

You cannot write `WHERE rn <= 2` in the same query, because `WHERE` runs **before** window functions are computed. Compute the rank in a CTE, then filter:

```sql
WITH ranked AS (
  SELECT category, name, revenue,
         RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS revenue_rank
  FROM products
)
SELECT category, name, revenue, revenue_rank
FROM ranked
WHERE revenue_rank <= 2;
```

This "rank in a CTE, filter outside" shape is the standard top-N-per-group pattern. (Some warehouses such as Snowflake, BigQuery and Databricks offer a `QUALIFY` clause that filters window results directly; SQLite and PostgreSQL do not.)

## Two more everyday uses

**Latest row per entity.** Each customer's most recent order:

```sql
WITH ordered AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY order_date DESC, id DESC) AS rn
  FROM orders AS o
)
SELECT * FROM ordered WHERE rn = 1;
```

**De-duplication.** If a load inserted the same record twice, keep one copy by ranking within the duplicate key and keeping `rn = 1`. The `id DESC` tie-breaker makes the choice repeatable.

## Without PARTITION BY

Leave out `PARTITION BY` and the whole result is one window, so you get an overall ranking:

```sql
SELECT name, revenue,
       DENSE_RANK() OVER (ORDER BY revenue DESC) AS overall_rank
FROM products;
```

Also useful: `NTILE(4) OVER (ORDER BY revenue DESC)` splits rows into four roughly equal buckets (quartiles), and `PERCENT_RANK()` gives a 0 to 1 position.

## Window functions vs GROUP BY

| `GROUP BY` | window function |
|---|---|
| one output row per group | one output row per input row |
| detail columns must be aggregated | detail columns stay as they are |
| filter groups with `HAVING` | filter results in an outer query |

You can combine them: a window function can run over an already grouped result, such as ranking regions by `SUM(amount)`. Window functions run after `GROUP BY` and `HAVING`, and before the final `ORDER BY` and `LIMIT`.

## Common mistakes

- **Filtering a window function in `WHERE`** of the same query: an error. Wrap it in a CTE.
- **No tie-breaker with `ROW_NUMBER`**, so the "top" row changes between runs.
- **`ORDER BY revenue` instead of `ORDER BY revenue DESC`**, which ranks the smallest first.
- **Forgetting `PARTITION BY`** when you wanted a per-group rank.
- **Using `LIMIT 2`** for "top 2 per category": `LIMIT` applies to the whole result, not per group.

## Quick recap

- `function() OVER (PARTITION BY ... ORDER BY ...)` computes across related rows without collapsing them.
- `ROW_NUMBER` = unique numbers; `RANK` = ties share, then gaps; `DENSE_RANK` = ties share, no gaps.
- Top N per group: rank in a CTE, filter outside.

## Try it

Open the **Do** tab and return the top 2 revenue ranks per category, keeping ties.


Rank rows within groups with ROW_NUMBER, RANK and DENSE_RANK, and use the top-N-per-group pattern.

ROW_NUMBER and RANK

SQL Window Functions | Clearly Explained | PARTITION BY, ORDER BY, ROW_NUMBER, RANK, DENSE_RANK

SQL Window Function | How to write SQL Query using RANK, DENSE RANK, LEAD/LAG | SQL Queries Tutorial

## Why it matters

Daily numbers are noisy. A dashboard that shows "revenue so far this month" (a running total) or "7-day average sign-ups" (a moving average) tells a much clearer story than raw daily values. Before window functions, analysts did this with slow self-joins or in a spreadsheet. With aggregate window functions it is one line of SQL.

## Aggregates as window functions

Any normal aggregate (`SUM`, `AVG`, `COUNT`, `MIN`, `MAX`) becomes a window function when you add `OVER (...)`. With an empty window it sees every row:

```sql
SELECT sale_date, amount,
       SUM(amount) OVER () AS grand_total,
       ROUND(amount * 100.0 / SUM(amount) OVER (), 1) AS pct_of_total
FROM daily_sales;
```

Every row keeps its own `amount` and also gets the grand total, which makes "share of total" calculations easy. Add `PARTITION BY store` and each store gets its own total.

## Running totals: add ORDER BY

When the window has an `ORDER BY`, the aggregate only looks at rows **up to the current one**:

```sql
SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales
ORDER BY sale_date;
```

| sale_date | amount | running_total |
|---|---|---|
| 2026-05-01 | 120 | 120 |
| 2026-05-02 | 95 | 215 |
| 2026-05-03 | 140 | 355 |
| 2026-05-04 | 80 | 435 |

Use `PARTITION BY` to restart the total, for example per month: `SUM(amount) OVER (PARTITION BY substr(sale_date, 1, 7) ORDER BY sale_date)` gives month-to-date revenue.

## Window frames: exactly which rows count

The **frame** says which rows around the current one are included. You write it after `ORDER BY`:

```sql
ROWS BETWEEN <start> AND <end>
```

where start and end can be `UNBOUNDED PRECEDING`, `n PRECEDING`, `CURRENT ROW`, `n FOLLOWING` or `UNBOUNDED FOLLOWING`.

A 3-day moving average is "this row and the two before it":

```sql
SELECT sale_date, amount,
       ROUND(AVG(amount) OVER (ORDER BY sale_date
                               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM daily_sales
ORDER BY sale_date;
```

| sale_date | amount | moving_avg_3 |
|---|---|---|
| 2026-05-01 | 120 | 120.0 |
| 2026-05-02 | 95 | 107.5 |
| 2026-05-03 | 140 | 118.33 |
| 2026-05-04 | 80 | 105.0 |

The first two rows average fewer than three values, because there are no earlier rows. If your report should only show complete windows, add `COUNT(*) OVER (same window)` and hide rows where it is below 3.

## ROWS vs RANGE: the hidden default

When you write `ORDER BY` without a frame, the SQL standard default is:

```sql
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
```

`RANGE` treats rows with the **same** `ORDER BY` value as one step. If two sales share the same date, both rows get the total including both of them, so the running total "jumps" on ties. `ROWS` counts physical rows one at a time. For a running total on a column with possible duplicates, either:

- write the frame explicitly: `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` plus a tie-breaker such as `ORDER BY sale_date, id`, or
- aggregate to one row per day first (in a CTE), then run the window over the daily totals.

The second option is usually what the business wants anyway.

## Moving windows need complete data

`ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` means "the previous 6 **rows**", not "the previous 6 **days**". If a day with no sales is simply missing from the table, the window silently stretches over 8 or 9 calendar days. Fix gaps first: generate every date (a calendar table or the recursive CTE from the CTE lesson), `LEFT JOIN` your data onto it, and use `COALESCE(amount, 0)`. PostgreSQL also supports `RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW`, which works on actual dates.

## Common mistakes

- **Empty `OVER ()` when you wanted a running total.** Without `ORDER BY` you get the grand total on every row.
- **Relying on the default `RANGE` frame** when the order column has ties.
- **Moving averages over missing dates**, which quietly cover the wrong period.
- **Expecting the window `ORDER BY` to sort the output.** It only defines the calculation. Add a final `ORDER BY` for the display order.
- **Integer division** when computing percentages: use `100.0`, not `100`, in SQLite and PostgreSQL.

## Quick recap

- `SUM(x) OVER ()` = grand total on each row; add `PARTITION BY` for per-group totals.
- `SUM(x) OVER (ORDER BY d)` = running total.
- Frames (`ROWS BETWEEN n PRECEDING AND CURRENT ROW`) define moving windows.
- Fill date gaps and use explicit `ROWS` frames to avoid surprises.

## Try it

Open the **Do** tab and add a running total and a 3-day moving average to a daily sales table.


Turn SUM and AVG into window functions to compute running totals, shares of total and moving averages with window frames.

Running totals and moving averages

Running & Rolling Total | Moving & Rolling Average |  SQL Key Data Analysis Concepts

## Why it matters

"Revenue is 51,800 this month" means little on its own. "Revenue is up 17.5% on last month" is a headline. Period-over-period change (day over day, month over month, year over year) is in nearly every business report, and so are related questions: how many days between a customer's orders, or did a sensor reading jump compared with the previous one? `LAG` and `LEAD` answer all of these by letting a row look at its neighbours.

## The core idea

- `LAG(col)` returns `col` from the **previous** row in the window order.
- `LEAD(col)` returns `col` from the **next** row.

```sql
SELECT month, revenue,
       LAG(revenue)  OVER (ORDER BY month) AS prev_revenue,
       LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM monthly_revenue
ORDER BY month;
```

| month | revenue | prev_revenue | next_revenue |
|---|---|---|---|
| 2026-01 | 42000 | NULL | 45500 |
| 2026-02 | 45500 | 42000 | 44100 |
| 2026-03 | 44100 | 45500 | 51800 |
| 2026-04 | 51800 | 44100 | NULL |

The first row has no previous row, so `LAG` returns `NULL`; the last row has no next row.

Both functions take two optional extra arguments: `LAG(col, offset, default)`. `LAG(revenue, 12)` looks 12 rows back (year over year on monthly data), and `LAG(revenue, 1, 0)` returns 0 instead of `NULL` when there is no earlier row. Be careful with the default: a 0 makes the first month look like infinite growth.

## Month-over-month change

Compute the previous value in a CTE, then do the arithmetic. That keeps each expression short:

```sql
WITH with_prev AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT month,
       revenue,
       revenue - prev_revenue AS change,
       ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 1) AS pct_change
FROM with_prev
ORDER BY month;
```

| month | revenue | change | pct_change |
|---|---|---|---|
| 2026-01 | 42000 | NULL | NULL |
| 2026-02 | 45500 | 3500 | 8.3 |
| 2026-03 | 44100 | -1400 | -3.1 |
| 2026-04 | 51800 | 7700 | 17.5 |

The percentage formula is **(new - old) / old × 100**. Two details matter:

- **`100.0`, not `100`.** In SQLite and PostgreSQL, dividing two integers gives an integer: `3500 * 100 / 42000` is 8, not 8.33.
- **Division by zero.** If last month was 0, the result is meaningless. SQLite returns `NULL`, PostgreSQL raises an error. Guard it with `NULLIF(prev_revenue, 0)` in the denominator, which turns a 0 into `NULL`.

## Per-entity comparisons with PARTITION BY

Add `PARTITION BY` so each customer, store or product is compared only with its own history:

```sql
-- Days since each customer's previous order
SELECT customer_id, order_date,
       julianday(order_date)
         - julianday(LAG(order_date) OVER (PARTITION BY customer_id
                                           ORDER BY order_date)) AS days_since_prev
FROM orders;
```

`julianday` turns a date string into a day number in SQLite (PostgreSQL would subtract dates directly). Without `PARTITION BY`, the first order of customer 2 would be compared with the last order of customer 1, which is nonsense.

## Year over year needs complete periods

`LAG(revenue, 12)` assumes exactly one row per month with no gaps. If a month is missing, "12 rows back" is 13 months back. For reliable comparisons:

1. Aggregate to one row per period in a CTE (for example `GROUP BY substr(order_date, 1, 7)`).
2. Make sure every period exists (left-join onto a calendar if needed).
3. Then apply `LAG`.

## FIRST_VALUE and LAST_VALUE

Related functions compare with a fixed point instead of a neighbour. `FIRST_VALUE(revenue) OVER (ORDER BY month)` returns January's revenue on every row, so you can show "growth since the start of the year". `LAST_VALUE` needs an explicit frame (`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`), because the default frame stops at the current row; this catches many people out.

## Common mistakes

- **Mixing up LAG and LEAD.** LAG looks back (previous), LEAD looks forward (next).
- **Missing `PARTITION BY`**, so values leak between customers or stores.
- **Integer division** and **dividing by zero** in percentage change.
- **Running LAG on raw transactions** instead of on one row per period.
- **Filtering before the window.** `WHERE month >= '2026-03'` in the same query removes February, so March's `prev_revenue` becomes `NULL`. Filter in an outer query after computing `LAG`.

## Quick recap

- `LAG` = previous row, `LEAD` = next row, ordered by the window's `ORDER BY`.
- Percent change = `(new - old) * 100.0 / NULLIF(old, 0)`.
- Use `PARTITION BY` for per-entity history and aggregate to one row per period first.
- Filter after the window function, not before.

## Try it

Open the **Do** tab and build a month-over-month revenue report with `LAG`.


Compare each row with the previous or next one to compute month-over-month change and gaps between events.

LAG and LEAD

LAG and LEAD Functions (SQL) - Accessing Data in Other Rows

Month Over Month Revenue Growth Rate | LAG() + Growth Formula Explained | SQL Interview #37

Rank rows within groups, compute running totals and moving averages, and measure period-over-period change, all without losing row detail.