Window Functions — Cheat Sheet

Window functions compute a value for each row using a sliding "window" of related rows, without collapsing the result set the way GROUP BY does.

• ✓OVER() marks a function as a window function; without it the same function (SUM, COUNT) is a plain aggregate.
• ✓PARTITION BY groups rows for the window computation but does not collapse rows in the output — all rows are preserved.
• ✓ORDER BY inside OVER changes the default frame to a cumulative (running) window from unbounded preceding to the current row.
• ✓ROWS BETWEEN gives precise row-count frames; RANGE BETWEEN works on value ranges and can behave unexpectedly with ties.
• ✓Window functions execute after WHERE/GROUP BY/HAVING; filter on their results using a CTE or derived table.
• ✓An empty OVER() with no PARTITION BY or ORDER BY computes the function over the entire result set.

-- GROUP BY: collapses rows — one row per user
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id;

-- Window function: keeps all order rows, adds per-user total as extra column
SELECT
    id,
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id) AS user_total_spent,
    SUM(amount) OVER ()                      AS grand_total
FROM orders
WHERE status = 'completed';
-- Every completed order row is returned, with user-level and overall totals beside it

ROW_NUMBER assigns a unique sequential integer to every row; RANK skips positions after ties; DENSE_RANK never skips — choose based on whether gaps in ranking matter.

• ✓ROW_NUMBER always produces unique sequential numbers — no two rows share the same value even on ties.
• ✓RANK gives tied rows the same number and skips the next rank(s) equal to the number of tied rows.
• ✓DENSE_RANK gives tied rows the same number but never skips — consecutive ranks are always n and n+1.
• ✓The top-N per group pattern: partition by group, order by metric, filter rank <= N inside a CTE or subquery.
• ✓Use ROW_NUMBER for pagination because uniqueness guarantees exactly one row per number.
• ✓All three functions require ORDER BY inside OVER(); the result is undefined (non-deterministic) without it.

-- Show all three on employees ordered by salary within department
SELECT
    name,
    department_id,
    salary,
    ROW_NUMBER()  OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK()        OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
    DENSE_RANK()  OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees;

-- Sample output for one department (two employees tied at 90000):
-- name     dept  salary  row_num  rnk  dense_rnk
-- Alice    1     90000   1        1    1
-- Bob      1     90000   2        1    1
-- Carol    1     75000   3        3    2   ← RANK skips to 3, DENSE_RANK goes to 2

LAG accesses the value of a column from a previous row; LEAD accesses a future row — both within the current partition — enabling period-over-period comparisons without self-joins.

• ✓LAG(col, n, default) returns the value from n rows before the current row in the window; LEAD looks n rows ahead.
• ✓Without a default, both functions return NULL when the offset falls outside the partition.
• ✓Period-over-period analysis (MoM, YoY) with LAG is more efficient than a self-join on the same table.
• ✓Wrap NULLIF around the LAG value in percentage calculations to avoid division-by-zero errors.
• ✓Combine DISTINCT + LAG to detect consecutive-day patterns without duplicates per day.
• ✓Status transition detection: compare current status to LAG(status) within user partitions.

-- Monthly revenue and MoM change
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount)                      AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)                        AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month)              AS abs_change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
              / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        2
    )                                                          AS pct_change
FROM monthly_revenue
ORDER BY month;

FIRST_VALUE returns the first value in the window frame; LAST_VALUE requires an explicit extended frame clause or it will only ever return the current row's own value.

• ✓FIRST_VALUE returns the first value in the window frame; with ORDER BY and the default frame it effectively returns the partition-level maximum (or minimum, depending on order direction).
• ✓LAST_VALUE's default frame ends at the current row, not the end of the partition — always add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when you want the true last value.
• ✓NTH_VALUE(expr, n) generalises FIRST_VALUE (n=1) and LAST_VALUE; it also needs the extended frame for rows beyond the current position.
• ✓Prefer FIRST_VALUE over MIN() window when you need the associated row's other column values, not just the minimum scalar.
• ✓DISTINCT in the outer query removes duplicate partition-level values when only the per-partition result is needed.

