SQL Interview Questions: Top 50 with Answers

These fifty questions cover SQL topics that data analyst, data scientist and data engineer interviews commonly test: how queries are evaluated, NULL logic, joins, aggregation, window functions, dates, and performance. Every answer that involves a query was run against the small schema below on three engines, PostgreSQL 18, DuckDB 1.5 and SQLite 3.45, and the results shown are the real output. Where the engines disagree, the answer says so, because "it depends on the database" is often the most accurate thing you can say in an interview, as long as you can say how it depends.

Don't memorise the answers. Run the queries, change them, and predict the output before you press Enter. For deeper practice on specific areas, we have separate articles on SQL joins, window functions and scenario-based questions.

The practice schema

Six small tables: departments, employees (with a manager hierarchy), customers, orders, payments and daily logins. A few rows exist specifically to trigger common mistakes: an employee with no department, a customer with no city, a customer with no orders, a department with no employees, and two employees with the same salary.

CREATE TABLE departments (dept_id INTEGER PRIMARY KEY, dept_name VARCHAR(50) NOT NULL);
CREATE TABLE employees (
  emp_id INTEGER PRIMARY KEY, name VARCHAR(50) NOT NULL,
  dept_id INTEGER REFERENCES departments (dept_id),
  manager_id INTEGER REFERENCES employees (emp_id),
  salary INTEGER NOT NULL, hire_date DATE NOT NULL);
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name VARCHAR(50) NOT NULL, city VARCHAR(50));
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  ordered_at TIMESTAMP NOT NULL, amount DECIMAL(10, 2) NOT NULL, status VARCHAR(20) NOT NULL);
CREATE TABLE payments (payment_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders (order_id), amount DECIMAL(10, 2) NOT NULL);
CREATE TABLE logins (user_id INTEGER NOT NULL, login_date DATE NOT NULL);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Finance'), (4, 'Legal');
INSERT INTO employees VALUES (1, 'Asha', 1, NULL, 150000, '2019-04-01');
INSERT INTO employees VALUES
  (2, 'Ravi', 1, 1, 95000, '2021-06-15'), (3, 'Meena', 1, 1, 95000, '2022-01-10'),
  (5, 'Sara', 2, 1, 120000, '2020-02-01'), (8, 'Priya', 3, 1, 90000, '2021-11-11'),
  (9, 'Tom', NULL, 1, 60000, '2025-05-05');
INSERT INTO employees VALUES
  (4, 'John', 1, 2, 70000, '2023-03-01'), (6, 'Imran', 2, 5, 80000, '2022-08-20'),
  (7, 'Li', 2, 5, 130000, '2024-01-05');
INSERT INTO customers VALUES (1, 'Arjun', 'Hyderabad'), (2, 'Bina', 'Pune'), (3, 'Chen', NULL),
  (4, 'Divya', 'Hyderabad'), (5, 'Emil', 'Chennai');
INSERT INTO orders VALUES
  (101, 1, '2026-01-05 10:00:00', 250.00, 'delivered'), (102, 1, '2026-01-20 09:30:00', 100.00, 'delivered'),
  (103, 2, '2026-01-31 18:45:00', 400.00, 'cancelled'), (104, 3, '2026-02-02 12:00:00', 80.00, 'delivered'),
  (105, 1, '2026-02-14 16:20:00', 300.00, 'delivered'), (106, 4, '2026-02-28 11:10:00', 150.00, 'delivered'),
  (107, 2, '2026-03-03 08:05:00', 400.00, 'delivered'), (108, 4, '2026-03-15 19:40:00', 150.00, 'returned'),
  (109, 1, '2026-03-30 13:00:00', 50.00, 'delivered');
INSERT INTO payments VALUES (1, 101, 150.00), (2, 101, 100.00), (3, 102, 100.00), (4, 105, 300.00);
INSERT INTO logins VALUES (1, '2026-03-01'), (1, '2026-03-02'), (1, '2026-03-03'), (1, '2026-03-05'),
  (1, '2026-03-06'), (2, '2026-03-01'), (2, '2026-03-03');

