Top Data Engineer SQL Interview Questions: Complete Guide

SQL is one of the first things interviewers use to measure whether a data engineer can actually work with real data. You can talk about pipelines, cloud tools, and architectures all day, but if you can’t extract, clean, join, or compare data with SQL, nothing else works. That’s why SQL questions appear in almost every interview—from entry-level to senior roles.

These questions aren’t random puzzles. They tell hiring managers how you think, how you solve problems, and how you deal with messy tables, missing values, and large datasets. A simple task like finding the second-highest salary or identifying duplicate records reveals more about your skills than explaining any theory.

As you move through the next section, you’ll find the most common SQL questions companies ask. Each one reflects real situations you’ll face on the job, so preparing with them builds confidence and speed. Let’s jump into the questions and help you walk into your interview ready to think clearly and answer with purpose.

Data Engineer SQL Interview Questions

Basic SQL Questions

What is the difference between WHERE and HAVING?

Interviewers use this question to see if you understand how SQL processes data from start to finish. Many people memorize syntax but can’t explain the order of operations, which leads to mistakes in filtering. WHERE filters individual rows before any grouping is done, while HAVING filters the grouped results after aggregation. If you explain this clearly, it shows you can think through how a query runs and avoid common errors in reporting or data pipelines.

Explain GROUP BY with an example.

This question helps interviewers judge whether you can take raw data and turn it into useful summaries. A data engineer often needs totals, averages, or counts, and GROUP BY is the main tool for that. For example, if you have a table of sales records, grouping by salesperson lets you calculate each person’s total sales. Showing that you understand how it works proves you can organize large datasets into meaningful insights.

What are the different types of JOINs?

Interviewers ask this because joining tables is something you will do constantly. If you choose the wrong JOIN, you can lose rows or double-count data without realizing it. INNER JOIN returns only matching records, LEFT JOIN keeps all rows from the left table, RIGHT JOIN keeps all rows from the right table, and FULL JOIN keeps everything from both. A clear explanation shows you understand relationships across tables and can combine data safely.

How do you find duplicates in a table?

This question checks if you can handle messy, real-world data. Duplicates create wrong counts, false trends, and broken dashboards. The usual approach is to group by the column you want to check, count the occurrences, and keep groups where the count is greater than one. A confident answer shows you know how to maintain quality in datasets and catch issues early.

What is the difference between UNION and UNION ALL?

Interviewers ask this to see if you understand how SQL blends results and how your choices impact performance. UNION combines rows from multiple queries but removes duplicates, which takes extra processing. UNION ALL keeps everything, even repeated rows. If you can explain the trade-off—clean results versus speed—it shows you think about efficiency as well as correctness.

Define primary key and foreign key.

This question reveals whether you understand how relational databases stay organized. A primary key uniquely identifies each row, while a foreign key links one table to another by referencing that primary key. A simple, accurate explanation shows that you can design tables that stay consistent and avoid broken relationships, which is essential for building reliable pipelines.

Explain normalization vs denormalization.

Interviewers ask this to test your judgment, not your memory. Normalization reduces duplicate data by splitting information into smaller tables, which keeps storage clean and avoids inconsistencies. Denormalization combines tables to make reading faster, even if some information repeats. Knowing when to use each approach shows you can balance storage, speed, and accuracy—an important skill for real data systems.

Intermediate SQL Questions

Write a query to find the second-highest salary.

Interviewers love this question because it checks if you can think beyond MAX() and handle ranking logic. A simple way is to get the highest salary that is less than the overall max. For example, you select the maximum salary from employees where the salary is lower than the global maximum salary. This shows you understand both filters and aggregates, not just basic SELECTs.

How do you fetch the top N records per group?

This question tests whether you can mix grouping with ranking, which shows up all the time in reporting. A common pattern is to use a window function like ROW_NUMBER() with PARTITION BY. You partition by the group (for example, department_id) and order by a metric (like salary), then keep only rows where the row number is less than or equal to N. This proves you can pull “top X per category” instead of just global rankings.

Explain ROW_NUMBER, RANK, and DENSE_RANK.

Here, the interviewer wants to see if you understand how rows are ordered and how ties are handled. ROW_NUMBER() gives a unique sequence with no ties—1, 2, 3, 4—even if two rows have the same value. RANK() gives the same number to ties but skips the next rank, like 1, 2, 2, 4. DENSE_RANK() also gives the same number to ties but doesn’t skip, like 1, 2, 2, 3. If you can explain this clearly, it shows you can choose the right function based on how the business wants to see the data.

How do you handle NULL values in SQL?

This question checks how careful you are with edge cases. NULLs can break comparisons and calculations if you ignore them. You can use COALESCE() to replace NULL with a default value, such as zero or an empty string. You also use IS NULL and IS NOT NULL instead of = to test for missing data. A good answer shows that you respect NULLs as “unknown” values and adjust your logic so queries stay accurate.

Write a query to calculate a running total.

Interviewers ask this to see if you can handle time-based or cumulative metrics, like revenue over days or signups over weeks. The common pattern uses a window function, such as SUM(amount) OVER (ORDER BY date_column). This adds each row’s value to all previous rows based on the sort order. If you mention partitioning by a group (like customer or region) for separate running totals, you make the answer even stronger.

How do you filter rows using window functions?

This question checks if you understand that window functions run after the row-level data is read, so you cannot use them directly inside WHERE. The usual trick is to wrap the window function in a subquery or CTE, then filter in the outer query. For example, you assign ROW_NUMBER() in an inner query and, in the outer query, keep only rows where that row number equals 1. This shows you know how to combine window logic with filters without breaking SQL’s order of operations.

