Window Functions

Window functions separate junior SQL candidates from senior ones. They solve an entire class of problems — running totals, rankings, period-over-period comparisons, deduplication — that would otherwise require self-joins or subqueries that are verbose and slow. Interviewers ask window functions because they reveal whether you actually use SQL for analytical work.

---

Q1: What is a window function and how does it differ from GROUP BY?

Show answer

A window function computes a value for each row using a set of related rows, without collapsing those rows into a single output row. The key difference from GROUP BY is that the original rows are preserved.

With GROUP BY, 100 employee rows grouped by department become however-many-department rows. You lose access to individual employee data.

With a window function, all 100 rows remain, but each row gets an additional computed column derived from a calculation across its window (the set of rows the function "looks at").

-- GROUP BY: one row per department, individual employees lost
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Window function: every employee row kept, plus the department average alongside
SELECT
    employee_id,
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

The OVER() clause is what makes a function a window function. An empty OVER() means the window is the entire result set. PARTITION BY defines sub-windows (like GROUP BY but without collapsing rows).

---

Q2: Explain the OVER() clause — PARTITION BY, ORDER BY, and frame specification.

Show answer

The OVER() clause defines the window — the set of rows the function operates on for each output row. It has three optional components:

PARTITION BY — divides rows into independent partitions. The function resets at each partition boundary. Analogous to GROUP BY but without collapsing rows.

ORDER BY — defines the order of rows within each partition. Required for ranking functions and cumulative calculations. Also affects the default frame.

Frame specification — defines which rows within the ordered partition the function includes. The syntax is ROWS BETWEEN start AND end or RANGE BETWEEN start AND end.

Common frame boundaries: - UNBOUNDED PRECEDING — from the first row of the partition - N PRECEDING — N rows before the current row - CURRENT ROW — the current row - N FOLLOWING — N rows after the current row - UNBOUNDED FOLLOWING — through the last row of the partition

SELECT
    sale_date,
    daily_revenue,

    -- Running total from start of partition to current row
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,

    -- 7-day moving average (current row + 6 preceding)
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,

    -- Department total (no ORDER BY = no frame = whole partition)
    SUM(daily_revenue) OVER (
        PARTITION BY region
    ) AS region_total
FROM daily_sales;

When ORDER BY is specified but no frame is given, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For SUM and AVG, this usually behaves like a running calculation — but can produce unexpected results when there are ties in the ORDER BY column. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is explicit and safer.

---

Q3: What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Show answer

All three assign numeric rankings to rows within a partition. They differ in how they handle ties.

ROW_NUMBER() — assigns a unique sequential integer to every row. Ties get different numbers (the order among tied rows is arbitrary unless the ORDER BY fully specifies the sort).

RANK() — assigns the same rank to tied rows, then skips numbers. Ties at rank 3 get rank 3, and the next row gets rank 5 (3 + 2, where 2 is the number of tied rows).

DENSE_RANK() — assigns the same rank to tied rows, but does not skip numbers. Ties at rank 3 get rank 3, and the next distinct value gets rank 4.

SELECT
    name,
    score,
    ROW_NUMBER()  OVER (ORDER BY score DESC) AS row_num,
    RANK()        OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK()  OVER (ORDER BY score DESC) AS dense_rank
FROM exam_results;

name	score	row_num	rank	dense_rank
Alice	95	1	1	1
Bob	90	2	2	2
Carol	90	3	2	2
Dave	85	4	4	3

When to use each: - ROW_NUMBER: deduplication, pagination, picking exactly one row per group - RANK: competition rankings where ties share a rank and subsequent ranks skip - DENSE_RANK: ranking where you care about the number of distinct tiers, not gaps

Interview tip: the deduplication pattern always uses ROW_NUMBER(), not RANK(), because you need exactly one row per group (rank 1), and ties must produce different numbers.

---

Q4: How do LAG() and LEAD() work? Give a period-over-period example.

Show answer

LAG(col, n) returns the value of col from n rows before the current row in the ordered window. LEAD(col, n) returns the value from n rows after. Both return NULL if the offset goes beyond the partition boundary (you can supply a default as a third argument).

SELECT
    sale_date,
    daily_revenue,
    LAG(daily_revenue, 1)  OVER (ORDER BY sale_date) AS prev_day_revenue,
    LEAD(daily_revenue, 1) OVER (ORDER BY sale_date) AS next_day_revenue
FROM daily_sales;

Month-over-month growth rate:

SELECT
    sale_month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY sale_month) AS prev_month_revenue,
    ROUND(
        100.0 * (monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY sale_month))
              / NULLIF(LAG(monthly_revenue) OVER (ORDER BY sale_month), 0),
        2
    ) AS mom_growth_pct