The employee rows are inserted in three statements so that every manager exists before the rows that reference them. DuckDB checks the self-referencing foreign key against rows already in the table, so a single multi-row insert fails there.

Fundamentals

1. In what order does a database evaluate a SELECT query?

Logically, not in the order you write it: FROM and JOINs first, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT/OFFSET. The optimiser may execute things differently, but results must match this order. That explains a classic error. A column alias created in SELECT does not exist yet when WHERE runs:

SELECT name, salary * 12 AS annual FROM employees WHERE annual > 1500000;

PostgreSQL rejects it with column "annual" does not exist. SQLite and DuckDB accept it as an extension and return Asha and Li. Don't rely on that in an interview: repeat the expression or wrap the query in a subquery. ORDER BY runs after SELECT, so aliases work there everywhere.

2. What is the difference between WHERE and HAVING?

WHERE filters rows before they are grouped; HAVING filters groups after aggregation, so it can use aggregates:

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2021-01-01'
GROUP BY dept_id
HAVING AVG(salary) > 85000;

This returns departments 1, 2 and 3, with averages of about 86,667, 105,000 and 90,000. Tom, hired in 2025, passes the WHERE filter, but his department is NULL and his group's average of 60,000 fails HAVING. Filter in WHERE whenever you can; it shrinks the data before the expensive grouping step.

3. What do COUNT(*), COUNT(column) and COUNT(DISTINCT column) each count?

COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts distinct non-NULL values. On the employees table they return 9, 8 and 3: Tom's NULL department is skipped by the second and third.

4. Why does WHERE city = NULL return nothing?

Because NULL means "unknown", and comparing anything to unknown gives unknown, not true. WHERE keeps only rows where the condition is true, so city = NULL matches zero rows; use city IS NULL instead. The same logic catches people with <>: WHERE city <> 'Hyderabad' returns Bina and Emil but not Chen, whose city is NULL. To include unknowns you must say so: city <> 'Hyderabad' OR city IS NULL.

5. Why can NOT IN return no rows at all?

Find departments with no employees:

SELECT dept_name FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);

All three engines return nothing, although Legal has no employees. The subquery's results include Tom's NULL department. For Legal, 4 NOT IN (1, 2, 3, NULL) means 4 <> 1 AND 4 <> 2 AND 4 <> 3 AND 4 <> NULL, and the last comparison is unknown, so the whole condition is never true. NOT EXISTS doesn't have this problem and correctly returns Legal:

SELECT d.dept_name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

Prefer NOT EXISTS for anti-joins, or add WHERE dept_id IS NOT NULL inside the subquery.

6. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the combined result; UNION ALL keeps every row. Stacking the customers' cities twice gives 10 rows with UNION ALL and 4 with UNION (Hyderabad, Pune, Chennai and one NULL; for duplicate removal, NULLs count as equal). UNION has to sort or hash to find duplicates, so use UNION ALL unless you actually need de-duplication.

7. How do DELETE, TRUNCATE and DROP differ?

DELETE removes rows, optionally filtered by WHERE, and fires row-level delete triggers. TRUNCATE removes all rows from a table at once, much faster for large tables. DROP removes the table itself, structure included. Details vary by database. In PostgreSQL, TRUNCATE is transactional and can be rolled back, and it does not fire ON DELETE triggers. Some other databases treat TRUNCATE as a schema change that commits immediately, so check yours before relying on a rollback.

8. How does a PRIMARY KEY differ from a UNIQUE constraint?

A primary key is unique and never NULL, and a table has at most one. A UNIQUE constraint also prevents duplicates but allows NULLs, and here databases differ. PostgreSQL treats NULLs as distinct by default, so many rows may hold NULL in a unique column; the NULLS NOT DISTINCT option changes that (PostgreSQL constraints). SQL Server allows only one NULL per column in a unique constraint (SQL Server documentation).

