Guide
Senior SQL rounds are not about syntax. They are about whether you can reason over a query plan, defend a data model, and explain the trade-off you just made. This guide collects the questions that actually separate senior candidates, with the answer a Senior Data Engineer interviewer is listening for — the same reasoning the AI mentor in the SQL playground gives on every submission.
Senior interviews rarely ask you to write a GROUP BY. They ask for running totals, gap-and-island detection, and per-partition ranking where a self-join would be too slow. Know the difference between ROWS and RANGE framing, and why RANK, DENSE_RANK and ROW_NUMBER produce different row counts after filtering.
Rank inside the partition and filter the rank: SELECT * FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) rn FROM orders o) t WHERE rn = 1. ROW_NUMBER guarantees exactly one row per customer even when two orders share a timestamp; RANK would return both.
AVG(revenue) OVER (PARTITION BY store_id ORDER BY day RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW). RANGE on a date column handles missing days correctly; ROWS BETWEEN 6 PRECEDING silently averages the last 7 *rows*, which is wrong when a store has no sales on some days.
Subtract a row number from the date: day - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) DAY gives a constant island key. Group by that key to get streak start, end and length.
Hierarchies, bill-of-materials explosions and date spines are the standard senior probes. The interviewer is watching for a termination condition, cycle protection and an awareness that recursion is a loop, not a set operation.
WITH RECURSIVE tree AS (SELECT id, manager_id, name, 1 AS depth FROM employees WHERE id = ? UNION ALL SELECT e.id, e.manager_id, e.name, t.depth + 1 FROM employees e JOIN tree t ON e.manager_id = t.id) SELECT * FROM tree. Add a path column and a NOT LIKE check on it if the data can contain cycles.
Recurse from the min date adding one day until the max date, then LEFT JOIN facts onto the spine. This is what turns a sparse events table into a chart-ready series without client-side gap filling.
At senior level, correctness is assumed and performance is the interview. Read the plan out loud: access method, join order, join algorithm, row estimates versus actuals. Say why the optimizer chose what it chose before proposing an index.
Compare estimated versus actual rows in EXPLAIN ANALYZE — a large gap points at stale statistics or a non-sargable predicate. Check for functions wrapping indexed columns (DATE(created_at) = ...), implicit type casts, leading wildcards, and OR chains that defeat index usage. Only then consider a covering or composite index, ordered by equality columns first, range column last.
When the query filters on both columns together. An index merge of two single-column indexes costs an extra intersection step; a composite index on (tenant_id, created_at) serves the filter and the sort in one range scan, and can become covering if you add the selected columns.
Identify the hot key, then either pre-aggregate the large side before joining, split the hot key into its own branch and UNION the results, or salt the key. In warehouses, also check partition pruning and clustering keys before touching the SQL.
Expect design discussion: slowly changing dimensions, grain, idempotent loads and how you would model late-arriving facts. Answer with the grain first — most wrong answers are wrong because the grain was never stated.
SCD type 2: one row per version with valid_from / valid_to and an is_current flag. Joins from the fact use the fact timestamp against the validity range, so historical reports stay correct after the attribute changes.
Make the batch deterministic by partition: delete-and-insert the affected partitions inside a transaction, or MERGE on a natural key with a deterministic dedupe (ROW_NUMBER over the key ordered by ingestion time). Re-running the same input must produce the same table.
Every submission is read semantically, not string-matched, so a correct answer written differently from the reference solution still passes. Alongside the verdict you get a performance note (why your plan would or would not hold at scale) and a best-practice tip. Deepen the theory in the MySQL visual tutorial, drill warehouse-side design in the GCP data engineer bank, or browse the broader SQL interview question set.