FROM (
    SELECT
        DATE_TRUNC('month', order_date) AS sale_month,
        SUM(amount)                     AS monthly_revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
) monthly
ORDER BY sale_month;

NULLIF(..., 0) prevents division-by-zero when the previous month had zero revenue.

PARTITION BY in LAG/LEAD — each partition restarts independently, so LAG does not look across partition boundaries:

-- Month-over-month per product category, not crossing categories
SELECT
    category,
    sale_month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (
        PARTITION BY category
        ORDER BY sale_month
    ) AS prev_month_revenue
FROM monthly_category_sales;

---

Q5: What do FIRST_VALUE() and LAST_VALUE() do, and what frame trap should you know?

Show answer

FIRST_VALUE(col) returns the value of col from the first row of the window frame. LAST_VALUE(col) returns the value from the last row of the window frame.

SELECT
    employee_id,
    department,
    hire_date,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department
        ORDER BY hire_date
    ) AS first_hire_salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department
        ORDER BY hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_hire_salary
FROM employees;

The critical trap with LAST_VALUE: when you specify ORDER BY without a frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For LAST_VALUE, this means the "last" row in the frame is the current row itself — so LAST_VALUE returns the current row's own value, which is usually not what you want.

To get the true last value across the entire partition, you must explicitly specify:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

This is a frequently-asked gotcha. FIRST_VALUE doesn't have this problem (the first row in the frame is always the partition start), but LAST_VALUE almost always needs the explicit frame.

---

Q6: How do you compute a running total and a moving average using window functions?

Show answer

Running totals and moving averages are the most common real-world uses of window functions in analytics.

Running total:

SELECT
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales
ORDER BY sale_date;

Running total partitioned by region (resets per region):

SELECT
    region,
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS regional_running_total
FROM daily_sales;

7-day moving average:

SELECT
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY sale_date;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current row plus the 6 rows before it — a 7-row window. For dates without enough history (the first 6 rows), the window is smaller and the average uses however many rows exist. If you want only full 7-day windows, filter afterwards on sale_date >= MIN(sale_date) + 6.

---

Q7: What is NTILE() and when would you use it?

Show answer

NTILE(n) divides the ordered rows within a partition into n roughly equal buckets and assigns each row a bucket number from 1 to n.

-- Divide customers into 4 quartiles by total spend
SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
FROM customer_totals;

If the number of rows is not evenly divisible by n, the first buckets get one extra row.

Common uses: - Quartile/decile/percentile analysis without computing exact percentile values - Labelling top 10% / bottom 10% of a distribution - Splitting a ranked list into "tiers" for a marketing campaign

-- Label customers as top/mid/bottom tier
SELECT
    customer_id,
    total_spend,
    CASE NTILE(10) OVER (ORDER BY total_spend DESC)
        WHEN 1  THEN 'top 10%'
        WHEN 10 THEN 'bottom 10%'
        ELSE 'middle'
    END AS spend_tier
FROM customer_totals;

NTILE vs PERCENT_RANK: NTILE(100) gives approximate percentile buckets; PERCENT_RANK() gives the exact percentile rank of each row as a fraction between 0 and 1. Use NTILE when you want discrete buckets; use PERCENT_RANK when you need exact ranks.

---

Q8: How do you compute percent of total using a window function?

Show answer

Computing each row's share of a total is a classic combination of aggregation and window functions. Without window functions, you'd need a subquery for the total; with window functions, it's a single scan.

-- Each product's share of total revenue
SELECT
    product_name,
    revenue,
    SUM(revenue) OVER ()                                  AS total_revenue,
    ROUND(100.0 * revenue / SUM(revenue) OVER (), 2)      AS pct_of_total
FROM product_revenue
ORDER BY revenue DESC;

SUM(revenue) OVER () with an empty OVER() computes the sum across the entire result set — the grand total. Each row divides its own revenue by that grand total.

Percent within group — each product's share within its category:

SELECT
    category,
    product_name,
    revenue,
    SUM(revenue) OVER (PARTITION BY category)                          AS category_total,
    ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY category), 2) AS pct_of_category
FROM product_revenue
ORDER BY category, revenue DESC;

The PARTITION BY scopes the SUM to each category independently, so the percentages sum to 100% within each category rather than across all categories.

---

Q9: How do you use ROW_NUMBER() for deduplication?

Show answer

ROW_NUMBER() OVER (PARTITION BY key ORDER BY tiebreaker) is the standard approach for keeping exactly one row per key — the most common deduplication pattern in production SQL.