9. What does a foreign key with ON DELETE CASCADE do?

A foreign key guarantees that a referenced row exists: you cannot insert an order for a customer who isn't in the customers table. With ON DELETE CASCADE, deleting the parent row automatically deletes its child rows. That suits rows that cannot exist on their own, such as the lines of an order. It is dangerous for independent entities: cascading from customers to orders would silently erase sales history.

10. What are the first three normal forms?

First normal form: each column holds one value, with no lists or repeating groups. Second: every non-key column depends on the whole key, not just part of a composite key. Third: non-key columns depend only on the key, not on other non-key columns; storing both dept_id and dept_name in employees would break it. Normalisation prevents update anomalies in transactional systems. Analytical models often denormalise on purpose into star schemas, trading storage for simpler, faster queries.

11. What is an index, and what does it cost?

An index is a separate, sorted structure (usually a B-tree) that lets the database find rows by a column's value without scanning the whole table. It speeds up selective filters, joins and sorts on that column. It costs storage, and it slows every INSERT, UPDATE and DELETE, because each index must be updated too. An index may go unused if the query wraps the column in a function, uses a leading wildcard such as LIKE '%bad', or matches so many rows that scanning the table is cheaper.

12. What is the difference between a clustered and a non-clustered index?

In SQL Server, a clustered index determines the order in which the table's rows are physically stored, so there can be only one; non-clustered indexes are separate structures that point back to the rows. PostgreSQL stores tables as unordered heaps and has no automatically maintained clustered index. Its CLUSTER command reorders a table once, and later changes are not kept in that order (PostgreSQL CLUSTER). Saying this distinction out loud shows you know the concept is engine-specific.

13. What is a transaction, and what does ACID mean?

A transaction groups statements so they succeed or fail as one unit, like debiting one account and crediting another. Atomicity: all or nothing. Consistency: constraints hold before and after. Isolation: concurrent transactions don't see each other's partial work, to a degree set by the isolation level. Durability: once committed, changes survive a crash.

14. What are isolation levels, and which anomalies does each prevent?

The standard levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, each ruling out more anomalies: dirty reads (seeing uncommitted data), non-repeatable reads (a row changes between two reads), phantom reads (new rows appear in a repeated query) and serialisation anomalies. Defaults differ. PostgreSQL uses READ COMMITTED, and its READ UNCOMMITTED behaves like READ COMMITTED (PostgreSQL isolation). MySQL's InnoDB engine defaults to REPEATABLE READ (MySQL documentation).

15. What is the difference between a view and a materialized view?

A view is a saved query: every time you select from it, the query runs against current data. A materialized view stores the query's result, so reads are fast but the data is only as fresh as the last refresh (REFRESH MATERIALIZED VIEW in PostgreSQL). SQL Server's closest equivalent is an indexed view. Use materialized results for expensive aggregations that can tolerate some staleness.

Joins and subqueries

16. What join types are there?

INNER JOIN keeps matching pairs only. LEFT JOIN keeps every row from the left table, with NULLs where the right side has no match; RIGHT JOIN is the mirror image. FULL OUTER JOIN keeps unmatched rows from both sides. CROSS JOIN pairs every row with every row. Our joins interview article works through each with diagrams.

17. Why did my LEFT JOIN turn into an inner join?

Because a condition on the right-hand table was placed in WHERE:

SELECT c.name, o.order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered';

Emil has no orders, so his row carries o.status = NULL, and the WHERE clause removes him. The LEFT JOIN is now effectively an INNER JOIN. Move the condition into the ON clause to filter which orders join while keeping every customer:

SELECT c.name, COUNT(o.order_id) AS delivered
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'
GROUP BY c.name;

Now Emil appears with 0 delivered orders, next to Arjun's 4 and one each for Bina, Chen and Divya.

18. How do you find customers who have never placed an order?

This is an anti-join. Either LEFT JOIN and keep the unmatched rows:

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

