Exercises — Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

==ROW_NUMBER()==. WHY: it is the only ranker that never repeats a number. RANK and DENSE_RANK both let equal values share a number; the requirement "no ties allowed" rules them out. So the answer is ROW_NUMBER() OVER (ORDER BY salary DESC).

==LAG(revenue, 1)== over ORDER BY day. WHY: LAG looks backward by an offset (default 1 row). "Previous day" = 1 row before in day-order. LEAD would look forward (next day) — wrong direction.

==DENSE_RANK()==. WHY: "distinct levels, no gaps" is literally the definition of DENSE_RANK — ties share a number and the next number is not skipped (1,1,2,3…). RANK would skip (1,1,3…) so <= 3 might return only two distinct levels. ROW_NUMBER treats tied people as different positions, so it counts people, not levels.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, id ASC) AS rn
FROM emp;

First put the rows in the exact window order salary DESC, id ASC. Salary decides first; when salaries tie, the smaller id comes first: 900(Ada,id1), 900(Bo,id2), 800(Ed,id5), 800(Fi,id6), 700(Cy,id3), 600(Gu,id7), 500(Di,id4). Now ROW_NUMBER just counts 1,2,3,… straight down that ordered line:

ROW_NUMBER just counts down the sorted line; the id ASC tie-break makes Ada beat Bo and Ed beat Fi deterministically.

SELECT name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS r,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM emp;

Apply the parent's formulas: $\text{RANK}(x)=1+|\{\text{rows strictly ahead}\}|$, $\text{DENSE\_RANK}(x)=1+|\{\text{distinct values strictly ahead}\}|$.

See how RANK jumps 1→3→5 (skips, because two rows sit ahead each time) but DENSE_RANK walks 1→2→3→4 (counts distinct salaries ahead). The figure below makes this concrete.

SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rn
FROM emp;

PARTITION BY dept resets the counter at each department boundary:

Notice dept_rn starts over at 1 for Sales — that is the whole point of PARTITION BY.

Why it fails: window functions are evaluated after WHERE in the logical query order (FROM → WHERE → GROUP BY → SELECT → window → ORDER BY). At the moment WHERE runs, the ROW_NUMBER simply does not exist yet — so you cannot filter on it. Fix: compute it in an inner query (or CTE), filter outside:

SELECT name, dept, salary
FROM (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM emp
) t
WHERE rn = 1;

Result: Ada (Eng, 900) and Ed (Sales, 800). Two rows — one per department.

SELECT day, revenue,
       LAG(revenue, 1, 0) OVER (ORDER BY day) AS prev,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM sales;

Day 1 has no predecessor → LAG returns the default 0, so delta = 100 - 0 = 100.

The 0 default is what stops day 1's delta from being NULL. Without it, 100 - NULL = NULL.

From L2.2, RANK over emp gives 1,1,3,3,5,6,7 for salaries 900,900,800,800,700,600,500. So RANK <= 3 keeps salaries 900 and 800 only — the 700 (which is the 3rd distinct level) has RANK 5 and is dropped. Wrong for "top 3 distinct levels". Correct — use DENSE_RANK (values 1,1,2,2,3,4,5), keep <= 3, i.e. salaries 900, 800, 700:

SELECT name, salary
FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM emp
) t
WHERE dr <= 3;

Returns 5 people: Ada, Bo (900), Ed, Fi (800), Cy (700). (Counts people, but only from the top 3 distinct levels — exactly the ask.)

Use an aggregate as a window function — AVG(...) OVER (PARTITION BY dept). Because it has no ORDER BY, the whole partition is one frame, so every row in a dept sees that dept's overall average, and (unlike GROUP BY) no rows collapse.

SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept) AS diff
FROM emp;

Eng salaries 900,900,700,500 → average $=(900+900+700+500)/4=3000/4=750$. Sales salaries 800,800,600 → average $=(800+800+600)/3=2200/3\approx 733.33$.

Every row keeps its identity and carries its dept average — the signature move of a window aggregate. Notice Sales diffs sum to $66.67+66.67-133.33=0$ (deviations from the mean always cancel) — a nice hand-check.

Adding ORDER BY day to a SUM() OVER (...) gives a running total — the default frame becomes "all rows from the start of the partition up to the current row" (see frames).

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM sales;

Revenues 100,130,130,90,150 accumulate:

The final running total (600) equals the plain grand total — a good sanity check.

"2nd-highest distinct" → DENSE_RANK, then keep = 2, then pick the salary (deduplicated).

WITH ranked AS (
  SELECT DISTINCT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM emp
)
SELECT salary FROM ranked WHERE dr = 2;

Distinct salaries DESC: 900(dr 1), 800(dr 2), 700(dr 3), 600(dr 4), 500(dr 5). So dr = 2 → 800.

Order by salary DESC inside each dept, then LEAD looks one row ahead — which is the next-lower salary. Default = salary handles the lowest-paid person (no row ahead): subtracting salary from itself gives gap 0.

SELECT name, dept, salary,
       LEAD(salary, 1, salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS next_lower,
       salary - LEAD(salary, 1, salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS gap
FROM emp;

Eng, sorted DESC → 900(Ada), 900(Bo), 700(Cy), 500(Di):

Ada's next row is Bo (also 900) → gap 0. Di is last in Eng → default kicks in → gap 0.

Sales, sorted DESC → 800(Ed), 800(Fi), 600(Gu):

Ed's next row is Fi (also 800) → gap 0. Gu is last in Sales → default kicks in → gap 0. Every gap is measured within its own department because of PARTITION BY dept.

Compute the per-department average as a window (keeps rows), then filter outside the CTE (you cannot filter on a window function inside WHERE — L3.1).

WITH w AS (
  SELECT name, dept, salary,
         AVG(salary) OVER (PARTITION BY dept) AS dept_avg
  FROM emp
)
SELECT name, dept, salary,
       salary - dept_avg AS above_by
FROM w
WHERE salary > dept_avg;

Eng avg = 750 → Ada(900), Bo(900) are above (+150 each); Cy(700), Di(500) are not. Sales avg = $2200/3\approx 733.33$ → Ed(800), Fi(800) above ($800-733.33=66.67$ each); Gu(600) not.

Every design decision earned: window (keep rows), PARTITION BY (per-dept average), CTE (so we can filter after the window computes), strictly > (excludes anyone exactly at the average).

Ranking on ties 900,900,700? ::: ROW_NUMBER 1,2,3 · RANK 1,1,3 · DENSE_RANK 1,1,2 Why filter window results in an outer query? ::: Windows are computed after WHERE in the logical order. Aggregate + keep every row? ::: Use AGG(...) OVER (...), not GROUP BY.