-- Keep the most recent record per customer (by updated_at)
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM customers
)
SELECT * FROM ranked WHERE rn = 1;

The logic: PARTITION BY customer_id creates one independent window per customer. ORDER BY updated_at DESC puts the most recent row first within each window. ROW_NUMBER() assigns 1 to that first row, 2 to the second, and so on. Filtering WHERE rn = 1 keeps only the most recent row per customer.

You can choose any column to break ties — most recent, highest value, first alphabetically:

-- Keep the highest-value order per customer
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_total DESC, order_id ASC    -- tie-break on order_id
        ) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

This pattern is used in: - Deduplicating CDC (change data capture) streams - Keeping the latest snapshot per entity - Selecting one representative row from a group for downstream joins

---

Q10: How do you identify consecutive sequences or "streaks" in SQL?

Show answer

Detecting consecutive streaks — login streaks, consecutive profitable days, consecutive failures — is a classic window function puzzle.

The technique relies on the observation that if you assign row numbers to ordered rows, consecutive rows will have a constant difference between their row number and some other ordered sequence (like date number). A break in the sequence shows up as a change in that difference.

-- Find login streaks per user
-- Assumption: one row per (user_id, login_date), no duplicate dates per user

WITH ordered AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM user_logins
),
grouped AS (
    SELECT
        user_id,
        login_date,
        login_date - rn * INTERVAL '1 day' AS streak_group
        -- consecutive dates have the same streak_group value
    FROM ordered
)
SELECT
    user_id,
    streak_group,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_length
FROM grouped
GROUP BY user_id, streak_group
ORDER BY user_id, streak_start;

The key insight: for consecutive days, login_date - ROW_NUMBER() * 1 day produces the same value (the "anchor" date for the streak). When the streak breaks, the subtraction produces a different value, creating a new group.

Interviewers use this problem to test whether you understand that row numbers and date sequences are both ordered, so their difference isolates continuity breaks.

---

Q11: What is PERCENT_RANK() and CUME_DIST(), and when do they differ?

Show answer

Both functions express a row's position in an ordered distribution as a fraction.

PERCENT_RANK() — the relative rank of a row, as a fraction between 0 and 1. The lowest row gets 0; the highest gets 1. Formula: (rank - 1) / (total_rows - 1).

CUME_DIST() — the cumulative distribution: the fraction of rows with a value less than or equal to the current row's value. Always between 1/n and 1. Formula: (number of rows ≤ current row) / total_rows.

SELECT
    name,
    score,
    PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,
    CUME_DIST()    OVER (ORDER BY score) AS cume_dist
FROM exam_results
ORDER BY score;

name	score	pct_rank	cume_dist
Dave	70	0.00	0.25
Carol	80	0.33	0.50
Bob	90	0.67	0.75
Alice	95	1.00	1.00

Key difference: PERCENT_RANK can return 0 (the minimum); CUME_DIST never returns 0 (the smallest value still has at least 1/n rows ≤ it). For large tables they converge, but they answer subtly different questions: "what fraction of rows are strictly below me" vs "what fraction are at or below me".

Use CUME_DIST when the question is "what percentile does this row fall at?" Use PERCENT_RANK for relative standing where you want the bottom to be exactly 0.

---

Q12: How do you compute a year-over-year comparison using LAG with PARTITION BY?

Show answer

Year-over-year (YoY) comparisons require pairing each row with the corresponding row from the prior year. LAG() with PARTITION BY on a time-period dimension handles this cleanly.

-- Monthly revenue with year-over-year comparison by region
WITH monthly_revenue AS (
    SELECT
        region,
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount)                     AS revenue
    FROM orders
    GROUP BY region, DATE_TRUNC('month', order_date)
)
SELECT
    region,
    month,
    revenue,
    LAG(revenue, 12) OVER (
        PARTITION BY region
        ORDER BY month
    ) AS revenue_prev_year,
    ROUND(
        100.0 * (revenue - LAG(revenue, 12) OVER (PARTITION BY region ORDER BY month))
              / NULLIF(LAG(revenue, 12) OVER (PARTITION BY region ORDER BY month), 0),
        2
    ) AS yoy_growth_pct
FROM monthly_revenue
ORDER BY region, month;

LAG(revenue, 12) with monthly data looks back 12 rows — exactly one year. PARTITION BY region ensures the lookback stays within the same region (January 2024 for Region A does not look back to January 2023 for Region B).

The NULLIF(..., 0) prevents division-by-zero and is good practice whenever computing growth rates.