Q19 of 26 · SQL
What are window functions and give a QA example using ROW_NUMBER or RANK.
SQLSeniorsqlwindow-functionsrow-numberrankadvanceddeduplication
Short answer
Short answer: Window functions compute a value across a set of rows related to the current row (a 'window'), without collapsing them into a group. ROW_NUMBER assigns a sequential rank within each partition — useful for finding the latest record per entity or deduplicating.
Detail
Unlike GROUP BY which collapses rows, window functions keep all rows and add a computed column:
SELECT
order_id,
customer_id,
created_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC
) AS rn
FROM orders;
This gives each order a rank within its customer — rank 1 is the most recent. Now wrap it to get only the latest order per customer:
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn = 1;
QA deduplication use: If a table has duplicate rows but you need to process only the latest one:
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM users
)
SELECT * FROM ranked WHERE rn = 1;
-- Returns one row per email: the most recently created
Other useful window functions:
- RANK() — like ROW_NUMBER but ties get the same rank (and the next rank is skipped)
- LAG(col, 1) — value from the previous row; great for detecting gaps in sequences or time-based anomalies
- SUM() OVER (PARTITION BY …) — running total per group without collapsing rows
// EXAMPLE
-- Find the most recent payment per order (handle retried payments)
WITH ranked_payments AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_at DESC) AS rn
FROM payments
)
SELECT order_id, amount, status, created_at
FROM ranked_payments
WHERE rn = 1;// WHAT INTERVIEWERS LOOK FOR
PARTITION BY + ORDER BY within OVER(). The deduplication pattern with WHERE rn = 1. Awareness of RANK vs ROW_NUMBER for ties.