Module 6 — Window Functions

Why window functions exist

When to use it

• You need detail rows and an aggregate/rank/comparison in the same result set (rank, running total, % of total, comparison to previous row).
• Top-N-per-group problems ("top 3 highest paid employees per department").
• Time-series analysis: running totals, moving averages, period-over-period change.
• Deduplication: keep exactly one row per group based on some rule (latest record, highest value).
• Gap detection, streak detection, sessionization — anything where "compare this row to the row before/after it" matters.

When NOT to use it

• If you only need one aggregated row per group and don't need the detail rows — plain GROUP BY is simpler and usually cheaper.
• If you need to filter on the aggregate directly in the same query without a wrapping query — you cannot put a window function in WHERE (more on this trap later).
• Very old database versions without window function support (rare today, but some legacy MySQL < 8.0 setups still exist) — you'd fall back to correlated subqueries or variables.

How it executes internally

Schema & sample data

CREATE TABLE orders (
  order_id     INT PRIMARY KEY,
  customer_id  INT NOT NULL,
  order_date   DATE NOT NULL,
  amount       NUMERIC(10,2) NOT NULL,
  status       VARCHAR(20)
);

Anatomy of OVER()

function_name(expr) OVER (
  PARTITION BY partition_column(s)   -- optional: splits rows into groups
  ORDER BY sort_column(s)            -- optional: defines row order inside each group
  ROWS/RANGE BETWEEN ... AND ...     -- optional: defines the frame (subset of the group)
)

What happens if PARTITION BY is missing

SELECT order_id, amount,
       SUM(amount) OVER () AS grand_total
FROM orders;

What happens if window ORDER BY is missing

Query ORDER BY vs window ORDER BY

Multiple window functions in one query

SELECT order_id, customer_id, amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq,
       SUM(amount)  OVER (PARTITION BY customer_id)                    AS customer_total,
       LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount
FROM orders;

PARTITION BY vs GROUP BY

Example: customer total beside every order

-- GROUP BY: one row per customer, detail lost
SELECT customer_id, SUM(amount) AS customer_total
FROM orders
GROUP BY customer_id;

-- PARTITION BY: every order row survives, total riding alongside
SELECT order_id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Percent of customer total

SELECT order_id, customer_id, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id), 1) AS pct_of_customer
FROM orders;

Percent of grand total

SELECT order_id, customer_id, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_grand_total
FROM orders;

Ranking functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE

SELECT customer_id, amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn,
       RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS drnk,
       NTILE(4)     OVER (PARTITION BY customer_id ORDER BY amount DESC) AS quartile
FROM orders;

Tie behavior — the single most-tested distinction

• ROW_NUMBER() — always unique, 1,2,3,4. Ties are broken arbitrarily (or nondeterministically) unless you add a tie-breaker column.
• RANK() — ties share the same rank, and the next rank skips (1,1,3,4).
• DENSE_RANK() — ties share the same rank, but the next rank does not skip (1,1,2,3).
• NTILE(n) — splits the partition into n roughly-equal-sized buckets (1..n), used for quartiles/percentile buckets. If rows don't divide evenly, earlier buckets get the extra rows.

Deterministic ordering with tie-breaker columns

Top-N per group

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id) AS rn
  FROM orders
) x
WHERE rn <= 3;

Pagination

-- "Page 3" of 20-row pages, ordered by order_date
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY order_date, order_id) AS rn
  FROM orders
) x
WHERE rn BETWEEN 41 AND 60;

Percentile buckets with NTILE

SELECT customer_id, amount,
       NTILE(4) OVER (ORDER BY amount) AS quartile   -- 1 = lowest 25%, 4 = highest 25%
FROM orders;

Navigation / offset functions: LAG and LEAD

SELECT customer_id, order_date, amount,
       LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
       LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_amount
FROM orders;

Difference from a self join

Period-over-period change

SELECT customer_id, order_date, amount,
       amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS change_vs_prev
FROM orders;

Previous / next order

Same pattern as above — LAG(order_id) / LEAD(order_id) gives you the neighboring order's identifier directly, useful for building "next steps" or audit trails without a join.

Default values in LAG/LEAD

LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount_or_zero

Multiple offset values

SELECT customer_id, order_date, amount,
       LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS one_back,
       LAG(amount, 2) OVER (PARTITION BY customer_id ORDER BY order_date) AS two_back
FROM orders;