-- Each employee's salary alongside their department's top earner salary
SELECT
    e.name,
    d.name                            AS department,
    e.salary,
    FIRST_VALUE(e.salary) OVER (
        PARTITION BY e.department_id
        ORDER BY e.salary DESC
        -- default frame: RANGE UNBOUNDED PRECEDING TO CURRENT ROW
        -- first row in this ordered partition = highest salary
    )                                 AS dept_top_salary,
    FIRST_VALUE(e.name) OVER (
        PARTITION BY e.department_id
        ORDER BY e.salary DESC
    )                                 AS top_earner_name
FROM employees e
JOIN departments d ON d.id = e.department_id;

NTILE(n) distributes rows into n ranked buckets as evenly as possible, enabling quartile, decile, and percentile segmentation directly in SQL.

• ✓NTILE(n) assigns bucket numbers 1..n; when rows are not evenly divisible the first (mod) buckets receive one extra row.
• ✓PERCENT_RANK = (rank − 1) / (total_rows − 1); it returns 0 for the first row and 1 for the last.
• ✓CUME_DIST = number of rows with value <= current / total rows; it is always > 0 and <= 1.
• ✓Filter PERCENT_RANK >= 0.9 to get the top 10 %; filter NTILE(10) = 10 for the equivalent decile.
• ✓NTILE does not guarantee equal counts when rows are not divisible; the first buckets are larger by one.
• ✓Always supply ORDER BY inside OVER() for NTILE, PERCENT_RANK, and CUME_DIST — results are meaningless without ordering.

-- Divide customers into 4 spending quartiles
WITH customer_spend AS (
    SELECT
        u.id                AS user_id,
        u.name,
        SUM(o.amount)       AS total_spent
    FROM users u
    JOIN orders o ON o.user_id = u.id
    WHERE o.status = 'completed'
    GROUP BY u.id, u.name
)
SELECT
    user_id,
    name,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent ASC) AS spending_quartile
    -- quartile 4 = top spenders, quartile 1 = lowest
FROM customer_spend
ORDER BY total_spent DESC;

Frame clauses (ROWS BETWEEN / RANGE BETWEEN) control which rows contribute to each window computation, enabling running sums, moving averages, and partition-resetting accumulators.

• ✓ROWS BETWEEN counts physical rows; RANGE BETWEEN counts rows whose ORDER BY value falls within a range — use ROWS for predictable moving windows.
• ✓The default frame when ORDER BY is present inside OVER() is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — make it explicit to avoid surprises.
• ✓PARTITION BY resets the running total automatically at each group boundary; no extra logic needed.
• ✓A 7-day moving average uses ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (7 rows total including current).
• ✓ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING spans the entire partition — used for LAST_VALUE and grand totals.
• ✓Running totals earlier in the partition will have fewer rows contributing, which is by design and matches the cumulative pattern.

-- Monthly revenue and cumulative revenue year-to-date
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount)                      AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
)
SELECT
    month,
    revenue,
    SUM(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM monthly
ORDER BY month;

GROUP BY collapses many rows into one aggregate row per group; window functions keep all rows while adding computed values — choose based on whether you need row-level detail alongside aggregates.

• ✓GROUP BY collapses rows: one output row per group. Window functions preserve all rows: every input row appears in output.
• ✓Use GROUP BY for pure summaries (report tables). Use window functions when you need both detail rows and group-level metrics together.
• ✓GROUP BY and window functions can coexist: GROUP BY runs first, then the window function operates on the aggregated rows.
• ✓You cannot reference a window function alias in WHERE, HAVING, or GROUP BY — wrap in a CTE or subquery.
• ✓Window functions add computational cost; avoid applying them on large intermediate result sets without appropriate indexes.

-- Requirement: list every order with the customer's total order count

-- WRONG attempt with GROUP BY: loses individual order rows
SELECT user_id, COUNT(*) AS total_orders
FROM orders
GROUP BY user_id;
-- Can't include order id, amount, etc. — they would need to be aggregated too

-- CORRECT: window function preserves every row
SELECT
    o.id           AS order_id,
    o.user_id,
    o.amount,
    o.status,
    o.created_at,
    COUNT(*)   OVER (PARTITION BY o.user_id) AS user_total_orders,
    SUM(amount) OVER (PARTITION BY o.user_id) AS user_total_spent
FROM orders o;