Window Functions

Introduction

Window functions compute across related rows without collapsing groups like GROUP BY. Rank posts per user, compare each row to the previous value, or build running totals—all while keeping every input row visible. Syntax closely matches MySQL window functions; PostgreSQL adds FILTER on window aggregates in some contexts and standard QUALIFY is not built in—use a subquery wrapper.

Prerequisites

Subqueries and Aggregates
SELECT Queries Basics
Sample data in users and posts

ROW_NUMBER and Ranking

Number posts per user by publish date:

sql

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

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

sql

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

LAG and LEAD

Previous publish date for the same user:

sql

LAG(col, 1, default) — offset and default when no prior row.

Days since last post:

sql

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

Running Totals

Daily publish count with cumulative sum:

sql

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

Running average per user:

sql

SELECT
  user_id,
  title,
  published_at,
  AVG(length(body)) OVER (
    PARTITION BY user_id
    ORDER BY published_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_avg_body_len
FROM posts
WHERE published_at IS NOT NULL;

OVER Clause Parts

sql

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.

Frame examples:

Frame	Meaning
`ROWS UNBOUNDED PRECEDING`	From start of partition to current row
`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`	Sliding window of 3 rows
`RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW`	Time-based (with ORDER BY timestamp)

Window vs GROUP BY

GROUP BY	Window
One row per group	One row per input row
HAVING filters groups	Filter with subquery (no QUALIFY in PostgreSQL)
*`COUNT()`** aggregate	*`COUNT() OVER ()`**

Top Row Per Group

Latest published post per user—classic ROW_NUMBER pattern:

sql

Alternative with DISTINCT ON—PostgreSQL-specific—SELECT chapter.

FIRST_VALUE and NTILE

First title in partition:

sql

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

Split users into four buckets by post count:

sql

SELECT user_id, post_count,
  NTILE(4) OVER (ORDER BY post_count DESC) AS quartile
FROM (
  SELECT user_id, COUNT(*) AS post_count FROM posts GROUP BY user_id
) t;

FILTER With Window Aggregates

PostgreSQL allows FILTER on aggregates used as windows:

sql

SELECT
  user_id,
  COUNT(*) FILTER (WHERE published_at IS NOT NULL) OVER (
    PARTITION BY user_id
  ) AS published_count,
  COUNT(*) OVER (PARTITION BY user_id) AS total_count
FROM posts;

E-commerce Style: Top N per Category (Preview)

Pattern used in E-commerce project:

sql

-- Assuming products(category_id, name, sales)
-- SELECT * FROM (
--   SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
--   FROM products
-- ) t WHERE rn <= 3;

FAQ

PostgreSQL vs MySQL window syntax?

Nearly identical OVER syntax—both follow SQL

windowing.

Performance?

Large PARTITION BY + ORDER BY sorts—index matching partition/order columns helps.

QUALIFY clause?

Not in PostgreSQL—wrap in subquery WHERE rn = 1.

DISTINCT inside OVER?

Use subquery first—COUNT(DISTINCT x) OVER (...) has restrictions; test your version.

Window in WHERE?

Not allowed—compute in subquery or CTE, filter outside.

NTILE for A/B splits?

NTILE(2) on random or hash column—reporting and sampling patterns.