The second argument to LAG/LEAD is how many rows to skip — LAG(amount, 2) reaches two rows behind the current one, not one.

Value functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE

SELECT customer_id, order_date, amount,
       FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_amount,
       LAST_VALUE(amount)  OVER (PARTITION BY customer_id ORDER BY order_date
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_amount,
       NTH_VALUE(amount, 2) OVER (PARTITION BY customer_id ORDER BY order_date
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_order_amount
FROM orders;

The correct fix: full-partition frame

LAST_VALUE(amount) OVER (
  PARTITION BY customer_id
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

FIRST_VALUE() doesn't have this problem in the same way, because "first row of the default frame" and "first row of the partition" are the same thing — but it's good habit to state the frame explicitly for all three of these functions so the query means the same thing on every engine.

NTH_VALUE

NTH_VALUE(expr, n) returns the value from the n-th row of the frame — e.g. the 2nd order's amount. Like LAST_VALUE, it needs the full-partition frame to behave predictably when n might fall after the current row.

Frame clauses: ROWS, RANGE, GROUPS

... OVER (
  PARTITION BY ...
  ORDER BY ...
  {ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end
)

Default frame behavior

ROWS vs RANGE — the difference that bites in production

GROUPS

GROUPS (supported on newer Postgres, Snowflake, BigQuery) is like ROWS but counts distinct peer groups instead of individual rows — GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW means "the current peer group plus the two peer groups before it," useful when ties are common and you still want row-like counting.

Duplicate ORDER BY values and peer rows

Filtering window results: subquery, CTE, QUALIFY

-- ❌ fails: "column rn does not exist" / invalid use of window function
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
WHERE rn = 1;

Fix 1 — wrap it in a subquery

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) x
WHERE rn = 1;

Fix 2 — wrap it in a CTE (same idea, more readable)

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Fix 3 — QUALIFY (BigQuery, Snowflake)

SELECT *,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
QUALIFY rn = 1;

QUALIFY is to window functions what HAVING is to GROUP BY aggregates — a dedicated filter stage that runs after the window function is computed, without the ceremony of a wrapping subquery or CTE. It's not available in PostgreSQL, SQL Server, or MySQL — use the subquery/CTE pattern there.

Running totals & aggregate windows

Running totals

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (
         PARTITION BY customer_id ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders;

Running averages

AVG(amount) OVER (
  PARTITION BY customer_id ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_avg

Grand totals vs per-group totals

SELECT order_id, customer_id, amount,
       SUM(amount) OVER ()                     AS grand_total,        -- no PARTITION BY
       SUM(amount) OVER (PARTITION BY customer_id) AS per_customer_total -- no ORDER BY = whole partition
FROM orders;

Percent of total

SELECT order_id, customer_id, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id), 1) AS pct_of_customer_total
FROM orders;

Moving averages

-- 7-day (row-based) moving average
SELECT customer_id, order_date, amount,
       AVG(amount) OVER (
         PARTITION BY customer_id ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7
FROM orders;

Why you need a calendar/date spine in production

-- Postgres-style date spine
SELECT d::date AS day
FROM generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day') d;

Period-over-period analysis

Daily / monthly change

SELECT order_date, daily_revenue,
       daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date) AS change_vs_prev_day
FROM (
  SELECT order_date, SUM(amount) AS daily_revenue
  FROM orders GROUP BY order_date
) daily;

Monthly change is the same query with DATE_TRUNC('month', order_date) (or your engine's equivalent) in place of the raw date.

Revenue growth %

SELECT order_date, daily_revenue,
       ROUND(100.0 * (daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date))
             / NULLIF(LAG(daily_revenue) OVER (ORDER BY order_date), 0), 1) AS pct_growth
FROM (
  SELECT order_date, SUM(amount) AS daily_revenue
  FROM orders GROUP BY order_date
) daily;

Handling the first row with no previous period

LAG() returns NULL for the first row of each partition by design — there is no prior period to compare against. Decide deliberately whether that should stay NULL, become 0 via LAG(x, 1, 0), or be filtered out downstream; don't let it silently propagate into a growth-% calculation as an unhandled NULL.

Deduplication with ROW_NUMBER

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, order_id DESC) AS rn
  FROM orders_staging
)
SELECT * FROM ranked WHERE rn = 1;

Keep the latest row per key

PARTITION BY the natural key, ORDER BY the recency column descending, keep rn = 1. This is the exact same shape whether the key is a customer, a product, or an event ID.

Delete duplicates using ROW_NUMBER

DELETE FROM orders_staging
WHERE order_id IN (
  SELECT order_id FROM (
    SELECT order_id,
           ROW_NUMBER() OVER (PARTITION BY customer_id, order_date ORDER BY order_id DESC) AS rn
    FROM orders_staging
  ) x
  WHERE rn > 1
);

Dedup staging before MERGE

A MERGE/upsert statement will error or silently misbehave if the source side contains multiple rows matching the same target key. Standard production pattern: dedup the staging table (or a CTE feeding the merge) with ROW_NUMBER() = 1 before the MERGE ever runs.

Deterministic tie-breakers

Same rule as ranking functions: ORDER BY updated_at DESC alone can tie. Always append a unique column (order_id, a surrogate key, or the raw file's row-ingestion timestamp) so which row survives is reproducible, not luck-of-the-plan.

Top-N per group

Top 1 per group

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id) AS rn
  FROM orders
) x WHERE rn = 1;

