Window Functions

Window functions (MySQL 8.0+) perform calculations across a set of rows without collapsing them — unlike GROUP BY, every input row stays in the output.

Syntax

function() OVER (
  [PARTITION BY columns]
  [ORDER BY columns]
  [frame_clause]
)

Ranking functions

SELECT
  name_en, department, cgpa,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY cgpa DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY cgpa DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY cgpa DESC) AS dense_rnk,
  NTILE(4)     OVER (ORDER BY cgpa DESC) AS quartile
FROM students;

Function	On a tie (3.9, 3.9, 3.8)
ROW_NUMBER()	1, 2, 3 — always distinct
RANK()	1, 1, 3 — gaps after ties
DENSE_RANK()	1, 1, 2 — no gaps

Aggregates as window functions

SELECT
  name_en, department, cgpa,
  AVG(cgpa) OVER (PARTITION BY department)              AS dept_avg,
  cgpa - AVG(cgpa) OVER (PARTITION BY department)       AS diff_from_avg,
  COUNT(*)  OVER (PARTITION BY department)              AS dept_size,
  MAX(cgpa) OVER ()                                     AS overall_best
FROM students;

Before MySQL 8 this needed a self-join to a GROUP BY subquery.

Running totals and moving averages

SELECT
  sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(amount) OVER (ORDER BY sale_date
                    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS moving_avg_7d
FROM sales;

Frame clauses: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, ROWS BETWEEN n PRECEDING AND n FOLLOWING, RANGE BETWEEN …. If you write ORDER BY without a frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Comparing rows: LAG and LEAD

SELECT
  sale_date, amount,
  LAG(amount)  OVER (ORDER BY sale_date) AS prev_day,
  LEAD(amount) OVER (ORDER BY sale_date) AS next_day,
  amount - LAG(amount) OVER (ORDER BY sale_date) AS change,
  ROUND(100.0 * (amount - LAG(amount) OVER (ORDER BY sale_date))
        / NULLIF(LAG(amount) OVER (ORDER BY sale_date), 0), 1) AS pct_change
FROM sales;

FIRST_VALUE(), LAST_VALUE() and NTH_VALUE() pick a specific row from the window.

Named windows

Define a window once and reuse it:

SELECT
  name_en,
  RANK()       OVER w AS rnk,
  DENSE_RANK() OVER w AS dense_rnk,
  AVG(cgpa)    OVER w AS dept_avg
FROM students
WINDOW w AS (PARTITION BY department ORDER BY cgpa DESC);

Top-N per group

The canonical use case — window functions cannot appear in WHERE, so wrap the query in a CTE:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY cgpa DESC) AS rn
  FROM students
)
SELECT department, name_en, cgpa FROM ranked WHERE rn <= 3 ORDER BY department, rn;

Deduplication

WITH dupes AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
  FROM students WHERE email IS NOT NULL
)
DELETE FROM students WHERE id IN (SELECT id FROM dupes WHERE rn > 1);