or use NOT EXISTS. Both return Emil. Test IS NULL on a column that can't be NULL in a real match, such as the primary key.

19. Which employees earn more than their manager?

Join the table to itself, once as the employee and once as the manager:

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

The only result is Li (130,000), who reports to Sara (120,000). Asha has no manager, so the inner join drops her, which is correct for this question.

20. When would you actually use a CROSS JOIN?

To generate every combination on purpose. The typical case is a reporting scaffold: every customer against every month, so that months with no sales show 0 instead of disappearing. Then LEFT JOIN the real data onto the scaffold. With 5 customers and 4 departments, customers CROSS JOIN departments returns 20 rows. On large tables an accidental cross join is a common cause of runaway queries.

21. IN or EXISTS: which should you use?

For a positive test they return the same rows. WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'returned') and its EXISTS equivalent both return Divya, and modern optimisers often produce the same plan for both. The difference that matters is the negative form: NOT IN breaks when the subquery can return NULL (question 5), and NOT EXISTS doesn't.

22. What is a correlated subquery?

A subquery that refers to the outer query's current row, so it is logically evaluated once per row. Employees paid above their own department's average:

SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id);

This returns Asha, Sara and Li. Tom never appears: with a NULL department, e2.dept_id = e.dept_id is never true, so his average is NULL and the comparison fails. A window function (AVG(salary) OVER (PARTITION BY dept_id)) gives the same answer and can be faster on large tables.

23. When would you use a CTE instead of a subquery or a temporary table?

A common table expression (WITH ... AS) names an intermediate result for one statement, which makes multi-step logic readable and lets you reference the same step twice. A subquery does the same job inline. A temporary table persists for the session and can be indexed, which is worth it when several statements reuse a large intermediate result. In PostgreSQL 12 and later, a non-recursive CTE referenced once is normally folded into the main query, and you can control this with MATERIALIZED or NOT MATERIALIZED (WITH queries).

24. How do you query a hierarchy such as an org chart?

With a recursive CTE: an anchor query for the top of the tree, then a recursive part that joins the next level to the rows found so far:

WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, c.depth + 1
  FROM employees e JOIN chain c ON e.manager_id = c.emp_id
)
SELECT name, depth FROM chain ORDER BY depth, name;

Asha is at depth 0. Meena, Priya, Ravi, Sara and Tom are at depth 1, and Imran, John and Li at depth 2. SQL Server uses the same pattern but without the RECURSIVE keyword.

25. Why did my total double after adding a join?

Because the join multiplied rows. Order 101 was paid in two instalments, so joining orders to payments produces two rows for it, and summing the order amount counts it twice:

SELECT SUM(o.amount) AS order_total, SUM(p.amount) AS paid
FROM orders o JOIN payments p ON p.order_id = o.order_id;
-- order_total 900.00, paid 650.00

The true total of the three paid orders is 650. Aggregate the many-side before joining:

SELECT SUM(o.amount) AS order_total, SUM(p.paid) AS paid
FROM orders o
JOIN (SELECT order_id, SUM(amount) AS paid FROM payments GROUP BY order_id) p
  ON p.order_id = o.order_id;
-- order_total 650.00, paid 650.00

This one is worth knowing well, because the same mistake produces wrong numbers in real dashboards. Always ask "what is the grain of each table?" before joining.

26. What do EXCEPT and INTERSECT do?

They are set operations on whole rows. A EXCEPT B returns distinct rows in A that are not in B, and A INTERSECT B returns distinct rows in both. SELECT customer_id FROM customers EXCEPT SELECT customer_id FROM orders returns customer 5, Emil, which is another way to write question 18.

27. Why do I get "must appear in the GROUP BY clause"?

Because you selected a column that is neither grouped nor aggregated:

SELECT dept_id, name, MAX(salary) FROM employees GROUP BY dept_id;

