← SQL roadmap

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

FunctionWhat it doesExample → 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 ties90,90,80 → 1,1,3
DENSE_RANK() OVER(w)Rank without gaps90,90,80 → 1,1,2
NTILE(n) OVER(w)Split ordered rows into n near-equal bucketsNTILE(4) → quartile 1–4
PERCENT_RANK() / CUME_DIST()Relative position 0–1Top 10% customers
LAG(col, n, default) OVER(w)Value n rows beforeLAG(revenue) → previous month
LEAD(col, n, default) OVER(w)Value n rows afterLEAD(login_date) → next login
FIRST_VALUE(col) OVER(w)First value in the frameFirst order amount per customer
LAST_VALUE(col) OVER(w ROWS … UNBOUNDED FOLLOWING)Last value in the frameLatest status per ticket
NTH_VALUE(col, n) OVER(w)n-th value in the frameSecond highest salary
SUM/AVG(col) OVER(w)Running or moving aggregate7-day moving average
JSON_EXTRACT(doc, '$.path')Read a value by pathJSON_EXTRACT('{"a":{"b":5}}','$.a.b') → 5
doc->>'$.path'Extract and unquoteinfo->>'$.city' → Pune
JSON_ARRAY(v1, v2, …)Build a JSON arrayJSON_ARRAY(1,'a',NULL) → [1, "a", null]
JSON_OBJECT(k1, v1, …)Build a JSON objectJSON_OBJECT('id',7) → {"id": 7}
JSON_CONTAINS / JSON_LENGTHTest membership / count itemsJSON_LENGTH('[1,2,3]') → 3
JSON_TABLE(doc, path COLUMNS …)Turn a JSON array into rowsExplode 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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Functions Reference: Advanced, Window & JSON” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.