SQL is the round almost every data-engineer interview starts with. Interviewers watch three things: whether you clarify the data first (what is one row, what is the key, can it have duplicates or NULLs), whether your query survives edge cases, and whether you can explain why it will or will not scale. Here are 10 questions that come up again and again, each with a model answer. The runnable examples use a tiny practice dataset (at the end), and every output shown was produced by actually running the query.
1. What is the difference between WHERE and HAVING?
Level: Easy. What the interviewer is testing: logical query order and the difference between filtering rows and filtering groups.
WHERE filters individual rows before grouping. HAVING filters groups after aggregation, so it can use aggregate functions. The logical order is FROM/JOIN, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT.
SELECT customer_id, COUNT(*) AS paid_orders, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid' -- row filter, runs first
GROUP BY customer_id
HAVING SUM(amount) > 1000; -- group filter, runs after aggregation
Output (run on the practice dataset in SQLite):
| customer_id | paid_orders | revenue |
|---|---|---|
| 1 | 3 | 2600 |
| 3 | 3 | 3150 |
Put conditions in WHERE whenever you can. It reduces the rows that reach the aggregation.
2. How do NULLs behave in COUNT, comparisons and NOT IN?
Level: Medium. What the interviewer is testing: NULL semantics and the NOT IN trap, which breaks real pipelines.
COUNT(*) counts rows. COUNT(col) counts non-NULL values. Any comparison with NULL (=, <>, >) yields UNKNOWN, which WHERE treats as false. NOT IN is dangerous: if the subquery returns even one NULL, x NOT IN (...) can never be true.
SELECT COUNT(*) AS all_rows, COUNT(amount) AS non_null_amount,
(SELECT COUNT(*) FROM customers
WHERE customer_id NOT IN (SELECT amount FROM orders)) AS not_in_with_null
FROM orders;
Output (run on the practice dataset in SQLite):
| all_rows | non_null_amount | not_in_with_null |
|---|---|---|
| 9 | 8 | 0 |
The last column is 0 because amount contains a NULL. Prefer NOT EXISTS, or filter NULLs out of the subquery.
3. Explain ROW_NUMBER, RANK and DENSE_RANK.
Level: Easy. What the interviewer is testing: the three ranking functions and what happens on ties.
All three number rows inside a partition by an ordering. On ties: ROW_NUMBER gives distinct numbers arbitrarily, RANK gives equal numbers then skips (1, 2, 2, 4), and DENSE_RANK gives equal numbers with no gaps (1, 2, 2, 3).
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk
FROM employees
ORDER BY dept, salary DESC, name;
Output (run on the practice dataset in SQLite):
| name | dept | salary | rn | rnk | drnk |
|---|---|---|---|---|---|
| Neha | Eng | 3000 | 1 | 1 | 1 |
| Omar | Eng | 2500 | 2 | 2 | 2 |
| Pia | Eng | 2500 | 3 | 2 | 2 |
| Quin | Eng | 2000 | 4 | 4 | 3 |
| Raj | Ops | 1800 | 1 | 1 | 1 |
| Sana | Ops | 1800 | 2 | 1 | 1 |
| Tej | Ops | 1500 | 3 | 3 | 2 |
If ROW_NUMBER ties must resolve the same way every run, add a tie-breaker column to ORDER BY.
4. A table has several rows per key. Keep only the latest row per key.
Level: Medium. What the interviewer is testing: deduplication is the most common real-world task; ties and determinism matter.
Number rows per key, newest first, and keep number 1. Add a deterministic tie-breaker so reruns give the same answer.
CREATE TABLE cust_updates (customer_id INT, city TEXT, updated_at TEXT);
INSERT INTO cust_updates VALUES (1,'Pune','2025-01-01'),(1,'Bengaluru','2025-03-01'),(2,'Delhi','2025-02-01'),(2,'Delhi','2025-02-01');
SELECT customer_id, city, updated_at FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, city) AS rn
FROM cust_updates)
WHERE rn = 1 ORDER BY customer_id;
Output (run on the practice dataset in SQLite):
| customer_id | city | updated_at |
|---|---|---|
| 1 | Bengaluru | 2025-03-01 |
| 2 | Delhi | 2025-02-01 |
To only detect duplicates use GROUP BY key HAVING COUNT(*) > 1. To delete duplicates in a warehouse, prefer building a deduplicated copy and swapping it in.
5. Show month-over-month revenue change.
Level: Medium. What the interviewer is testing: using LAG for period-over-period metrics and handling the first row.
Aggregate to one row per month first, then use LAG to fetch the previous month. The first month has no previous value, so the change is NULL; do not default it to zero without saying so.
WITH m AS (
SELECT substr(order_date,1,7) AS month, SUM(amount) AS revenue
FROM orders WHERE status='paid' GROUP BY 1)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM m ORDER BY month;
Output (run on the practice dataset in SQLite):
| month | revenue | prev | change |
|---|---|---|---|
| 2025-06 | 4300 | NULL | NULL |
| 2025-07 | 2350 | 4300 | -1950 |
6. For each user, find the longest streak of consecutive login days.
Level: Hard. What the interviewer is testing: the classic gaps-and-islands pattern.
Remove duplicate days, number each user’s dates, and subtract the row number (as days) from the date. Consecutive days produce the same difference, so that difference identifies an island. Group by it and count.
WITH d AS (SELECT DISTINCT user_id, login_date FROM logins),
g AS (
SELECT user_id, login_date,
julianday(login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
FROM d)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM g GROUP BY user_id, grp ORDER BY user_id, days DESC;
Output (run on the practice dataset in SQLite):
| user_id | streak_start | streak_end | days |
|---|---|---|---|
| 1 | 2025-08-01 | 2025-08-03 | 3 |
| 1 | 2025-08-05 | 2025-08-05 | 1 |
| 2 | 2025-08-03 | 2025-08-06 | 4 |
| 2 | 2025-08-01 | 2025-08-01 | 1 |
julianday is SQLite. In Snowflake use DATEADD(day, -ROW_NUMBER() OVER (...), login_date); in Spark SQL use date_sub. Always dedupe first, otherwise duplicate days break the arithmetic.
7. Explain SCD Type 1, 2 and 3.
Level: Easy. What the interviewer is testing: slowly changing dimensions: the three standard types.
Type 1 overwrites the old value: simple, no history. Type 2 keeps history by adding a new row per change with validity dates and a current flag; this is the standard choice when reports must show “what was true then”. Type 3 adds a column for the previous value: limited history, rarely used. Mention the cost: Type 2 grows the table and makes joins time-aware.
8. How does MERGE work, and what can go wrong?
Level: Medium. What the interviewer is testing: MERGE semantics and its main failure mode.
MERGE matches a target to a source on a key, then runs WHEN MATCHED (update or delete) and WHEN NOT MATCHED (insert) actions in one statement. The classic failure: if more than one source row matches one target row, engines either raise an error or apply a non-deterministic result. De-duplicate the source on the merge key first. Another trap is merging on a non-unique key and unintentionally updating many rows.
MERGE INTO dim_customer t
USING stg_customer s ON t.customer_id = s.customer_id
WHEN MATCHED AND t.row_hash <> s.row_hash THEN
UPDATE SET city = s.city, row_hash = s.row_hash
WHEN NOT MATCHED THEN
INSERT (customer_id, city, row_hash) VALUES (s.customer_id, s.city, s.row_hash);
This form works in Snowflake and Databricks SQL (Delta Lake). SQLite has no MERGE; it uses INSERT ... ON CONFLICT.
9. Why might WHERE YEAR(order_date) = 2025 be slow, and how do you fix it?
Level: Medium. What the interviewer is testing: sargability: a favourite practical question.
Wrapping a column in a function usually stops the engine from using an index, partition pruning or min/max file statistics, because it must compute the function for every row. Rewrite as a range on the raw column.
-- avoid
WHERE YEAR(order_date) = 2025
-- prefer
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
The same applies to implicit casts (comparing a string column to a number) and to LIKE '%text' with a leading wildcard.
10. Why can SUM go wrong after a join, and how do you prevent it?
Level: Medium. What the interviewer is testing: join fan-out is a classic silent bug.
If the join key is not unique on one side, each row on the other side is repeated, so a SUM over the repeated column inflates. Example: join orders to a table with several rows per order, then sum the order amount.
CREATE TABLE order_items (order_id INT, item TEXT);
INSERT INTO order_items VALUES (101,'a'),(101,'b'),(102,'c');
SELECT SUM(o.amount) AS inflated
FROM orders o JOIN order_items i ON i.order_id = o.order_id;
Output (run on the practice dataset in SQLite):
| inflated |
|---|
| 3200 |
Orders 101 and 102 add up to 2000 but the query returns 3200, because order 101 (1200) is counted twice. Aggregate each side to the target grain before joining, or compute the sum in a subquery that is unique per key.
The practice dataset
Every runnable query above uses these four tiny tables. Paste them into any SQLite shell (or a Snowflake / Databricks worksheet) and run along.
Show the practice dataset (SQL)
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT, signup_date TEXT);
INSERT INTO customers VALUES (1,'Asha','Bengaluru','2025-01-05'),(2,'Ravi','Mumbai','2025-02-11'),(3,'Meera','Bengaluru','2025-03-02'),(4,'Karan','Delhi','2025-03-20'),(5,'Divya','Chennai','2025-04-08');
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, amount REAL, status TEXT);
INSERT INTO orders VALUES (101,1,'2025-06-01',1200,'paid'),(102,1,'2025-06-15',800,'paid'),(103,2,'2025-06-03',450,'refunded'),(104,3,'2025-06-20',2300,'paid'),(105,3,'2025-07-02',150,'paid'),(106,1,'2025-07-09',600,'paid'),(107,2,'2025-07-11',900,'paid'),(108,4,'2025-07-15',NULL,'pending'),(109,3,'2025-07-30',700,'paid');
CREATE TABLE employees (emp_id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES (1,'Neha','Eng',3000,NULL),(2,'Omar','Eng',2500,1),(3,'Pia','Eng',2500,1),(4,'Quin','Eng',2000,2),(5,'Raj','Ops',1800,1),(6,'Sana','Ops',1800,5),(7,'Tej','Ops',1500,5);
CREATE TABLE logins (user_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES (1,'2025-08-01'),(1,'2025-08-02'),(1,'2025-08-03'),(1,'2025-08-05'),(2,'2025-08-01'),(2,'2025-08-03'),(2,'2025-08-04'),(2,'2025-08-05'),(2,'2025-08-06');
How to practise these
Run each query on the dataset above, then break it on purpose: add a duplicate row, a NULL key or a tie, and predict the new output before you run it again. Saying out loud why the result changed is exactly what interviewers listen for.
Written with AI assistance by ATS Data Labs. Every output above comes from running the query in SQLite. Key facts were checked against official documentation in November 2026; vendors change limits and names, so confirm details in the current docs before relying on them.
Top comments (0)