Each department has several names, so the database can't know which one you want. PostgreSQL and DuckDB reject the query. SQLite accepts it and, with MAX, returns the name from the row holding the maximum, a documented SQLite quirk that doesn't carry over to other engines. MySQL also rejects it under its default ONLY_FULL_GROUP_BY mode. To get the name of each department's top earner, use a window function (question 29).

Aggregation and window functions

28. How do you find the second-highest salary?

Two standard answers:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t
WHERE rnk = 2;

Both return 130,000. Mention the edge cases: if everyone earns the same, the first query returns NULL and the second returns no rows. DENSE_RANK also generalises to the Nth highest.

29. How do you get the top two salaries in each department?

Rank within each department and filter:

SELECT dept_id, name, salary
FROM (SELECT dept_id, name, salary,
             DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
      FROM employees WHERE dept_id IS NOT NULL) t
WHERE rnk <= 2;

Engineering returns three people: Asha, and Meena and Ravi tied at 95,000. Sales returns Li and Sara; Finance returns Priya. Whether ties should all be included is a question to ask the interviewer. If "top two" means exactly two rows, use ROW_NUMBER with a tie-breaker.

30. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

They differ only in how they treat ties. For Engineering, ordered by salary descending:

ROW_NUMBER always gives unique numbers; which tied row gets 2 is arbitrary unless the ORDER BY breaks the tie. RANK gives ties the same number and then skips (no 3). DENSE_RANK gives ties the same number without gaps.

31. How do you return the top N rows per group?

Number rows within each group, then filter. The two largest orders per customer:

SELECT customer_id, order_id, amount
FROM (SELECT customer_id, order_id, amount,
             ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id) AS rn
      FROM orders) t
WHERE rn <= 2;

The order_id tie-breaker matters. Customer 2's two orders are both 400.00, and without it the numbering between them is arbitrary.

32. Why does my running total repeat the same value?

Because of the default window frame. When a window has ORDER BY but no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all of the current row's peers, meaning rows with the same ORDER BY value (PostgreSQL documentation). For Engineering:

SUM(salary) OVER (ORDER BY salary)
-- John 70000, Meena 260000, Ravi 260000, Asha 410000

Meena and Ravi tie at 95,000, so both see the total including each other. For a row-by-row running total, use ROWS and a unique order:

