Loading Academy Hub...
Solve ranking, deduplication, running metrics, and period-over-period delta problems in pure SQL.
Window functions perform calculations across sets of rows that are related to the current row without collapsing the rows into a single summary row.
Given salaries: [100k, 90k, 90k, 80k]:
ROW_NUMBER(): 1, 2, 3, 4 (Strict unique sequential integers; arbitrary tie-breaker).RANK(): 1, 2, 2, 4 (Ties share rank; skips subsequent numbers).DENSE_RANK(): 1, 2, 2, 3 (Ties share rank; NO skipped numbers).A core pattern asked in Microsoft, Deloitte, and Walmart interviews:
WITH ranked_records AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY business_key
ORDER BY updated_at DESC
) AS rn
FROM staging_orders
)
SELECT * FROM ranked_records WHERE rn = 1;
LAG(col, 1): Retrieves value from 1 row prior in the partition (e.g. previous month's revenue).LEAD(col, 1): Retrieves value from 1 row ahead in the partition.LAST_VALUE): Whenever an OVER (PARTITION BY ... ORDER BY ...) clause omits ROWS BETWEEN ..., SQL silently defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. As a result, LAST_VALUE(price) returns only the current row's price unless you specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING!ROWS vs RANGE on Equal Timestamps: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW counts exact physical rows; RANGE groups all peer rows sharing identical ORDER BY keys into the current frame simultaneously.QUALIFY: Filters directly on window function outputs without wrapping everything in an extra CTE (QUALIFY ROW_NUMBER() OVER (...) = 1).Review the production code, company interview Q&As, incident traces, and comprehension quiz inside this lesson.
-- Top 3 Highest Earners Per Department (Handling Ties with DENSE_RANK)
WITH ranked_employees AS (
SELECT
emp_id,
emp_name,
dept_id,
salary,
DENSE_RANK() OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS salary_rank,
-- Period-over-period delta comparison using LAG
salary - LAG(salary, 1) OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS delta_from_higher_earner
FROM employees
)
SELECT emp_id, emp_name, dept_id, salary, salary_rank
FROM ranked_employees
WHERE salary_rank <= 3;
ERROR: window functions are not allowed in WHERE\nLINE 4: WHERE ROW_NUMBER() OVER(...) = 1