Topic 39 of 52 · Advanced
Functions Reference: Advanced, Window & JSON
Ranking, offset and value window functions plus MySQL 8 JSON helpers, with frames explained.
Concept
A window function computes a value across a set of related rows (the window) without collapsing them. The OVER clause defines it: PARTITION BY splits rows into groups, ORDER BY sets the order inside each group, and an optional frame (ROWS BETWEEN ...) narrows it further.
Ranking functions number rows; offset functions (LAG, LEAD) read neighbouring rows; value functions (FIRST_VALUE, LAST_VALUE) read the edge of the frame.
The default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which is why LAST_VALUE often just returns the current row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the real last value.
MySQL 8 stores JSON natively. JSON_EXTRACT (or the -> and ->> operators) reads values by path, and JSON_ARRAY / JSON_OBJECT build JSON from columns.
Cheat sheet
| Function | What it does | Example → result |
|---|---|---|
| ROW_NUMBER() OVER(w) | Unique 1, 2, 3 … per partition (ties broken arbitrarily) | scores 90,90,80 → 1,2,3 |
| RANK() OVER(w) | Rank with gaps after ties | 90,90,80 → 1,1,3 |
| DENSE_RANK() OVER(w) | Rank without gaps | 90,90,80 → 1,1,2 |
| NTILE(n) OVER(w) | Split ordered rows into n near-equal buckets | NTILE(4) → quartile 1–4 |
| PERCENT_RANK() / CUME_DIST() | Relative position 0–1 | Top 10% customers |
| LAG(col, n, default) OVER(w) | Value n rows before | LAG(revenue) → previous month |
| LEAD(col, n, default) OVER(w) | Value n rows after | LEAD(login_date) → next login |
| FIRST_VALUE(col) OVER(w) | First value in the frame | First order amount per customer |
| LAST_VALUE(col) OVER(w ROWS … UNBOUNDED FOLLOWING) | Last value in the frame | Latest status per ticket |
| NTH_VALUE(col, n) OVER(w) | n-th value in the frame | Second highest salary |
| SUM/AVG(col) OVER(w) | Running or moving aggregate | 7-day moving average |
| JSON_EXTRACT(doc, '$.path') | Read a value by path | JSON_EXTRACT('{"a":{"b":5}}','$.a.b') → 5 |
| doc->>'$.path' | Extract and unquote | info->>'$.city' → Pune |
| JSON_ARRAY(v1, v2, …) | Build a JSON array | JSON_ARRAY(1,'a',NULL) → [1, "a", null] |
| JSON_OBJECT(k1, v1, …) | Build a JSON object | JSON_OBJECT('id',7) → {"id": 7} |
| JSON_CONTAINS / JSON_LENGTH | Test membership / count items | JSON_LENGTH('[1,2,3]') → 3 |
| JSON_TABLE(doc, path COLUMNS …) | Turn a JSON array into rows | Explode order items |
Code (SQL · MySQL 8)
-- Top 2 earners per department (ties share a rank)
SELECT * FROM (
SELECT dept, name, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk <= 2;
-- Month-over-month growth and first / last value
SELECT month, revenue,
revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS change_amt,
NTILE(4) OVER (ORDER BY revenue DESC) AS quartile,
FIRST_VALUE(revenue) OVER (ORDER BY month) AS first_month,
LAST_VALUE(revenue) OVER (ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_month,
AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM monthly_sales;
-- JSON helpers
SELECT id,
info->>'$.address.city' AS city,
JSON_EXTRACT(info, '$.skills[0]') AS first_skill,
JSON_OBJECT('id', id, 'name', name, 'tags', JSON_ARRAY('sql', 'java')) AS api_payload
FROM candidates;Remember
- ROW_NUMBER never ties; RANK leaves gaps; DENSE_RANK doesn't.
- Window functions can't be used directly in WHERE — wrap them in a subquery or CTE.
- LAST_VALUE needs an explicit full frame to behave as expected.
- ->> unquotes JSON text; -> returns JSON.
Interview questions
Find the second highest salary per department.
Use DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) in a subquery and keep rows where the rank = 2. DENSE_RANK handles ties correctly.
Why does LAST_VALUE return the current row?
With ORDER BY the default frame ends at CURRENT ROW. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
How do you remove duplicate rows keeping the latest?
ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) and delete or filter out rows where the number is > 1.
LAG vs self-join for previous row?
LAG reads the previous row in one pass; a self-join needs a matching condition and is slower and harder to read.
Lesson test
Take an 8-question test on “Functions Reference: Advanced, Window & JSON” — concepts, output prediction, bug spotting and interview questions.