Top 3 per group

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id) AS rn
  FROM orders
) x WHERE rn <= 3;

Ties: RANK() vs exact N with ROW_NUMBER()

Worked examples

-- Top 3 highest-paid employees per department
SELECT * FROM (
  SELECT employee_id, department_id, salary,
         DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drnk
  FROM employees
) x WHERE drnk <= 3;

-- Top product per customer by total spend
SELECT * FROM (
  SELECT customer_id, product_id, SUM(amount) AS spend,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY SUM(amount) DESC) AS rn
  FROM orders GROUP BY customer_id, product_id
) x WHERE rn = 1;

Gaps and islands

Consecutive date streaks / consecutive login days

WITH numbered AS (
  SELECT user_id, login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM logins
),
islands AS (
  SELECT user_id, login_date,
         login_date - (rn * INTERVAL '1 day') AS island_key   -- constant within a streak
  FROM numbered
)
SELECT user_id, island_key, COUNT(*) AS streak_length,
       MIN(login_date) AS streak_start, MAX(login_date) AS streak_end
FROM islands
GROUP BY user_id, island_key
ORDER BY user_id, streak_start;

Why it works: within an unbroken run of consecutive days, login_date increases by exactly 1 each row and rn also increases by exactly 1 — so login_date - rn stays constant for the whole run and jumps the instant a day is skipped.

Grouping islands using date − ROW_NUMBER()

The pattern generalizes beyond dates: for any strictly-increasing integer sequence, sequence_value - ROW_NUMBER() is constant within a consecutive island and different across islands.

Detecting missing sequence numbers

SELECT order_id AS gap_start_after,
       LEAD(order_id) OVER (ORDER BY order_id) - order_id - 1 AS missing_count
FROM orders
QUALIFY LEAD(order_id) OVER (ORDER BY order_id) - order_id > 1;

Detecting inactive periods

SELECT customer_id, order_date,
       order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last_order
FROM orders
QUALIFY order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) > 30;

Sessionization

WITH events_with_gap AS (
  SELECT user_id, event_time,
         EXTRACT(EPOCH FROM (event_time - LAG(event_time) OVER (
           PARTITION BY user_id ORDER BY event_time))) / 60 AS gap_minutes
  FROM clickstream
),
session_starts AS (
  SELECT *,
         CASE WHEN gap_minutes IS NULL OR gap_minutes > 30 THEN 1 ELSE 0 END AS is_new_session
  FROM events_with_gap
),
sessionized AS (
  SELECT *,
         SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
  FROM session_starts
)
SELECT user_id, session_id, event_time FROM sessionized ORDER BY user_id, event_time;

How it works, step by step

• LAG() compares each event's timestamp to the previous event from the same user.
• If the gap exceeds 30 minutes (or it's the user's very first event), flag it as is_new_session = 1 — a "session-start flag."
• A running SUM() of that flag, in event order, produces a number that only increments at session boundaries — that running sum is the session ID.

Production use case: web analytics

This exact three-step pattern (gap detection → flag → running sum) is how most web analytics pipelines (Snowplow-style event pipelines, GA4-style session stitching) derive session IDs from raw clickstream events before any downstream funnel or bounce-rate analysis can run.

Cohort & retention analysis

