SQL interview questions: 20 problems with worked solutions

SQL rounds are deceptive: the syntax takes a week to learn, and people still fail them regularly. The reason is that the questions are not about syntax but about what a query does to the data.

Where it starts: execution order

You write a query starting from SELECT; it executes starting from FROM. Three consequences follow, and they are what gets tested:

• A SELECT alias cannot be used in WHERE — it does not exist yet. In ORDER BY it can, because that runs later.
• WHERE filters rows before grouping, HAVING filters groups after. An aggregate condition in WHERE will not work.
• WHERE is cheaper than HAVING: discard rows as early as possible.

Joins

Find users with no orders

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

The follow-up is about alternatives. NOT EXISTS usually reads better and often wins on large tables; NOT IN is a trap, because a single NULL in the subquery makes it return nothing at all.

SELECT id, email FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Why did the join produce more rows?

Because one row on the left matched several on the right. The classic case is joining orders to order items and then summing — the totals come out wrong.

The fix is to aggregate first and join second.

SELECT o.id, o.created_at, i.total
FROM orders o
JOIN (
    SELECT order_id, SUM(price * qty) AS total
    FROM order_items GROUP BY order_id
) i ON i.order_id = o.id;

Aggregates

COUNT(*) against COUNT(column)

COUNT(*) counts rows; COUNT(column) counts rows where the column is not NULL; COUNT(DISTINCT column) counts unique non-null values. A great deal of broken reporting starts here.

Top three products per category

SELECT category, product, revenue
FROM (
    SELECT category, product, SUM(price * qty) AS revenue,
           ROW_NUMBER() OVER (
               PARTITION BY category ORDER BY SUM(price * qty) DESC
           ) AS rn
    FROM sales
    GROUP BY category, product
) t
WHERE rn <= 3;

A window function cannot be filtered in the WHERE of the same level — it is computed afterwards. Hence the subquery or a CTE.

Window functions

ROW_NUMBER, RANK, DENSE_RANK

• ROW_NUMBER — a running count, ties broken arbitrarily: 1, 2, 3, 4.
• RANK — ties share a rank and the next one skips: 1, 2, 2, 4.
• DENSE_RANK — ties share a rank with no gap: 1, 2, 2, 3.

The follow-up: which one for "top three including ties"? DENSE_RANK — otherwise two second places push the third out.

Running total

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM daily;

State the frame explicitly: with an ORDER BY the default is RANGE, and on duplicate dates that is not the result people expect.

Month-over-month change

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS diff
FROM monthly;

Practical problems

Remove duplicates, keeping one row

DELETE FROM t
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (
            PARTITION BY email ORDER BY created_at
        ) AS rn FROM t
    ) x WHERE rn > 1
);

Find the second highest salary

The naive ORDER BY salary DESC LIMIT 1 OFFSET 1 breaks on ties — it returns the same maximum again.

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

Cohort retention

WITH first_seen AS (
    SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort
    FROM events GROUP BY user_id
)
SELECT f.cohort,
       DATE_TRUNC('month', e.event_at) AS period,
       COUNT(DISTINCT e.user_id) AS users
FROM events e
JOIN first_seen f ON f.user_id = e.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

What is assessed here is less the query than whether you understand what a cohort is and why anyone wants one.

Performance

Why is the index not being used?

• A function on the column: WHERE DATE(created_at) = '2026-01-01' cannot use it. Rewrite as a range.
• LIKE '%text' with a leading wildcard — a B-tree is no help.
• Low selectivity: if the condition returns half the table, a sequential scan is the better plan and the planner knows it.
• Column order in a composite index: an index on (a, b) serves a condition on a, not one on b alone.

In short

• Execution order explains half the other questions.
• NOT IN with nulls, and row multiplication after a join, are the two most common traps.
• Be able to tell ROW_NUMBER, RANK and DENSE_RANK apart out loud.
• Start performance answers with EXPLAIN, not with an index.
• Stuck? Build the query in steps with CTEs and narrate them.