SUM(salary) OVER (ORDER BY salary, emp_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- John 70000, Ravi 165000, Meena 260000, Asha 410000

All three engines behave the same way here.

33. How do you calculate a moving average?

With a ROWS frame of a fixed size. The average of each order and the two before it:

SELECT order_id, amount,
       ROUND(AVG(amount) OVER (ORDER BY ordered_at
                               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM orders ORDER BY ordered_at;

The first four rows give 250.00, 175.00, 250.00 and 193.33. The first two rows average fewer than three values. Say whether that is acceptable or whether those rows should be NULL. For a time-based window, such as the last seven days rather than the last three rows, you need a RANGE frame with an interval, which PostgreSQL and DuckDB support.

34. How do you compute month-over-month change?

Aggregate to months, then compare each month with the previous one using LAG:

SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM (SELECT CAST(DATE_TRUNC('month', ordered_at) AS DATE) AS month, SUM(amount) AS revenue
      FROM orders WHERE status = 'delivered' GROUP BY 1) m
ORDER BY month;

Delivered revenue is 350 in January, 530 in February (+180) and 450 in March (−80); January's change is NULL. Truncating dates is dialect-specific: DATE_TRUNC works in PostgreSQL and DuckDB. SQLite has no DATE_TRUNC, so use strftime('%Y-%m', ordered_at). SQL Server 2022 added DATETRUNC.

35. How do you keep only the latest row per key?

Number the rows per key, newest first, and keep number 1:

SELECT customer_id, order_id, ordered_at
FROM (SELECT customer_id, order_id, ordered_at,
             ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS rn
      FROM orders) t
WHERE rn = 1;

This returns orders 109, 107, 104 and 108, the latest for customers 1 to 4. To delete duplicates rather than select around them, use the same numbering to identify the primary keys of the rows with rn > 1, and delete those.

36. How do you turn rows into columns (pivot)?

With conditional aggregation, which works in every major database:

SELECT customer_id,
       SUM(CASE WHEN status = 'delivered' THEN amount ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status = 'returned'  THEN amount ELSE 0 END) AS returned
FROM orders GROUP BY customer_id;

Customer 2 shows 400 delivered and 400 cancelled; customer 4 shows 150 delivered and 150 returned. Some databases have a PIVOT keyword, but CASE works everywhere and handles conditions PIVOT can't.

37. How do you show each group's share of the total?

Nest an aggregate inside a window function that spans the whole result:

SELECT dept_id, SUM(salary) AS payroll,
       ROUND(100.0 * SUM(salary) / SUM(SUM(salary)) OVER (), 1) AS pct_of_total
FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id;

Engineering is 49.4%, Sales 39.8% and Finance 10.8%. The 100.0 matters: with integers alone, some databases would do integer division (question 43).

38. How do you find streaks of consecutive days (gaps and islands)?

Subtract a row number from each date. Consecutive dates then share the same result, which becomes the group key:

SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM (SELECT user_id, login_date,
             login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS INTEGER) AS grp
      FROM logins) t
GROUP BY user_id, grp ORDER BY user_id, streak_start;

User 1 has a 3-day streak from 1 to 3 March and a 2-day streak on 5–6 March; user 2 has two single days. This runs as written in PostgreSQL and DuckDB. SQLite stores dates as text, so subtracting from the date string silently produces nonsense. Use julianday(login_date) there instead.

39. How do you calculate a median?

In PostgreSQL and DuckDB, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) returns 95,000 for the employees table. SQL Server also has PERCENTILE_CONT, but as a window function with an OVER clause. SQLite has no built-in median, so you compute it with window functions or outside the database. The interview point is that AVG is not the median, and on skewed data such as salaries the two can differ a lot.

40. How do you find each customer's first and last order?

For the dates alone, MIN(ordered_at) and MAX(ordered_at) grouped by customer are enough. Customer 1 ordered first on 5 January and last on 30 March, four orders in total. If you need other columns from the first order, such as its amount, MIN won't give them. Use ROW_NUMBER as in question 35, or FIRST_VALUE.

41. How do you count distinct customers cumulatively over time?

The obvious query, COUNT(DISTINCT customer_id) OVER (ORDER BY ordered_at), works in DuckDB but fails in PostgreSQL ("DISTINCT is not implemented for window functions") and in SQLite. The portable approach finds each customer's first order, then runs a cumulative sum of new customers:

SELECT month, new_customers, SUM(new_customers) OVER (ORDER BY month) AS cumulative_customers
FROM (SELECT CAST(DATE_TRUNC('month', first_order) AS DATE) AS month, COUNT(*) AS new_customers
      FROM (SELECT customer_id, MIN(ordered_at) AS first_order FROM orders GROUP BY customer_id) f
      GROUP BY 1) m
ORDER BY month;

January brings 2 new customers and February 2 more, for 4 cumulative. Replace DATE_TRUNC with strftime in SQLite.

NULLs, dates and data types

42. What do COALESCE and NULLIF do?

COALESCE(a, b, ...) returns the first non-NULL argument: COALESCE(city, 'Unknown') shows Chen's city as Unknown. NULLIF(a, b) returns NULL when a equals b, and its classic use is avoiding division by zero:

SELECT d.dept_name, COUNT(e.emp_id) AS staff,
       SUM(e.salary) / NULLIF(COUNT(e.emp_id), 0) AS avg_salary
FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

Legal, with 0 staff, gets a NULL average instead of an error.

43. What does 5 / 2 return?

It depends on the engine. PostgreSQL and SQLite return 2, because dividing two integers performs integer division, and SQL Server behaves the same way. DuckDB returns 2.5, because its / operator always produces a decimal result. Make the intent explicit with 5 / 2.0 or a CAST, which returns 2.5 everywhere. This is a common source of percentages that come out as 0.

44. What is wrong with BETWEEN '2026-01-01' AND '2026-01-31' on a timestamp?

It stops at midnight at the start of 31 January. Order 103 was placed at 18:45 that day, so the BETWEEN version counts 2 January orders while there are 3. Use a half-open range instead:

WHERE ordered_at >= '2026-01-01' AND ordered_at < '2026-02-01'

It is correct for any time precision, and it doesn't need you to know how many days a month has.

45. Where do NULLs appear when you sort?

That depends on the database too. PostgreSQL and DuckDB put NULLs last in ascending order, so Chen appears last when customers are sorted by city. SQLite puts them first, and SQL Server treats NULLs as the lowest values, so also first (SQL Server ORDER BY). PostgreSQL, DuckDB and recent SQLite versions accept NULLS FIRST or NULLS LAST to make the order explicit. In SQL Server, sort on a CASE expression first.

46. Why can the same LIMIT query return different rows?

Because ORDER BY on a non-unique column leaves ties in an arbitrary order. Sorting employees by salary descending with LIMIT 4 returns Asha, Li, Sara and then one of Meena or Ravi, who tie at 95,000. When this query was run for this article, PostgreSQL and DuckDB returned Meena and SQLite returned Ravi. Both are correct. When you paginate or pick "the top N", add a unique tie-breaker, such as ORDER BY salary DESC, emp_id.

Performance and design

47. How do you read a query plan?

Run EXPLAIN to see the plan the optimiser chose, or EXPLAIN ANALYZE to execute the query and see actual row counts and timings. Remember that EXPLAIN ANALYZE really runs the statement, including an UPDATE or DELETE, unless you wrap it in a transaction and roll back (Using EXPLAIN). Look for sequential scans on large tables where you expected an index, estimated row counts that are far from the actual counts (a sign of stale statistics), and the join method used for the largest joins.

48. What makes a condition "sargable"?

A sargable predicate is one the database can answer with an index seek on the column. WHERE ordered_at >= '2026-01-01' AND ordered_at < '2027-01-01' can use an index on ordered_at. WHERE EXTRACT(YEAR FROM ordered_at) = 2026 usually can't, because the function has to be computed for every row first. The same goes for LIKE '%smith' and for implicit type conversions on the column. Rewrite the condition as a range on the bare column, or, in PostgreSQL, create an index on the expression itself.

49. Why do analytics databases use star schemas instead of normalised tables?

Transactional (OLTP) systems are normalised so each fact is stored once and updates stay consistent. Analytical (OLAP) workloads mostly read, aggregate and filter large volumes. A star schema, with one fact table of events surrounded by denormalised dimension tables, keeps those queries to a few simple joins that are easy to write and fast to run. Our article on star schema, snowflake and data vault modelling compares the options.

50. An interviewer says "this query is slow". How do you respond?

Work through it out loud, in order:

1. Clarify what "slow" means: how slow, since when, and on what data volume.
2. Measure with the query plan (question 47) rather than guessing.
3. Check the usual causes: missing or unusable indexes (question 48), row explosion from joins (question 25), stale statistics, and functions on filtered columns.
4. Change one thing at a time and compare plans and timings.
5. Confirm the results are unchanged, because a fast wrong query is worse than a slow right one.

The process matters more than naming a specific fix. Our SQL optimisation interview questions go deeper into each step.

How to practise with this list

Load the schema into any of the three engines. DuckDB and SQLite need no server, so they are the quickest start. Then, for each question, write your answer before reading ours, run both, and explain any difference. Practise saying the edge cases out loud, because they are what follow-up questions probe: NULLs, ties, duplicates from joins, and dialect differences. For more timed practice, see our guide to mock interviews. Window functions and CTEs, which run through many of the questions above, are part of the syllabus of the GloryTecks SQL Server course, taught in T-SQL.