WITH first_activity AS (
  SELECT user_id,
         MIN(activity_date) OVER (PARTITION BY user_id) AS first_activity_date
  FROM user_activity
),
labeled AS (
  SELECT user_id, activity_date,
         DATE_TRUNC('month', first_activity_date) AS cohort_month,
         DATE_PART('month', AGE(DATE_TRUNC('month', activity_date), DATE_TRUNC('month', first_activity_date))) AS month_offset
  FROM first_activity
)
SELECT cohort_month, month_offset, COUNT(DISTINCT user_id) AS active_users
FROM labeled
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

The pieces

• First activity date per user — MIN(activity_date) OVER (PARTITION BY user_id), so every row for that user carries their signup/first-activity date alongside it.
• Cohort month — truncate the first-activity date to the month; every user gets bucketed into exactly one cohort.
• Activity month offset — the difference in months between the current activity's month and the cohort month (0 = signup month, 1 = one month later, etc.), typically via DATE_DIFF/AGE.
• Retention matrix — after windowing derives cohort month and offset per row, a final GROUP BY cohort_month, month_offset aggregation produces the classic triangular retention table (rows = cohorts, columns = months since signup).

Slowly changing / latest-snapshot patterns

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY updated_at DESC) AS rn
  FROM entity_history
)
SELECT * FROM ranked WHERE rn = 1;

Latest record / current active row

Same shape as deduplication: PARTITION BY the entity's natural key, ORDER BY updated_at DESC, keep rn = 1. The difference from a pure dedup task is intent — here you're deliberately modeling "current state" as a view on top of a full history table, not discarding history.

Why this matters before joining dimensions

PERCENT_RANK, CUME_DIST, and quartiles

SELECT customer_id, amount,
       PERCENT_RANK() OVER (ORDER BY amount) AS pct_rank,   -- (rank-1) / (n-1), range 0..1
       CUME_DIST()    OVER (ORDER BY amount) AS cume_dist,  -- rows <= current / n, range >0..1
       NTILE(4)       OVER (ORDER BY amount) AS quartile
FROM orders;

PERCENT_RANK()

Returns the relative rank of the current row as a fraction between 0 and 1: (rank - 1) / (total_rows - 1). The lowest value in the partition is always exactly 0; the highest is always exactly 1.

CUME_DIST()

"Cumulative distribution" — the fraction of rows in the partition whose value is less than or equal to the current row's value. Unlike PERCENT_RANK, it never returns 0 (every row is at least "less than or equal to" itself) and ties share the same value.

Median / percentile discussion

None of the ranking functions directly compute a median or an arbitrary percentile value — for that, most engines offer a dedicated function outside the window-ranking family (PERCENTILE_CONT/PERCENTILE_DISC as ordered-set aggregates, or APPROX_PERCENTILE for large-scale approximations in Snowflake/BigQuery/Spark). PERCENT_RANK/CUME_DIST tell you where a given row sits in the distribution; PERCENTILE_CONT tells you what value sits at a given percentile.

NTILE(4) for quartiles

The simplest practical outlier-flagging pattern in interviews: bucket into quartiles with NTILE(4), then treat quartile 1 and quartile 4 as "low outlier candidates" and "high outlier candidates" for a follow-up filter.

Window function performance

Sort cost

Indexes for window functions

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

• Index the PARTITION BY column(s) first, then the window ORDER BY column(s) — a composite index in that order lets the engine potentially read rows already sorted, skipping an explicit sort step.
• A covering index (one that also includes the selected/aggregated columns) can let the engine satisfy the whole window computation from the index alone, without touching the base table.

Memory spills

Pre-filtering rows with a WHERE clause before the window function runs (rather than filtering afterward with QUALIFY/subquery) shrinks the data being sorted and partitioned, directly reducing spill risk.

Partition size & skew

Multiple windows: reuse with a named WINDOW clause

SELECT customer_id, order_date, amount,
       SUM(amount)   OVER w AS running_total,
       AVG(amount)   OVER w AS running_avg,
       COUNT(*)      OVER w AS running_count
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

Reusing a named window lets the engine potentially sort the data once and stream all three functions off that single sort. Functions with genuinely different PARTITION BY/ORDER BY specs still force separate sorts — named windows can't merge fundamentally different orderings.

Pre-aggregation

Distributed warehouse notes

• Partition key affects data movement — a well-chosen key (e.g. customer_id with many distinct, evenly-sized customers) shuffles cleanly; a poor key concentrates work on one node.
• Skew in window partitions — the distributed-systems version of the "one customer has 90% of rows" problem: that node becomes a straggler that the whole job waits on.
• Cost of global windows with no PARTITION BY — an unpartitioned window (e.g. SUM(x) OVER ()) effectively forces every row onto a single node/reducer for that computation, which on a genuinely large table can be dramatically slower or even infeasible.

