Window Functions

Introduction

Window functions compute across related rows without collapsing groups like GROUP BY. Rank posts per user, compare row to previous value, or running totals—MySQL 8.0 added ROW_NUMBER, RANK, LAG, LEAD, and SUM() OVER. This chapter uses OVER and PARTITION BY on blog_dev data.

Prerequisites

• Group By and Aggregates
• SELECT Queries Basics
• MySQL 8.0+

ROW_NUMBER and Ranking

Number posts per user by publish date:

SELECT
  user_id,
  title,
  published_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY published_at DESC
  ) AS rn
FROM posts
WHERE published_at IS NOT NULL;

RANK() — ties share rank with gaps; DENSE_RANK() — no gaps:

SELECT
  user_id,
  COUNT(*) AS post_count,
  RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_by_volume
FROM posts
GROUP BY user_id;

Note: ranking over aggregate needs subquery or different shape—example with derived table:

SELECT user_id, post_count,
  RANK() OVER (ORDER BY post_count DESC) AS rnk
FROM (
  SELECT user_id, COUNT(*) AS post_count
  FROM posts
  GROUP BY user_id
) AS t;

LAG and LEAD

Previous post date for same user:

SELECT
  user_id,
  title,
  published_at,
  LAG(published_at) OVER (
    PARTITION BY user_id
    ORDER BY published_at
  ) AS previous_publish
FROM posts
WHERE published_at IS NOT NULL;

LEAD — next row value.

Running Total

SELECT
  DATE(published_at) AS day,
  COUNT(*) AS posts_that_day,
  SUM(COUNT(*)) OVER (
    ORDER BY DATE(published_at)
    ROWS UNBOUNDED PRECEDING
  ) AS running_total
FROM posts
WHERE published_at IS NOT NULL
GROUP BY DATE(published_at)
ORDER BY day;

OVER Clause Parts

function(args) OVER (
  PARTITION BY col1   -- optional: separate windows
  ORDER BY col2        -- defines frame order
  ROWS BETWEEN ...     -- frame clause (advanced)
)

PARTITION BY — like GROUP BY groups but keeps all rows visible.

Window vs GROUP BY

Top post per user without self-join:

Classic pattern before window functions used correlated subqueries.

FAQ

MySQL 5.7?

Window functions unavailable—upgrade to 8.0.

Performance?

Large partitions sort—index PARTITION BY + ORDER BY columns helps.

DISTINCT with OVER?

Careful syntax—often subquery first.

FIRST_VALUE / LAST_VALUE?

Same OVER frame—useful for pick first in group.

NTILE?

Split into N buckets—reporting quartiles.

FILTER clause?

MySQL lacks SQL standard FILTER—use CASE inside aggregate.