Kaushik's Notes

Window Functions

8 min read

Overview

  • Window Function: Computes a value across a set of related rows while keeping every row (unlike GROUP BY, which collapses them)
  • Syntax: function(...) OVER (PARTITION BY … ORDER BY … frame)
  • Families: Ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), offset (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), aggregate (sum, avg, count … OVER)
  • Evaluated: After WHERE/GROUP BY/HAVING, before ORDER BY/LIMIT — so you can’t filter on them in WHERE (see SELECT Fundamentals)
  • Sample Data: E-commerce Practice Queries

Anatomy of OVER()

sum(amount) OVER (
    PARTITION BY user_id           -- split rows into independent groups (optional)
    ORDER BY created_at            -- order inside each partition (needed for ranking/LAG/running totals)
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- frame: which rows feed the aggregate
)
PartPurposeIf Omitted
PARTITION BYRestart the calculation per groupWhole result is one partition
ORDER BYDefines sequence for rank/offset/running calcsRows unordered; aggregate covers whole partition
FrameSubset of the partition for aggregates/*_VALUERANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY present, else whole partition
WINDOW w AS (…)Name a window to reuseRepeat the definition
-- GROUP BY collapses; a window keeps every row and adds the group value
SELECT name, department, salary,
       round(avg(salary) OVER (PARTITION BY department))          AS dept_avg,
       round(salary - avg(salary) OVER (PARTITION BY department)) AS diff_from_avg,
       round(100 * salary / sum(salary) OVER (), 1)            AS pct_of_payroll
FROM employees
ORDER BY department, salary DESC;

Ranking Functions

ROW_NUMBER vs RANK vs DENSE_RANK

FunctionTiesGaps After TiesSalaries 150, 150, 120
ROW_NUMBER()Arbitrary unique numbersn/a1, 2, 3
RANK()Same rankYes1, 1, 3
DENSE_RANK()Same rankNo1, 1, 2
PERCENT_RANK()(rank − 1) / (rows − 1)—0, 0, 1
NTILE(n)Splits into n near-equal buckets—1, 1, 2 (n = 2)
SELECT name, department, salary,
       ROW_NUMBER() OVER w AS row_num,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense_rnk,
       NTILE(2)     OVER w AS half
FROM employees
WHERE department = 'Engineering'
WINDOW w AS (ORDER BY salary DESC)
ORDER BY salary DESC, name;

Top-N per Group

-- Top 2 earners per department (window must be filtered in an outer query)
SELECT department, name, salary
FROM (
    SELECT department, name, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk <= 2
ORDER BY department, rnk, name;
  • ROW_NUMBER: Exactly N rows per group, ties broken arbitrarily (add a tie-breaker column)
  • RANK / DENSE_RANK: May return more than N rows when ties exist
  • Alternatives: DISTINCT ON for N = 1, LATERAL … LIMIT N for large tables with an index (see Joins)

Classic: Nth Highest Salary

-- 2nd highest distinct salary overall (NULL if it doesn't exist)
SELECT (SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 1 LIMIT 1) AS second_highest;
 
-- With DENSE_RANK — generalises to "Nth per department"
SELECT department, name, salary
FROM (SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r FROM employees) t
WHERE r = 2;

Deduplication

-- Keep only the latest payment attempt per order; ROW_NUMBER marks the duplicates
SELECT order_id, id AS payment_id, status, paid_at
FROM (
    SELECT p.*, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY paid_at DESC, id DESC) AS rn
    FROM payments p
) t
WHERE rn = 1 AND order_id IN (3, 8)
ORDER BY order_id;
 
-- Delete duplicates pattern (here: would delete earlier attempts — rolled back)
BEGIN;
DELETE FROM payments
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY paid_at DESC) AS rn
        FROM payments
    ) t WHERE rn > 1
);
ROLLBACK;

Offset Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

FunctionReturnsDefault If Missing
LAG(x, n, default)Value from n rows beforeNULL (or default)
LEAD(x, n, default)Value from n rows afterNULL (or default)
FIRST_VALUE(x)First row of the frame—
LAST_VALUE(x)Last row of the frame (gotcha below)—
NTH_VALUE(x, n)n-th row of the frameNULL
-- Each customer's orders with previous/next order date and gap in days
SELECT user_id, id, created_at::date AS order_date,
       LAG(created_at::date)  OVER w                        AS prev_order,
       LEAD(created_at::date) OVER w                        AS next_order,
       created_at::date - LAG(created_at::date) OVER w      AS days_since_prev,
       FIRST_VALUE(created_at::date) OVER w                 AS first_order
FROM orders
WHERE user_id IN (1, 2)
WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
ORDER BY user_id, created_at;

The LAST_VALUE Gotcha

  • Problem: With ORDER BY, the default frame ends at the current row, so LAST_VALUE returns the current row’s value
  • Fix: Extend the frame to UNBOUNDED FOLLOWING (or use FIRST_VALUE with reversed order)
SELECT name, salary,
       LAST_VALUE(name) OVER (ORDER BY salary)                    AS wrong_lowest_paid_is_me,
       LAST_VALUE(name) OVER (ORDER BY salary
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_paid
FROM employees
WHERE department = 'Sales'
ORDER BY salary;

Aggregate Windows: Running Totals and Moving Averages

WITH daily AS (
    SELECT o.created_at::date AS day, sum(oi.quantity * oi.unit_price) AS revenue
    FROM orders o JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped', 'delivered')
    GROUP BY 1
)
SELECT day, revenue,
       sum(revenue) OVER (ORDER BY day)                                       AS running_total,
       round(avg(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_last_3,
       sum(revenue) OVER (PARTITION BY date_trunc('month', day) ORDER BY day) AS month_to_date,
       round(100 * revenue / sum(revenue) OVER (), 1)                         AS pct_of_total
FROM daily
ORDER BY day;

Frames: ROWS vs RANGE vs GROUPS

Frame Modes

ModeFrame Measured InTies (Peers)Example
ROWSPhysical rowsEach row separatelyROWS BETWEEN 2 PRECEDING AND CURRENT ROW = last 3 rows
RANGEValue distance on the ORDER BY keyPeers included togetherRANGE BETWEEN interval '7 days' PRECEDING AND CURRENT ROW = last 7 days
GROUPSPeer groupsGroups of tiesGROUPS BETWEEN 1 PRECEDING AND CURRENT ROW

Frame Bounds

  • UNBOUNDED PRECEDING / FOLLOWING: Partition start / end
  • n PRECEDING / FOLLOWING: Offset (rows, value, or groups depending on mode)
  • CURRENT ROW: In RANGE/GROUPS this means “the current row and its peers”
  • EXCLUDE CURRENT ROW / TIES / GROUP: Postgres 11+ frame exclusion

ROWS vs RANGE With Ties

-- Engineering has two 150000 salaries: RANGE treats them as peers and adds both at once
SELECT name, salary,
       sum(salary) OVER (ORDER BY salary ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_running,
       sum(salary) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_running
FROM employees
WHERE department = 'Engineering'
ORDER BY salary, name;
  • Takeaway: The default frame is RANGE, so a “running total” over a non-unique key jumps on ties. Use ROWS (or add a unique tie-breaker to ORDER BY) for a strict row-by-row running sum

RANGE With an Interval: Rolling Time Windows

-- Revenue in the 30 days up to and including each order (calendar-based, not row-based)
SELECT o.id, o.created_at::date,
       sum(t.total) OVER (ORDER BY o.created_at
                          RANGE BETWEEN interval '30 days' PRECEDING AND CURRENT ROW) AS rolling_30d
FROM orders o
JOIN (SELECT order_id, sum(quantity * unit_price) AS total FROM order_items GROUP BY order_id) t
  ON t.order_id = o.id
WHERE o.status <> 'cancelled'
ORDER BY o.created_at;

Gaps and Islands

  • Problem: Group consecutive runs (e.g. consecutive months with an order, login streaks)
  • Trick: value − ROW_NUMBER() is constant within a consecutive run
-- Streaks of consecutive months in which Asha placed an order
WITH months AS (
    SELECT DISTINCT date_trunc('month', created_at)::date AS m
    FROM orders WHERE user_id = 1
), grp AS (
    SELECT m,
           m - (ROW_NUMBER() OVER (ORDER BY m) * interval '1 month') AS island
    FROM months
)
SELECT min(m) AS streak_start, max(m) AS streak_end, count(*) AS months
FROM grp
GROUP BY island
ORDER BY streak_start;

Filtering on Window Results

-- ERROR: window functions are not allowed in WHERE
-- SELECT * FROM employees WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 3;
 
-- Wrap it (CTE or subquery)
WITH ranked AS (
    SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees
)
SELECT name, salary FROM ranked WHERE rn <= 3;
  • PG 15+ Note: For outer filters like WHERE rn <= N on ROW_NUMBER/RANK/count, the planner adds a “Run Condition” and stops evaluating the window early, so the wrapper is efficient

Interview Questions

  • “ROW_NUMBER vs RANK vs DENSE_RANK?”: Unique numbers vs gaps after ties vs no gaps
  • “Top 3 per category?”: Ranking window in a subquery, filter rn <= 3 outside
  • “Running total?”: sum(x) OVER (ORDER BY …); use a ROWS frame if the key has ties
  • “Month-over-month growth?”: LAG(revenue) OVER (ORDER BY month); divide with NULLIF(…, 0)
  • “Why does LAST_VALUE return the current row?”: Default frame ends at the current row — widen it
  • “Window vs GROUP BY?”: Window keeps detail rows; GROUP BY collapses them

Summary

  • OVER(): PARTITION BY groups, ORDER BY sequences, frame selects rows for aggregates
  • Ranking: ROW_NUMBER (unique), RANK (gaps), DENSE_RANK (no gaps), NTILE (buckets)
  • Offsets: LAG/LEAD for previous/next comparisons; widen the frame for LAST_VALUE
  • Frames: ROWS counts rows, RANGE measures values (peers together, intervals), GROUPS counts tie-groups
  • Patterns: Top-N per group, deduplication, running totals, moving averages, gaps-and-islands

Graph View

Backlinks