Dialect differences

How interviewers trick you

3 hard interview questions

SELECT department_id, salary FROM (
  SELECT department_id, salary,
         DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drnk
  FROM employees
) x WHERE drnk = 2;

SELECT * FROM (
  SELECT customer_id, order_id, order_date, amount,
         LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount
  FROM orders
) x
WHERE amount > prev_amount;

WITH numbered AS (
  SELECT user_id, login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM logins
),
islands AS (
  SELECT user_id, login_date - (rn * INTERVAL '1 day') AS island_key
  FROM numbered
)
SELECT user_id, MAX(streak_len) AS longest_streak FROM (
  SELECT user_id, island_key, COUNT(*) AS streak_len
  FROM islands GROUP BY user_id, island_key
) s
GROUP BY user_id;

2 FAANG questions

WITH flagged AS (
  SELECT user_id, event_time,
         CASE WHEN LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
                   OR event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) > INTERVAL '30 minutes'
              THEN 1 ELSE 0 END AS is_new_session
  FROM clickstream
),
sessioned AS (
  SELECT *, SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
  FROM flagged
),
session_bounds AS (
  SELECT user_id, session_id, MIN(event_time) AS start_t, MAX(event_time) AS end_t
  FROM sessioned GROUP BY user_id, session_id
)
SELECT AVG(EXTRACT(EPOCH FROM (end_t - start_t))) AS avg_session_seconds
FROM session_bounds;

WITH first_activity AS (
  SELECT user_id, MIN(activity_date) OVER (PARTITION BY user_id) AS signup_date
  FROM user_activity
),
labeled AS (
  SELECT user_id, activity_date,
         DATE_TRUNC('month', signup_date) AS cohort_month,
         (EXTRACT(YEAR FROM activity_date) * 12 + EXTRACT(MONTH FROM activity_date))
         - (EXTRACT(YEAR FROM signup_date) * 12 + EXTRACT(MONTH FROM signup_date)) AS month_offset
  FROM first_activity
)
SELECT cohort_month, month_offset, COUNT(DISTINCT user_id) AS active_users
FROM labeled
WHERE month_offset BETWEEN 0 AND 3
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

Production safety & correctness

Deterministic ordering

ORDER BY updated_at DESC, id DESC

Dedup safety

• Preview the rows that would be removed with a SELECT before ever running the DELETE.
• Store the rows being removed in an audit table first, so a mistake is recoverable.
• Batch large deletes instead of one massive statement, and wrap in a transaction so you can roll back.

Missing dates

Late-arriving data

Null handling

• LAG()/LEAD() return NULL when there's no previous/next row — decide deliberately whether to leave that NULL, default it with the third argument, or filter it downstream.
• FIRST_VALUE()/LAST_VALUE() will happily return a NULL if the first/last row in the frame has a NULL in that column.
• Some engines (e.g. Snowflake, Oracle) support an IGNORE NULLS modifier on navigation/value functions to skip past NULLs and find the nearest non-null value instead — check dialect support before relying on it.

Practice, rewrites & whiteboard patterns

Classic questions (write the SQL, don't just read it)

Trick questions (explain out loud, no SQL needed)

• ROW_NUMBER vs RANK vs DENSE_RANK — what happens on a tie?
• Why doesn't WHERE rn = 1 work in the same query as the window function?
• Why does LAST_VALUE() sometimes just return the current row?
• What's the actual difference between ROWS and RANGE?
• Why does a missing tie-breaker make a result nondeterministic?
• What's the difference between the window ORDER BY and the final ORDER BY?
• Why can GROUP BY and window functions conflict in the same query?
• Why does COUNT(*) OVER() put the total row count on every single row?

Rewrite practice

• Rewrite a correlated subquery ("show each order plus that customer's max order amount") as a window function.
• Rewrite a self-join "compare row to previous row" query as LAG().
• Rewrite a top-N-per-group query built from a join/subquery into ROW_NUMBER().
• Rewrite a manual dedup query (DISTINCT + arbitrary pick) into a deterministic ROW_NUMBER() dedup.
• Rewrite a running total built from a self join (SUM where b.date <= a.date) into SUM() OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Whiteboard patterns — recognize the shape instantly

Self-check before you move on