SQL interview questions for data engineers: joins, NULLs, pivots and hierarchies

After window functions, the SQL questions that come up most in data engineering rounds are not exotic. They are joins, NULLs, grouping and a few shapes that repeat: customers with no orders, employees who earn more than their manager, rows turned into columns, a reporting chain. The queries are short. The marks are in the details, such as what a join does with a row that has no match, and what NULL does to a comparison.

This guide answers nine of those questions on small tables you can check by eye. The queries ran in DuckDB, and the output under each query is what it printed. The SQL is standard and works the same way in PostgreSQL, Snowflake and BigQuery unless a note says otherwise.

The tables used in every example

4 tables. Three details are on purpose, because interviewers plant them: one order has no customer (a guest checkout, so its customer_id is NULL), 2 customers have never ordered, and 2 employees earn more than their own manager.

CREATE TABLE customers (customer_id INTEGER, name VARCHAR);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meena'), (4, 'Kiran');

CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (101, 1, 1200), (102, 1, 300), (103, 2, 800), (104, NULL, 450);

CREATE TABLE employees (
  emp_id INTEGER, name VARCHAR, dept VARCHAR, salary INTEGER, manager_id INTEGER
);
INSERT INTO employees VALUES
  (1, 'Meera',  'Management',  150000, NULL),
  (2, 'Arjun',  'Engineering', 120000, 1),
  (3, 'Rohan',  'Sales',        90000, 1),
  (4, 'Kavya',  'Engineering', 125000, 2),
  (5, 'Imran',  'Engineering',  80000, 2),
  (6, 'Nikhil', 'Engineering',  72000, 4),
  (7, 'Sana',   'Sales',        60000, 3),
  (8, 'Dev',    'Sales',        95000, 3);

CREATE TABLE sales (sale_id INTEGER, city VARCHAR, sale_date DATE, amount INTEGER);
INSERT INTO sales VALUES
  (1, 'Pune',    '2026-01-10', 1200),
  (2, 'Pune',    '2026-02-05',  800),
  (3, 'Pune',    '2026-02-20',  450),
  (4, 'Chennai', '2026-01-15', 2500),
  (5, 'Chennai', '2026-03-02',  900),
  (6, 'Mumbai',  '2026-03-11',  700);

1What does each join return, and how many rows?

Interviewers often ask for the row count before the query, because it shows whether you understand what a join keeps. 4 customers and 4 orders. Work out the counts first: Asha has 2 orders, Ravi has one, Meena and Kiran have none, and order 104 has no customer.

SELECT 'INNER' AS join_type, COUNT(*) AS rows_returned
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
UNION ALL
SELECT 'LEFT', COUNT(*)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION ALL
SELECT 'RIGHT', COUNT(*)
FROM customers c RIGHT JOIN orders o ON o.customer_id = c.customer_id
UNION ALL
SELECT 'FULL', COUNT(*)
FROM customers c FULL JOIN orders o ON o.customer_id = c.customer_id;

join_type | rows_returned
----------+--------------
INNER     | 3
LEFT      | 5
RIGHT     | 4
FULL      | 6
(4 rows)

3 orders match a customer, so INNER gives 3. LEFT keeps every customer: the 3 matches plus Meena and Kiran with NULLs on the order side gives 5. RIGHT keeps every order: the 3 matches plus the guest order gives 4. FULL keeps both sides' leftovers: 3 + 2 + 1 = 6. Notice that Asha counts twice in the LEFT result because she has 2 orders. A join returns 1 row per matching pair, not 1 row per customer.

2Why does WHERE manager_id = NULL return nothing?

Meera is the top of the company and has no manager, so her manager_id is NULL. A first attempt at "employees with no manager" uses =:

SELECT name FROM employees WHERE manager_id = NULL;

name
----
(0 rows)

No rows. In SQL, NULL means "unknown", and a comparison with an unknown value is itself unknown, never true. The filter keeps only rows where the condition is true, so the row is dropped. Even NULL = NULL is unknown:

SELECT NULL = NULL AS equal, NULL IS NULL AS is_null;

equal | is_null
------+--------
NULL  | true
(1 row)

IS NULL is the only test that asks "is this value missing". Use it:

SELECT name FROM employees WHERE manager_id IS NULL;

name
-----
Meera
(1 row)

