Window Functions ③ — Ranking, Analytic Functions, and Execution Order

Ranking — ROW_NUMBER / RANK / DENSE_RANK

The dedicated ranking functions are ROW_NUMBER() / RANK() / DENSE_RANK().

All three number rows in the order given by OVER (ORDER BY ...), but they handle ties (equal values) differently.

The SQL below — and the diagram that visualizes its result — walks through a concrete case with tied totals row by row.

-- Compute each employee's total sales, then rank in descending order of total in 3 ways
SELECT emp_id,
  SUM(amount) AS total,
  ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rn,
  RANK()       OVER (ORDER BY SUM(amount) DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dr
FROM sales
GROUP BY emp_id
ORDER BY total DESC;
-- You can first aggregate with GROUP BY and then rank those aggregates

Imagine you want a ranking of employees by total sales. There are ties, so you'd like to see all three side by side: consecutive numbering, skipping next ranks, and not skipping. (If your query runs correctly, an explanation will appear.)

① GROUP BY the sales table by emp_id and select SUM(amount) aliased total.

② Over a window ordered by descending total, add ROW_NUMBER() aliased rn, RANK() aliased rk, and DENSE_RANK() aliased dr.

③ The grader checks row order, so end with ORDER BY total DESC, emp_id (descending by total, ascending by emp_id for ties).

LAG / LEAD — pull values from the previous / next row

LAG(column) pulls the value from the previous row onto the current row, and LEAD(column) pulls from the next row (lag = to fall behind, lead = to go ahead).

OVER (PARTITION BY ... ORDER BY ...) decides what "previous" and "next" mean by setting the order.

Use them for period-over-period diffs (vs. previous sale), vs.-last-month deltas, or comparisons against the next scheduled event.

LAG(amount) returns NULL on the first row of each partition (there's no previous row); similarly LEAD is NULL on the last row.

The optional second argument controls offset ("how many rows back") and the third sets a default value when the target row is missing — e.g., LAG(amount, 1, 0).

Subtract with amount - LAG(amount) OVER (...) to get the diff vs. the previous row directly.

-- Show previous sale (LAG) and the diff vs. the previous sale
SELECT emp_id, sale_date, amount,
  LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS diff_prev
FROM sales
ORDER BY emp_id, sale_date;

-- Use LEAD if you want to show the next sale alongside the current row
SELECT emp_id, sale_date, amount,
  LEAD(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS next_amount
FROM sales
ORDER BY emp_id, sale_date;

Imagine you want a per-employee list of sales ordered by date, with the previous sale and the diff vs. that previous sale alongside each row.

① Select emp_id, sale_date, and amount from sales.

② Over a window partitioned per employee and ordered by sale_date, add the previous row's amount aliased prev_amount.

③ Also add amount minus the previous amount aliased diff_prev.

④ The grader checks row order, so end with ORDER BY emp_id, sale_date.

NTILE — split ordered rows into n evenly sized groups

NTILE(n) splits the rows ordered by OVER (ORDER BY ...) into n as-evenly-sized-as-possible groups, labeling each row with a group number from 1 to n.

Use it for quartiles, quintiles, "top 25%", and similar bucketing.

When the row count isn't divisible by n, the earlier groups get one extra row each (e.g., splitting 5 rows into 4 groups gives 2 / 1 / 1 / 1).

Combine with PARTITION BY to do things like "split each employee's sales into upper / lower buckets within that employee."

The diagram below shows what happens when emp_id=11's 5 sales are sorted by amount desc and split with NTILE(4).

-- Per employee, split that employee's sales into 4 groups by amount desc
SELECT emp_id, sale_date, amount,
  NTILE(4) OVER (PARTITION BY emp_id ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY emp_id, amount DESC;

-- Split all sales into 4 buckets by amount (top 25% has quartile = 1)
SELECT sale_id, emp_id, amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY amount DESC;

Imagine you want a list of sales split into 4 groups per employee (by amount desc), shown in quartile order from top group down.

① Select emp_id, sale_date, and amount from sales.

② Over a window partitioned per employee and ordered by amount desc, compute NTILE(4) and add it as a 4th column aliased quartile.

③ The grader checks row order, so end with `ORDER BY quartile, emp_id, sale_id` (ORDER BY can refer to the quartile alias computed in SELECT).

Imagine you want to filter down to just the top 25% (quartile = 1). Try adding WHERE quartile = 1 to the Exercise 3 query.

① Use the same SELECT from Exercise 3 and add WHERE quartile = 1 after FROM sales.

② Running this returns an error (an error means you've got it right).

③ The next section, "SQL execution order", explains why. quartile worked in ORDER BY but doesn't work in WHERE — keep that puzzle in mind as we move on.

SQL execution order — why you can't put a window function in WHERE

You might wonder, "can't I just filter on the rank I computed, like WHERE rk = 1?"

You'd get an error. The reason is SQL's logical execution order.

The order you write the clauses (SELECT → FROM → WHERE ...) is not the order they're evaluated.

Evaluation roughly goes: FROM / JOIN (assemble tables) → WHERE (filter rows) → GROUP BY (group) → HAVING (filter groups) → SELECT (compute columns — this is where window functions run) → ORDER BY (sort) → LIMIT (truncate).

Since WHERE runs before SELECT, it can't see the rank that SELECT is about to compute.

To filter on a rank, push the query that computes the window function into a subquery (or CTE) and apply WHERE on the outside.

The inner SELECT finishes computing the window function, and the outer query can then treat that result as just another column.

-- This errors: WHERE runs before SELECT, so rk doesn't exist yet
-- SELECT emp_id, RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
-- FROM sales GROUP BY emp_id WHERE rk <= 3;

-- Correct: compute the rank in an inner subquery, filter on the outside
SELECT * FROM (
  SELECT emp_id,
    SUM(amount) AS total,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
  FROM sales
  GROUP BY emp_id
) AS ranked
WHERE rk <= 3
ORDER BY rk, emp_id;

Imagine you want just the top 3 employees by total sales. Since you're filtering on a rank, you need a subquery to work around execution order.

① In the inner subquery, GROUP BY sales by emp_id and select SUM(amount) aliased total, plus RANK() OVER (ORDER BY SUM(amount) DESC) aliased rk. Alias the subquery ranked.

② Filter the outer query with WHERE rk <= 3.

③ The grader checks row order, so end with ORDER BY rk, emp_id (ascending by rk, then by emp_id for ties).