SQL Mock Interview for Data Engineers

ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, and frame clauses. Window functions are the single most tested SQL topic in data engineering interviews. Interviewers use them because they separate candidates who memorized GROUP BY from those who understand how SQL processes ordered data. Patterns: Running totals and rolling averages with frame clauses; Ranking within partitions (the classic 'second highest per group' pattern); Comparing current row to previous row with LAG/LEAD; Percentile calculations using NTILE or PERCENT_RANK; Gaps-and-islands using ROW_NUMBER difference technique. Common mistake: Using RANK when the question needs DENSE_RANK. RANK skips numbers after ties (1, 1, 3). DENSE_RANK does not (1, 1, 2). When the question says 'find the second highest,' filtering WHERE rank = 2 returns nothing if the top value is tied. Interviewers test this on purpose.

INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN, self-joins, and inequality joins. Joins form the backbone of every multi-table query. Interviewers use join questions to test whether you understand NULL behavior, many-to-many relationships, and accidental row multiplication. Patterns: LEFT JOIN + IS NULL to find missing records (customers with no orders); Self-join to compare rows within the same table (employee pairs, consecutive events); Inequality join to match events within a time window; CROSS JOIN to generate all combinations, then LEFT JOIN to find gaps; Multiple joins with careful attention to join order and cardinality. Common mistake: Forgetting that LEFT JOIN followed by a WHERE filter on the right table converts it to an INNER JOIN. If you write LEFT JOIN orders o ON ... WHERE o.status = 'shipped', any customer with no orders gets filtered out. Move the condition into the ON clause instead.

GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX, and conditional aggregation with CASE WHEN. GROUP BY is the most frequently tested SQL keyword overall. Interviewers use aggregation questions to check your understanding of grouping granularity and filtered counting. Patterns: Conditional aggregation: SUM(CASE WHEN ... THEN 1 ELSE 0 END) for pivoting; HAVING to filter groups after aggregation; COUNT(DISTINCT ...) for unique value counting; Nested aggregations: average of per-group counts; Percentage calculations: group count divided by total count. Common mistake: Putting aggregate filters in WHERE instead of HAVING. WHERE filters individual rows before grouping. HAVING filters groups after aggregation. If you write WHERE COUNT(*) > 5, you get a syntax error. The correct version: HAVING COUNT(*) > 5.

Common Table Expressions for readable multi-step queries. CTEs let you break a complex query into named, sequential stages. Interviewers test whether you can decompose a problem into logical steps rather than nesting subqueries four levels deep. Patterns: Multi-step transformations: aggregate, then filter, then rank; Recursive CTEs for hierarchical data (org charts, category trees); CTE-based deduplication with ROW_NUMBER; Chaining CTEs for month-over-month or year-over-year comparisons; Date spine generation using recursive CTE. Common mistake: Over-nesting instead of using CTEs. A three-level nested subquery is hard to read, hard to debug, and signals to the interviewer that you do not know CTE syntax. Some candidates avoid CTEs because they think they are 'slower.' In practice, most query engines optimize CTEs identically to subqueries.

Scalar subqueries, correlated subqueries, and EXISTS/NOT EXISTS. Subqueries test whether you can think about data access at two levels simultaneously. Correlated subqueries in particular are tricky because they execute conceptually once per outer row. Patterns: EXISTS/NOT EXISTS for set membership tests; Correlated subquery for per-row comparisons (salary vs department average); Scalar subquery in SELECT for single-value lookups; Subquery in FROM (derived table) for pre-aggregation; NOT EXISTS as a performant alternative to NOT IN (handles NULLs correctly). Common mistake: Using NOT IN instead of NOT EXISTS when the subquery column contains NULLs. NOT IN returns no rows if any value in the list is NULL, because NULL != anything is unknown. NOT EXISTS handles this correctly. Interviewers love this gotcha.

A flashcard asks: 'What does ROW_NUMBER do?' You answer: 'Assigns a sequential integer to rows within a partition.' Correct. Now the interviewer asks: 'Write a query to deduplicate a table with 5 million rows, keeping the most recent entry per user.' Can you translate that knowledge into working SQL under time pressure? That translation step is what mock interviews with real execution train.

The most common SQL mistakes in interviews are syntax errors: missing commas, wrong function names, GROUP BY mismatches, WHERE vs HAVING confusion. You can know the concepts perfectly and still make these errors under pressure. The only way to reduce them is to write hundreds of queries and fix hundreds of errors. DataDriven gives you that repetition with real database feedback.

Your query returns 47 rows when the expected answer is 45. Where is the bug? Is it the join creating duplicates? A missing WHERE filter? A window function partition issue? Debugging SQL requires a systematic process: check row counts at each step, examine join cardinality, verify GROUP BY granularity. You cannot practice this process without running real queries against real data.

Single-table aggregations, basic joins, simple filtering with WHERE and HAVING. These questions test fundamental SQL literacy. If you cannot solve easy questions consistently in under 12 minutes, you need more time on fundamentals before mock interviews. Example: Find the top 3 departments by total salary using GROUP BY and ORDER BY with LIMIT.

Multi-table joins, window functions, CTEs, and conditional aggregation. Most interview questions fall into this range. A medium question on DataDriven matches what you would see in round 1 at Meta or Amazon. The time target of 15 to 25 minutes accounts for thinking, writing, and testing. Example: Calculate a 7-day rolling average of daily revenue, handling days with no orders.

Multi-CTE patterns, recursive queries, complex window frames, and performance-sensitive solutions. Hard questions target senior and staff candidates. They typically require 3 or more logical steps and at least one non-obvious insight (like recognizing a gaps-and-islands pattern or using a self-join for sessionization). Example: Identify user sessions where a session ends after 30 minutes of inactivity, then calculate the median session duration per user.