SQL interview questions data engineers actually get.

If you can't answer these cold, don't book the interview yet. They're the baseline every screen checks.

• Difference between INNER, LEFT, FULL OUTER, and CROSS JOIN. When does a LEFT JOIN silently drop rows? (Answer: it doesn't — but a WHERE on the right table turns it into an inner join.)
• WHERE vs HAVING vs QUALIFY. Order of evaluation. Why WHERE can't reference an aggregate.
• How does GROUP BY handle NULLs? (They form their own group.) How do aggregates handle NULLs? (Skipped, except COUNT(*).)
• Difference between UNION and UNION ALL. Which one costs a sort + distinct? Which one do you almost always want in a pipeline?
• What does COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column) return? One-line answers.

Windows are the single most-asked SQL topic in DE interviews. Know the shape without looking it up.

• Find the 2nd highest salary per department. DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) = 2. Say why DENSE_RANK over RANK over ROW_NUMBER — ties matter.
• Top-N per group. Same pattern with ROW_NUMBER. Filter in an outer query or with QUALIFY.
• 7-day rolling average of daily active users. AVG(dau) OVER (ORDER BY day RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW). Trap: days with zero events don't exist in the table — generate a date spine first.
• Cumulative sum of revenue per customer. SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS UNBOUNDED PRECEDING).
• Previous-row diff (day-over-day change). value - LAG(value) OVER (ORDER BY day).
• Percent of total per partition. amount / SUM(amount) OVER (PARTITION BY category).
• Difference between ROWS and RANGE frames. ROWS counts physical rows; RANGE counts logical values (ties collapse). Ask when it matters.

The pattern for sessionization, streak detection, and "consecutive days" problems. Master one template — it solves them all.

• Split events into sessions with a 30-min inactivity gap. Flag a new session when event_time - LAG(event_time) OVER (PARTITION BY user ORDER BY event_time) > INTERVAL '30' MINUTE, then running-sum the flag.
• Find the longest login streak per user. Row-number by date, subtract from date → constant per island. Group by that constant, take MAX(COUNT(*)).
• Find users who logged in 3 days in a row. Same island trick, filter islands with COUNT(*) >= 3.
• Merge overlapping time ranges per resource. Track running max of end-time, start a new island when a start > max end so far.

• Keep the latest row per key. QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1. Trap: what if updated_at ties? Add a tiebreaker (id, ingestion time) — otherwise output is non-deterministic.
• Find exact duplicates. GROUP BY every_column HAVING COUNT(*) > 1 — or ROW_NUMBER and keep rows where n > 1.
• How would you catch a silent duplication bug in a daily pipeline? Data-quality test on unique key: COUNT(*) = COUNT(DISTINCT pk). dbt unique test or a hand-rolled assertion.
• You joined two tables and row count exploded — what went wrong? Non-unique join key on one side (fan-out). Aggregate first, then join.

• Given orders(user_id, order_date, amount), compute month-over-month revenue growth. Aggregate to month, use LAG, compute (cur - prev) / prev.
• Compute customer retention: users who ordered in month N AND month N+1. Self-join monthly buckets or use array/set intersection functions.
• Compute daily active users, weekly active users, monthly active users from an events table. Three COUNT(DISTINCT user_id) queries with different date bucketing. Discuss the DAU/MAU ratio (stickiness).
• Pivot: turn one-row-per-event into one-row-per-user with columns per event type. MAX(CASE WHEN event = 'X' THEN 1 END) per column, or dialect-specific PIVOT.
• Unpivot: turn wide columns into long rows. UNION ALL per column, or UNPIVOT/CROSS JOIN LATERAL.

Any answer that reasons about IO, partitioning, and skew separates a mid from a senior candidate.

• Your query is slow — how do you diagnose it? Read the query plan first: what's the largest scan, where's the biggest shuffle, what's the join order? Only then talk about indexes or partitions.
• When does a subquery beat a window function (and vice versa)? Correlated subqueries are killers at scale; windows scan the partition once. Prefer windows unless the subquery filters the outer set dramatically first.
• How do you avoid a shuffle on a warehouse like Snowflake or BigQuery? Cluster/partition by the join or filter key. Aggregate before joining. Broadcast small tables.
• Column pruning and predicate pushdown — why do they matter for Parquet/Iceberg? Only relevant columns and partitions are read from object storage. A SELECT * across a wide table can cost 20× a targeted select.
• Explain data skew in a group-by or join. One key with 90% of rows sends everything to one worker. Fix with salting, two-stage aggregation, or a skew-hint.

• Restate the problem in your own words before writing anything. Confirm inputs, outputs, edge cases.
• Ask about NULLs, duplicates, and time zones — most trap questions hide there.
• Write CTEs top-down, not one giant query. Name each step. Interviewers grade readability.
• Say "I'd add a data-quality check here" when you make an assumption. Free senior points.
• If stuck, verbalize the shape of the answer ("I need one row per user per day, then rank, then filter"). They'd rather help you than watch you type in silence.

The SQL & warehousing courses drill windows, gaps-and-islands, and dbt patterns with exercises graded on the exact traps above. Bug Hunt ships broken queries and pipelines — you fix them and see the tests go green. If you can complete both, these interview questions become one-line answers instead of five-minute stumbles.