This three-valued logic (true, false, unknown) is the root of most NULL bugs, and question 4 shows the worst of them.

3What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

They count different things when a column has NULLs. Try them on orders.customer_id, which has one NULL:

SELECT COUNT(*)                    AS all_rows,
       COUNT(customer_id)          AS non_null_ids,
       COUNT(DISTINCT customer_id) AS distinct_ids
FROM orders;

all_rows | non_null_ids | distinct_ids
---------+--------------+-------------
4        | 3            | 2
(1 row)

COUNT(*) counts rows, whatever they contain: 4. COUNT(customer_id) skips NULLs: 3. COUNT(DISTINCT customer_id) skips NULLs and repeats: Asha appears twice, so 2. Every aggregate except COUNT(*) ignores NULLs, which is why AVG of a column with missing values divides by the number of non-NULL rows, not by all rows.

4How do you find customers with no orders?

This anti-join question is on nearly every list. There are three common answers, and one of them is a trap.

Method 1: LEFT JOIN, then keep the rows that found no match. After a LEFT JOIN, a customer with no orders has NULL in every order column, so test a column that can never be NULL for a real order, such as order_id.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.name;

name
-----
Kiran
Meena
(2 rows)

Method 2: NOT EXISTS. For each customer, ask whether a matching order exists, and keep the customers where none does.

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.name;

name
-----
Kiran
Meena
(2 rows)

Method 3: NOT IN. It reads the most naturally, which is why people write it. Predict the result before you run it, given that one order has a NULL customer_id:

SELECT c.name
FROM customers c
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders)
ORDER BY c.name;

name
----
(0 rows)

No rows, even though Meena and Kiran have no orders. NOT IN expands to "not equal to 1, and not equal to 2, and not equal to NULL". The last part is unknown, never true, so the whole condition can never be true for any customer. One NULL in the subquery empties the result, and no error tells you. If you must use NOT IN, remove the NULLs inside it:

SELECT c.name
FROM customers c
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL)
ORDER BY c.name;

name
-----
Kiran
Meena
(2 rows)

The safe habit is to prefer NOT EXISTS or the LEFT JOIN form, because neither is affected by NULLs in the other table. Practise it: Customers who never ordered (free) and Never-ordered customers via NOT EXISTS.

5How do you find employees who earn more than their manager?

The manager is another row of the same table, so join the table to itself. Give the 2 copies different aliases, and match each employee's manager_id to the manager's emp_id.

SELECT e.name AS employee, e.salary,
       m.name AS manager,  m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary
ORDER BY e.name;

employee | salary | manager | manager_salary
---------+--------+---------+---------------
Dev      | 95000  | Rohan   | 90000
Kavya    | 125000 | Arjun   | 120000
(2 rows)

Dev earns 95000 against Rohan's 90000, and Kavya earns 125000 against Arjun's 120000. Meera does not appear, and that is correct here: her manager_id is NULL, so the inner join finds no manager row for her. If the question were "list every employee with their manager's name, including the top person", you would switch to a LEFT JOIN and Meera would appear with a NULL manager. Practise it: Employees earning more than their manager (free) and Employee and manager names.

6When does the condition go in WHERE, and when in HAVING?

"Which departments pay more on average than the company as a whole?" You want to filter on an average, which only exists after grouping. WHERE runs before grouping, so it cannot see aggregates:

SELECT dept, AVG(salary) AS avg_salary
FROM employees
WHERE AVG(salary) > 99000
GROUP BY dept;

Binder Error: WHERE clause cannot contain aggregates!

WHERE filters rows before they are grouped. HAVING filters groups after they are formed, so it can use aggregates. A scalar subquery, which is a subquery that returns exactly one value, supplies the company average instead of a typed-in number:

SELECT dept, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
ORDER BY dept;

dept        | avg_salary
------------+-----------
Engineering | 99250.0
Management  | 150000.0
(2 rows)

The company average is 99000. Engineering averages 99250 and Management 150000, so both pass, and Sales at about 81667 does not. The rule of thumb: a condition on a single row's column goes in WHERE, because it also makes the grouping cheaper by dropping rows early, and a condition on an aggregate goes in HAVING.

7How do you turn rows into columns (a pivot)?

The business wants 1 row per city and 1 column per month. The portable way is conditional aggregation: sum an amount only when the row belongs to that month, and give the other rows zero.

