The most frequently asked SQL questions in real data engineering interviews at top tech companies. Sorted by how often they appear.
SQL remains the most critical skill tested in data engineering interviews. These questions cover window functions (ROW_NUMBER, RANK, DENSE_RANK), complex joins, CTEs, recursive queries, query optimization, indexing strategies, and real-world data transformation patterns. Each question includes a detailed answer and the companies that have asked it.
This collection contains 50 curated questions: 7 easy, 37 medium, and 6 hard. There's a strong foundation of fundamentals-focused questions — ideal for building confidence before tackling advanced topics.
The most frequently tested areas in this set are partition (24), join (20), sql (19), window (13), bigquery (10), and snowflake (7). Focusing on these topics will give you the highest return on your preparation time.
Start with the easy questions to warm up and solidify fundamentals. Medium-difficulty questions form the bulk of real interviews — spend the most time here and practice explaining your reasoning out loud. Hard questions often appear in senior and staff-level rounds; attempt them after you're comfortable with the basics. For each question, try answering before revealing the solution. Use our AI Mock Interview to simulate real interview conditions and get instant feedback on your responses.
Write an SQL query to find the second-highest salary from an employee table.
Demonstrate the difference between DENSE_RANK() and RANK()
Discuss differences between ROW_NUMBER(), RANK(), and DENSE_RANK(), and provide examples from your projects.
Explain the differences between a Data Lake and a Data Warehouse.
Explain the differences between Data Warehouse, Data Lake, and Delta Lake
Explain the differences between Repartition and Coalesce. When would you use each?
What is the difference between partitioning and bucketing in Spark, and when would you use bucketing?
Can you explain the difference between OLTP and OLAP?
Describe a scenario where partitioning and bucketing would improve query performance.
Describe a time when you had to optimize a slow SQL query. What steps did you take?
Explain Fact and Dimension Tables with examples.
Explain the concept of ACID properties in the context of databases.
Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Explain the types of triggers in ADF, including schedule, tumbling window, and event-based triggers.
How do you handle NULL values in SQL? Mention functions like COALESCE and NULLIF.
How do you remove duplicate rows in BigQuery?
Joins and window functions - INNER, LEFT, RIGHT, FULL OUTER, ROW_NUMBER(), RANK(), DENSE_RANK()
What is a Common Table Expression (CTE), and when would you use it?
What is the difference between a primary key and a unique key?
What is the difference between WHERE and HAVING clauses in SQL?
When would you choose a Snowflake schema over a Star schema?
Detail examples of inner, outer, left, and right joins.
Difference Between Internal and External Tables in BigQuery
Difference between ROW_NUMBER(), RANK(), and DENSE_RANK() with examples.
Difference between where and having clause with examples.
Explain Common Table Expressions (CTEs) and their benefits.
Explain SQL Window Functions with examples.
Explain the difference between UNION and UNION ALL.
Explain the use of the MERGE statement in SQL.
How do you handle NULL values in SQL? Mention functions like COALESCE and ISNULL.
How do you optimize a long-running SQL query?
How would you handle duplicate records in an SQL table?
Implement a query to find the top 5 customers by total sales amount.
SQL query to find the second highest salary from each department.
Triggers in ADF, especially tumbling window triggers.
What are primary keys and foreign keys? Why are they important?
What is a CTE (Common Table Expression)? What are its uses?
What is a self-join, and when would you use it?
What is a window function? Explain with an example.
What is normalization and denormalization? When would you use each?
What is the difference between a clustered and non-clustered index?
What is the difference between a view and a materialized view?
What is the difference between DELETE and TRUNCATE?
What is the difference between OLTP and OLAP?
Write a query to find the top three highest-paid employees in each department using window functions.
Write a SQL query to find top 3 earners in each department.
Write an SQL query to find duplicate emails in a users table.
Write complex SQL queries involving multiple joins, subqueries, and data aggregation logic.
Add a column to the Employees table that shows the name of the employee with the next higher employee_id.
Add a new column with manager names for each employee using a self-join.
Window functions (ROW_NUMBER, RANK, running totals), multi-table joins and anti-joins, aggregation with GROUP BY/HAVING, CTEs and recursive queries, deduplication, date/time logic, and query optimization (reading EXPLAIN plans, indexing, avoiding full scans). SQL appears in roughly 85% of data engineering loops and is where most rejections happen, so it deserves the most prep time.
INNER JOIN returns only rows with a match in both tables. LEFT JOIN returns all left-table rows plus matched right-table columns (NULLs where no match). FULL OUTER JOIN returns all rows from both sides, NULL-filling non-matches. A classic interview trap: filtering a LEFT-joined right table in the WHERE clause silently turns it into an INNER join — put that condition in the ON clause instead.
WHERE filters individual rows before aggregation; HAVING filters groups after GROUP BY aggregation. You can't use an aggregate like COUNT(*) in WHERE, but you can in HAVING. For example, WHERE status = 'active' narrows rows first, then HAVING COUNT(*) > 5 keeps only groups with more than five active rows.
Use a CTE (WITH clause) when a derived result is referenced multiple times or when readability matters — it names an intermediate step and reads top-to-bottom. Recursive CTEs also handle hierarchies (org charts, graph traversal) that subqueries can't. For a one-off, single-use filter, a subquery is fine; engines typically optimize both similarly.
To find duplicates, GROUP BY the columns that define uniqueness and keep groups with COUNT(*) > 1. To remove them, assign ROW_NUMBER() OVER (PARTITION BY <key> ORDER BY <tiebreaker>) and delete or keep only rows where the number equals 1. The window-function approach is the standard answer because it lets you control exactly which copy survives.
The Data Engineering Interview Answer Vault bundles 750+ reviewed answers into 7 focused PDF volumes — SQL, Spark, Python, System Design, Cloud, Behavioral, and Data Modeling. Study on any device, no subscription required.
800+ hands-on courses — Grokking System Design, Coding Patterns, and AI mock interviews for your DE loop.
Turn any topic or your own notes into an interactive, personalized course in 60 seconds.
The book that gets data engineers through system-design rounds. Essential reading.
Some links below are affiliate links. If you buy through them we may earn a small commission at no extra cost to you — it helps keep DataEngPrep free.
Reading answers is step one. Get instant AI feedback on your answers, run mock interviews, and track readiness — built specifically for data engineering interviews.