SQL Interview Q&A — Advanced & Scenario-Based¶
Last reviewed: October 2026
Senior SQL for data/analytics engineering: window functions, query tuning, data modeling, and the tricky semantics interviewers probe. Study at a glance, then open each question for depth.
How to use this page
Skim the 60-second talking points and rapid-fire for recall, then drill into the collapsible questions. Finish with the self-quiz. Related: Snowflake Interview Q&A · dbt Interview Q&A.
Study checklist¶
Can you explain each without notes?
- JOIN types and what each returns (incl. anti-joins)
- Window functions vs GROUP BY
-
RANKvsDENSE_RANKvsROW_NUMBER - CTEs, recursive CTEs, and readability vs performance
- How an index (or micro-partition pruning) speeds a query
-
WHEREvsHAVING,UNIONvsUNION ALL - NULL semantics (three-valued logic) and the traps
- Deduplication patterns
- Reading an execution plan
- SCD Type 2 and gaps-and-islands
60-second talking points¶
- "Window functions compute across rows without collapsing them." That's the difference from GROUP BY — you keep row detail plus an aggregate/rank.
- "NULL is 'unknown,' not zero or empty." It breaks equality, aggregates skip
it, and
NOT INwith a NULL bites you. - "Read the plan, don't guess." Full scan, bad join order, or no pruning shows up there.
Core concepts — simple, then the nuance¶
Window functions: explain it simply, then go deep
Simple: A window function does a calculation across a set of rows related to the current row, but unlike GROUP BY it doesn't merge them — every row stays.
The nuance: OVER (PARTITION BY ... ORDER BY ...) defines the window.
ROW_NUMBER/RANK/DENSE_RANK rank; LAG/LEAD look across rows; running totals
use SUM() OVER (ORDER BY ...). Frames (ROWS BETWEEN ...) control the window
extent. They're the tool for "top-N per group," running totals, period-over-
period, and dedup — all without a self-join.
NULL semantics: simple, then deep
Simple: NULL means "unknown." Comparing anything to NULL isn't true or false — it's unknown, so those rows quietly drop out of filters.
The nuance: x = NULL is never true (use IS NULL). NOT IN (subquery
with a NULL) returns no rows — a classic bug. Aggregates like SUM/AVG
skip NULLs but COUNT(*) counts them; COUNT(col) doesn't. Use
COALESCE/NULLIF deliberately, and prefer NOT EXISTS over NOT IN when
NULLs are possible.
Query patterns¶
Worked scenario: 'find the 2nd highest salary per department'
Reasoning:
1. Per-group ranking → window function, partition by department.
2. DENSE_RANK so ties don't skip the 2nd position (two people tied for #1
still leaves a #2). ROW_NUMBER would arbitrarily pick one.
3. Filter rnk = 2.
SELECT department, employee, salary FROM (
SELECT department, employee, salary,
DENSE_RANK() OVER (PARTITION BY department
ORDER BY salary DESC) AS rnk
FROM employees
) ranked WHERE rnk = 2;
Talking point: "The choice between ROW_NUMBER / RANK / DENSE_RANK is the interview — I'd ask how ties should behave."
RANK vs DENSE_RANK vs ROW_NUMBER?
All rank within a partition. ROW_NUMBER = unique 1,2,3 (ties broken arbitrarily). RANK = ties share a rank, then it skips (1,1,3). DENSE_RANK = ties share a rank, no gap (1,1,2). Pick by whether ties should share and whether you want gaps.
When do you use a window function instead of GROUP BY?
When you need the aggregate alongside the row detail (running totals, ranks, per-row share of group total, period-over-period), or top-N per group. GROUP BY collapses rows to one per group; a window keeps every row and adds the computed column. If you only need the summary, GROUP BY is simpler/cheaper.
How do you deduplicate rows keeping the most recent?
ROW_NUMBER() OVER (PARTITION BY natural_key ORDER BY updated_at DESC) then keep
rn = 1 (or QUALIFY rn = 1 in Snowflake). This picks one row per key
deterministically — better than DISTINCT (which can't choose which duplicate)
or GROUP BY with MAX on every column.
Explain the NOT IN + NULL trap.
If the subquery in NOT IN (...) returns even one NULL, the whole predicate
evaluates to unknown for every row, so you get zero rows — silently wrong.
Use NOT EXISTS (correlated) instead, which handles NULLs correctly, or filter
NULLs out of the subquery.
Performance & tuning¶
A query is slow. How do you approach it?
Read the execution plan first. Look for: full table scans where an index
or partition filter should apply; bad join order / method (nested loop on
big tables that should hash join); exploding joins (output rows >> inputs,
grain bug); sorts/spills; and functions on filtered columns that prevent
index/pruning use (WHERE UPPER(col) = ...). Fix the biggest node, remeasure.
Why can a function on a column kill index usage?
An index is on the raw column values. WHERE UPPER(name) = 'X' or
WHERE date(ts) = '...' forces a computation per row, so the engine can't use
the index/pruning on name/ts — it scans everything. Fix: store/compare in the
indexed form, use a functional index, or rewrite as a range (ts >= d AND ts <
d+1).
EXISTS vs IN vs JOIN — performance and correctness?
Often the optimizer treats them similarly, but: EXISTS short-circuits on the
first match and handles NULLs safely (best for "does a related row exist").
IN is fine for small, NULL-free lists. JOIN is right when you need columns
from both sides — but watch for row multiplication if the join isn't unique. For
"has any match," prefer EXISTS.
What makes a good index (or clustering) choice?
Index/cluster on columns used in selective filters and join keys, high cardinality, and frequent access paths. Composite index order matters (leftmost-prefix). Avoid indexing low-cardinality columns or over-indexing write-heavy tables (write cost). In Snowflake it's clustering/natural load order driving micro-partition pruning rather than b-tree indexes.
Data modeling¶
Star schema vs normalized (3NF) — when each?
3NF/normalized for OLTP: minimizes redundancy, fast writes, integrity. Star schema (fact + dimension tables) for analytics/OLAP: denormalized for fast, simple aggregation queries and BI usability. Warehouses favor star/wide tables because storage is cheap and scan/join patterns are predictable.
How do you model slowly changing dimensions (SCD Type 2)?
Keep history by adding a new row per change with valid_from/valid_to (and an
is_current flag), rather than overwriting (Type 1). Each business key can have
multiple versioned rows; joins to facts use the version valid at the event time.
Tools like dbt snapshots automate this.
Solve gaps-and-islands (consecutive runs).
Classic pattern: assign a group id via the difference between ROW_NUMBER() and
the sequence value, then aggregate per group. Example — find consecutive active
days:
An LLM generates SQL for business users. How do you make it safe and correct?
Safety: run under a read-only, least-privilege role scoped to curated
views, so row/column policies still apply; parse the SQL (e.g. with sqlglot)
and allow only SELECT on approved objects; add LIMIT, statement timeouts
and warehouse/credit caps; never concatenate user text into SQL you run with a
privileged role. Correctness: ground generation in a semantic layer
(metrics and joins defined once) plus verified example queries; validate with
EXPLAIN before running; check result sanity (row counts, NULL rates,
totals against known reports); and show the SQL and assumptions to the user.
Measure execution accuracy on a labeled question→answer set, comparing results
rather than SQL text, since many SQL strings are equally correct.
Rapid-fire¶
| Q | A |
|---|---|
WHERE vs HAVING? |
WHERE filters rows before grouping; HAVING filters groups after |
UNION vs UNION ALL? |
UNION dedups (sort/hash cost); UNION ALL keeps dups (faster) |
ROW_NUMBER vs RANK vs DENSE_RANK? |
unique / ties+gap / ties+no-gap |
| Anti-join? | LEFT JOIN + IS NULL (or NOT EXISTS) |
NOT IN + NULL? |
returns zero rows — use NOT EXISTS |
COUNT(*) vs COUNT(col)? |
counts all rows vs non-NULL values |
| CTE vs subquery? | CTE = named, readable, reusable in the query |
| SCD2? | versioned dimension rows with valid_from/valid_to |
| Star schema? | fact + denormalized dimensions for analytics |
| Dedup keep-latest? | ROW_NUMBER over key ORDER BY ts DESC, keep rn=1 |
Pitfalls interviewers probe¶
- Using
= NULLinstead ofIS NULL. NOT INwith a nullable subquery.- Functions on filter columns defeating indexes/pruning.
- Confusing WHERE vs HAVING.
UNIONwhenUNION ALLis meant (silent dedup + sort cost).- Assuming JOIN won't multiply rows (non-unique key).
Self-quiz¶
- Find the 2nd-highest salary per department — which ranking function and why?
- Explain the
NOT IN+ NULL trap and the fix. - When is a window function better than GROUP BY?
- Why does
WHERE UPPER(col)=...prevent index use? - EXISTS vs IN vs JOIN — pick one per scenario.
- Model an SCD Type 2 dimension.
- Deduplicate keeping the latest row per key.
- Solve consecutive-runs (gaps-and-islands).
Cross-links
Related: Snowflake Interview Q&A · dbt Interview Q&A · Data Engineering Interview Q&A