Top SQL Interview Questions for Data Engineers cheat sheet
Joins, window functions, aggregation traps, NULL semantics, and query patterns that come up in nearly every data engineering interview.
Joins and set operations
Explain the difference between INNER, LEFT, RIGHT, and FULL OUTER JOIN.- INNER returns only matching rows; LEFT keeps all left rows and NULL-fills unmatched right columns; RIGHT is the mirror; FULL OUTER keeps unmatched rows from both sides. A strong answer adds that filters on the right table in a LEFT JOIN's WHERE clause silently turn it into an INNER JOIN — put them in the ON clause instead.
What is the difference between UNION and UNION ALL?- UNION deduplicates the combined result, which forces a sort or hash across all columns; UNION ALL simply concatenates. Default to UNION ALL in pipelines — it is much cheaper and duplicates are usually impossible or handled explicitly.
When would a CROSS JOIN be useful?- Generating combinations deliberately: a date spine joined to every entity to produce dense time series, or pairing every test variant with every segment. It also appears implicitly with LATERAL or table functions like FLATTEN and UNNEST.
What causes a fan-out (row explosion) in a join, and how do you detect it?- Joining on a key that is not unique on the side you assumed was unique multiplies rows — for example joining orders to a payments table with multiple rows per order. Detect it by comparing counts before and after the join, or asserting uniqueness with GROUP BY key HAVING COUNT(*) > 1.
How do NULLs behave in join keys?- NULL never equals NULL, so rows with NULL keys never match in an equi-join and drop out of INNER JOINs. If you must match NULLs, use IS NOT DISTINCT FROM (or COALESCE to a sentinel, carefully) and know your engine's support.
What is a semi-join and an anti-join?- A semi-join returns left rows that have at least one match (EXISTS or IN), without duplicating for multiple matches; an anti-join returns left rows with no match (NOT EXISTS). Prefer NOT EXISTS over NOT IN because NOT IN returns zero rows if the subquery contains any NULL.
Window functions
What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?- ROW_NUMBER assigns unique sequential numbers even on ties; RANK gives ties the same rank and skips (1,1,3); DENSE_RANK gives ties the same rank without gaps (1,1,2). ROW_NUMBER is the standard tool for deduplication — keep the row where rn = 1.
How do you get the latest record per key?- Wrap ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) in a CTE and filter rn = 1. Alternatives include QUALIFY in Snowflake/BigQuery/Teradata, or DISTINCT ON in Postgres, but the ROW_NUMBER pattern is portable everywhere.
Why can't you use a window function in a WHERE clause?- Windows are evaluated after WHERE, GROUP BY, and HAVING in the logical processing order, so the value does not exist yet when WHERE runs. Filter in an outer query or CTE, or use QUALIFY where the dialect supports it.
Explain window frames and the default frame gotcha with LAST_VALUE.- A frame defines which rows within the partition feed the function; with an ORDER BY, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That default makes LAST_VALUE return the current row — you must specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the true last value.
How would you compute a 7-day moving average?- AVG(x) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for a 7-row window, or RANGE with an interval when dates have gaps and you want a true calendar window. State the distinction — interviewers use it to separate rows-based from value-based frames.
How do you sessionize clickstream events in pure SQL?- Use LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) to flag rows where the gap exceeds the timeout (say 30 minutes) as session starts, then a running SUM of that flag becomes the session ID. It is the classic two-window gaps-and-islands pattern.
What does NTILE do and when is it misleading?- NTILE(n) splits ordered rows into n buckets as equally as possible, useful for quartiles or deciles. It is misleading with heavy ties because equal values can land in different buckets — percentile functions like PERCENT_RANK behave more predictably there.
Aggregation and NULL semantics
What is the difference between COUNT(*), COUNT(col), and COUNT(DISTINCT col)?- COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL; COUNT(DISTINCT col) counts unique non-NULL values. On large datasets, mention approximate alternatives like APPROX_COUNT_DISTINCT/HLL when exactness is not required.
Explain HAVING vs WHERE.- WHERE filters rows before grouping; HAVING filters groups after aggregation. Pushing conditions into WHERE when they do not need aggregates is cheaper because fewer rows reach the GROUP BY.
How do NULLs behave in aggregates and comparisons?- Aggregates ignore NULLs (AVG divides by non-NULL count only), any comparison with NULL yields UNKNOWN, and NULLs group together in GROUP BY and DISTINCT. Also know sort placement: Postgres puts NULLs last ascending by default, and NULLS FIRST/LAST overrides it.
What does GROUP BY GROUPING SETS / ROLLUP / CUBE do?- They compute multiple grouping levels in one pass: ROLLUP(a,b) gives (a,b), (a), and grand total; CUBE gives all combinations; GROUPING SETS lets you list exactly the sets you want. GROUPING() distinguishes real NULLs from subtotal rows.
Write the idea behind a pivot without a PIVOT keyword.- Conditional aggregation: SUM(CASE WHEN month = 'Jan' THEN amount END) per target column, grouped by the row dimension. It is portable across every engine and easier to code-generate than dialect-specific PIVOT syntax.
How do you find duplicates in a table?- GROUP BY the business key with HAVING COUNT(*) > 1 to identify them, or ROW_NUMBER partitioned by the key to enumerate and delete/keep specific copies. Clarify whether duplicate means full-row duplicate or duplicate key with differing attributes — that changes the fix.
Query design and performance
What is a CTE and does it materialize?- A CTE names a subquery for readability and reuse. Materialization is engine-specific: Postgres 12+ inlines CTEs unless MATERIALIZED is specified or they are referenced multiple times; many warehouses treat them purely as text expansion — so a CTE referenced twice may be computed twice.
How do you read an execution plan? What do you look for first?- Start with the most expensive operator and check whether row estimates match actuals — bad cardinality estimates cascade into bad join strategies. Then look for full scans where pruning or indexes were expected, exploding joins, and sorts or spills that indicate memory pressure.
Explain predicate pushdown and why SARGability matters.- Engines push filters down to the scan so less data is read, but wrapping the column in a function — WHERE DATE(ts) = '2026-01-01' — can defeat index use and pruning. Rewrite as a range: ts >= '2026-01-01' AND ts < '2026-01-02'.
When is EXISTS faster than IN, and where does NOT IN go wrong?- EXISTS can short-circuit on the first match and modern optimizers often plan IN and EXISTS identically for clean cases. The real trap is NOT IN: if the subquery returns even one NULL, the predicate is never true and the query returns zero rows — use NOT EXISTS.
How would you delete duplicates from a large table safely?- Prefer rebuilding: CREATE TABLE AS SELECT with ROW_NUMBER filtering rn = 1, validate counts, then swap names — cheaper and safer than in-place DELETE on columnar warehouses. In OLTP engines, delete by ctid/rowid from a ranked subquery inside a transaction, in batches.
Why is SELECT * discouraged in production pipelines?- Columnar engines read only referenced columns, so SELECT * scans everything and inflates cost; it also breaks downstream contracts when new columns appear and defeats some view/query result caching. Explicit column lists make schema changes deliberate.
Practical patterns and correctness
How do you handle a slowly changing dimension type 2 in SQL?- Each change closes the current row (setting valid_to and current_flag = FALSE) and inserts a new row with a new valid_from. Implementation is typically MERGE with a change-detection hash of tracked columns; point-in-time joins then use BETWEEN valid_from AND valid_to.
How do you generate a date spine and why do you need one?- Use a generator (generate_series in Postgres, GENERATOR in Snowflake, or a numbers table) to produce every date in range, then LEFT JOIN facts onto it. Without it, days with zero activity vanish from time series and moving averages are computed over the wrong denominators.
What is the gaps-and-islands problem?- Finding consecutive runs (islands) and breaks (gaps) in sequences — login streaks, sensor uptime. The standard trick is value minus ROW_NUMBER over the ordered sequence: consecutive values share a constant difference, which becomes the island's group key.
How would you compare two tables to find mismatched rows?- Symmetric difference via EXCEPT in both directions, or FULL OUTER JOIN on the key comparing column-wise with IS DISTINCT FROM to handle NULLs. For very wide tables, compare hashes of concatenated columns per key first, then drill into differing rows.
Explain transaction isolation levels in one pass.- READ UNCOMMITTED permits dirty reads; READ COMMITTED (Postgres default) prevents them but allows non-repeatable reads; REPEATABLE READ prevents those but can see phantoms in some engines; SERIALIZABLE makes transactions behave as if run one at a time. MVCC engines deliver these without read locks by keeping row versions.
What makes a query deterministic, and why do interviewers care?- ORDER BY on a non-unique key gives unspecified order among ties, LIMIT without a full ordering returns arbitrary rows, and functions like RANDOM or CURRENT_TIMESTAMP vary per run. Pipelines that rely on nondeterministic results produce flaky tests and irreproducible backfills — always break ties with a unique column.
From DataLane — tutorials at/blog, practice SQL live in theplayground.