SELECT city,
       SUM(CASE WHEN EXTRACT(month FROM sale_date) = 1 THEN amount ELSE 0 END) AS jan,
       SUM(CASE WHEN EXTRACT(month FROM sale_date) = 2 THEN amount ELSE 0 END) AS feb,
       SUM(CASE WHEN EXTRACT(month FROM sale_date) = 3 THEN amount ELSE 0 END) AS mar
FROM sales
GROUP BY city
ORDER BY city;

city    | jan  | feb  | mar
--------+------+------+----
Chennai | 2500 | 0    | 900
Mumbai  | 0    | 0    | 700
Pune    | 1200 | 1250 | 0
(3 rows)

Pune's two February sales were added into one cell, 1250, and a month with no sales shows 0 instead of NULL because of the ELSE 0. Snowflake, SQL Server and DuckDB also have a PIVOT keyword, and BigQuery has its own PIVOT operator, but the CASE version runs everywhere and is the one interviewers expect you to be able to write. Its weakness is that the month columns are fixed in the query, so a new month needs a new line. Practise it: Pivot monthly sales to columns and Unpivot wide sales to rows.

8How do you walk a hierarchy with a recursive CTE?

"Show everyone with their level in the company and the chain of managers above them." The depth of an organisation chart is not known in advance, so a fixed number of self joins cannot do it. A recursive CTE repeats a step until no new rows appear.

WITH RECURSIVE org AS (
  SELECT emp_id, name, 1 AS level, name AS chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.emp_id, e.name, o.level + 1, o.chain || ' > ' || e.name
  FROM employees e
  JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, level, chain
FROM org
ORDER BY level, emp_id;

emp_id | name   | level | chain
-------+--------+-------+-------------------------------
1      | Meera  | 1     | Meera
2      | Arjun  | 2     | Meera > Arjun
3      | Rohan  | 2     | Meera > Rohan
4      | Kavya  | 3     | Meera > Arjun > Kavya
5      | Imran  | 3     | Meera > Arjun > Imran
7      | Sana   | 3     | Meera > Rohan > Sana
8      | Dev    | 3     | Meera > Rohan > Dev
6      | Nikhil | 4     | Meera > Arjun > Kavya > Nikhil
(8 rows)

The first SELECT is the anchor: the 1 row with no manager, at level 1. The second SELECT is the recursive step: join the employees table to what has been found so far, so the direct reports of level 1 become level 2, their reports become level 3, and so on. It stops when a pass adds no rows. Nikhil reports to Kavya, who reports to Arjun, who reports to Meera, so he is at level 4.

9Which ids are missing from a sequence?

An invoice table has numbers 1, 2, 3, 5, 6 and 9. Audit wants the numbers that never appear. You cannot select rows that do not exist, so first generate every number that should exist, then keep the ones with no match. It is the same anti-join as question 4.

CREATE TABLE invoices (invoice_no INTEGER);
INSERT INTO invoices VALUES (1), (2), (3), (5), (6), (9);

SELECT g.n AS missing_invoice
FROM generate_series((SELECT MIN(invoice_no) FROM invoices),
                     (SELECT MAX(invoice_no) FROM invoices)) AS g(n)
LEFT JOIN invoices i ON i.invoice_no = g.n
WHERE i.invoice_no IS NULL
ORDER BY g.n;

missing_invoice
---------------
4
7
8
(3 rows)

Numbers 4, 7 and 8 are missing. generate_series is the PostgreSQL and DuckDB way to build a number list. Snowflake uses a GENERATOR table function with ROW_NUMBER, and BigQuery uses GENERATE_ARRAY with UNNEST. A date version of the same idea, a calendar that fills days with no sales, is in the window function guide. Practise it: Gaps in order_id sequence.

How to answer any SQL scenario question

The 9 questions share a method, and it works on ones you have not seen:

1. 1Repeat the question in terms of the tables: what is 1 row, and what identifies it?
2. 2Say what you expect, including the count, before you run anything.
3. 3Check where NULLs can appear, in both the data and the join keys.
4. 4Write the simplest query first, then add the edge cases an interviewer would plant: ties, missing rows, duplicates.
5. 5Run it, and compare the real result with your expectation. A difference is the interesting part to talk about.

The interview question bank covers the theory behind each of these, and the SQL problems let you run them against hidden tests in the browser.