Write a query to get departments with above-average salaries.

This is a classic way to test grouping, aggregates, and comparisons in one problem. You first group by department, compute the average salary for each, then compare it to the overall average salary. Most people either use a subquery for the global average or join the grouped result to another query that calculates the overall average. A solid answer proves you can think in terms of “group vs overall” and build queries that match real business questions, such as which teams perform better than the company average.

Advanced SQL Questions

Write a query to find the second-highest salary.

Interviewers love this question because it checks if you can think beyond MAX() and handle ranking logic. A simple way is to get the highest salary that is less than the overall max. For example, you select the maximum salary from employees where the salary is lower than the global maximum salary. This shows you understand both filters and aggregates, not just basic SELECTs.

How do you fetch the top N records per group?

This question tests whether you can mix grouping with ranking, which shows up all the time in reporting. A common pattern is to use a window function like ROW_NUMBER() with PARTITION BY. You partition by the group (for example, department_id) and order by a metric (like salary), then keep only rows where the row number is less than or equal to N. This proves you can pull “top X per category” instead of just global rankings.

Explain ROW_NUMBER, RANK, and DENSE_RANK.

Here, the interviewer wants to see if you understand how rows are ordered and how ties are handled. ROW_NUMBER() gives a unique sequence with no ties—1, 2, 3, 4—even if two rows have the same value. RANK() gives the same number to ties but skips the next rank, like 1, 2, 2, 4. DENSE_RANK() also gives the same number to ties but doesn’t skip, like 1, 2, 2, 3. If you can explain this clearly, it shows you can choose the right function based on how the business wants to see the data.

How do you handle NULL values in SQL?

This question checks how careful you are with edge cases. NULLs can break comparisons and calculations if you ignore them. You can use COALESCE() to replace NULL with a default value, such as zero or an empty string. You also use IS NULL and IS NOT NULL instead of = to test for missing data. A good answer shows that you respect NULLs as “unknown” values and adjust your logic so queries stay accurate.

Write a query to calculate a running total.

Interviewers ask this to see if you can handle time-based or cumulative metrics, like revenue over days or signups over weeks. The common pattern uses a window function, such as SUM(amount) OVER (ORDER BY date_column). This adds each row’s value to all previous rows based on the sort order. If you mention partitioning by a group (like customer or region) for separate running totals, you make the answer even stronger.

How do you filter rows using window functions?

This question checks if you understand that window functions run after the row-level data is read, so you cannot use them directly inside WHERE. The usual trick is to wrap the window function in a subquery or CTE, then filter in the outer query. For example, you assign ROW_NUMBER() in an inner query and, in the outer query, keep only rows where that row number equals 1. This shows you know how to combine window logic with filters without breaking SQL’s order of operations.

Write a query to get departments with above-average salaries.

This is a classic way to test grouping, aggregates, and comparisons in one problem. You first group by department, compute the average salary for each, then compare it to the overall average salary. Most people either use a subquery for the global average or join the grouped result to another query that calculates the overall average. A solid answer proves you can think in terms of “group vs overall” and build queries that match real business questions, such as which teams perform better than the company average.

Real-World Scenario Questions

How do you join a fact table with multiple dimension tables?

Interviewers ask this to see if you understand how analytical databases are built. Fact tables often store events or transactions, while dimension tables hold details like customer, product, or location. A strong engineer knows how to connect them without breaking counts or duplicating rows. The typical approach is using INNER or LEFT JOINs based on matching keys, such as customer_id or product_id. A good answer shows you understand relationships, think about join direction, and can produce clean results for reporting.

Write a query to find users who purchased last month but not this month.

This question tests your ability to compare activity between time periods, which is common in retention analysis. You usually filter purchases for each month, group by user_id, and then use a LEFT JOIN to see who appears in the first list but not the second. A simple filter like WHERE month2.user_id IS NULL finishes the logic. Interviewers like this question because it shows whether you can work with time logic and exclusion conditions at the same time.

How do you load only incremental data using SQL?

Companies want data engineers who avoid reprocessing huge tables. This question checks if you understand incremental logic—pulling only new or updated rows. The usual method is to compare timestamps or version columns, then select rows greater than the last processed value. If you mention storing the “last loaded date,” your answer becomes even stronger. This shows you think about speed, cost, and long-term maintenance.

Write a query to detect late-arriving records.

Late data breaks dashboards and metrics, so interviewers want to know if you can spot it. You compare the event_time to the load_time and check cases where the event happened much earlier than it was recorded. A simple condition like WHERE load_time > event_time + INTERVAL 'X' identifies issues. This shows you can catch hidden problems in pipelines instead of assuming data is always clean.

How do you validate SQL results before loading them into a pipeline?

This question checks if you work with care and think beyond writing queries. A good answer includes checking row counts, verifying NULL patterns, comparing totals with previous runs, and validating key relationships. Interviewers use this question to see if you treat data like something that can break, not something that always behaves. A clear process proves you can prevent issues before they reach dashboards or downstream systems.

Conclusion

SQL interviews aren’t about memorizing tricks—they’re about showing you can think clearly with data. The questions you practiced here reflect the same problems you’ll face on real pipelines: joining messy tables, spotting bad data, ranking values, comparing time periods, and summarizing information without breaking accuracy.

If you understand the ideas behind each question instead of hunting for one perfect query, you’ll walk into your interview with confidence. Keep practicing with real datasets, mix in a few tougher scenarios, and focus on writing queries that are both correct and easy to read. That combination is what sets strong data engineers apart.