SQL Interview Deep Dive
Window functions are one of the highest-frequency SQL topics in data engineering interviews. PARTITION BY, ROW_NUMBER, and RANK/DENSE_RANK appear constantly. Master them with real execution.
Write the query, run it against a real database, and verify your output row by row.
ROW_NUMBER
RANK / DENSE_RANK
LAG / LEAD
SUM / AVG OVER
NTILE
FIRST_VALUE / LAST_VALUE
Every function below is tested in data engineering interviews. Sorted by interview frequency, based on DataDriven's analysis of verified interview data.
Frequency
Function
Category
ROW_NUMBER() OVER (PARTITION BY col ORDER BY col)Assigns a unique sequential number to each row within a partition. No ties, no gaps. Appears in 6.2% of all DE interviews, making it the most commonly tested window function.
Interview tip: Interviewers love asking 'get the Nth row per group' questions. ROW_NUMBER with a CTE wrapper is the standard pattern.
Frequency
Function
Category
RANK() OVER (PARTITION BY col ORDER BY col DESC)RANK assigns the same number to ties but leaves gaps (1, 2, 2, 4). DENSE_RANK has no gaps (1, 2, 2, 3). Combined, they appear in 4.9% of all DE interviews.
Interview tip: The follow-up question is always 'what if there are ties?' Know the difference between RANK and DENSE_RANK cold.
Frequency
Function
Category
LAG(col, offset, default) OVER (ORDER BY col)Access a value from a previous row (LAG) or next row (LEAD) without a self-join. Essential for time-series comparisons.
Interview tip: LAG/LEAD questions almost always involve calculating a delta or identifying a change. Think 'compare to previous row' whenever you see time-series data.
Frequency
Function
Category
SUM(col) OVER (ORDER BY col ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)Running totals, moving averages, and cumulative sums. The window frame clause (ROWS BETWEEN) is what makes these powerful.
Interview tip: Frame clauses trip up many candidates. Know the difference between ROWS and RANGE, and between UNBOUNDED PRECEDING and N PRECEDING.
Frequency
Function
Category
NTILE(n) OVER (ORDER BY col)Divides rows into N roughly equal buckets. Useful for percentile analysis and bucketing.
Interview tip: Less commonly tested than ROW_NUMBER or LAG, but it shows up in 'segment users into groups' questions. Quick to implement, impressive when used correctly.
Frequency
Function
Category
FIRST_VALUE(col) OVER (PARTITION BY col ORDER BY col)Get the first or last value in a window. Watch out: LAST_VALUE needs a proper frame clause or it returns the current row.
Interview tip: LAST_VALUE is a trap question. Without ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, it defaults to the current row. Interviewers use this to test frame clause knowledge.
Most window function interview questions are variations of these three patterns. Learn them cold.
Get the top N rows within each group. The most common window function interview pattern.
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rn
FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;Cumulative sum that grows with each row. Critical for financial and time-series queries.
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS UNBOUNDED PRECEDING
) AS running_total
FROM orders;Compare each row to the previous period. The go-to pattern for growth analysis questions.
SELECT
month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly_revenue;Reading about window functions is not the same as writing them under pressure. Practice with real SQL execution and timed drills.