SQL Interview Questions for Data Analysts (With Answers)

Data analyst interviews almost always include a live SQL round. The questions are rarely exotic: joins, grouping, a window function or two, and one "top N per group" problem. What separates candidates is whether the query is correct on the first try, including the edge cases with NULL and ties, and whether they can explain why it works.

Below are the SQL interview questions that come up most for data analyst roles, each with a working query against one small schema. The SQL is standard and runs on PostgreSQL; where MySQL differs, it is noted.

The sample schema

customers (customer_id, name, email, city, signup_date)
orders    (order_id, customer_id, order_date, amount DECIMAL(10,2), status)
-- status is 'completed', 'cancelled' or 'refunded'

First, know the order SQL runs in

A query is written SELECT … FROM … WHERE …, but it is evaluated in a different order. Half of all "why doesn't this work?" questions are answered by it.

• FROM / JOIN – build the rows.
• WHERE – filter individual rows.
• GROUP BY – collapse rows into groups.
• HAVING – filter groups.
• SELECT – compute output columns (window functions run here).
• ORDER BY – sort.
• LIMIT – cut.

This is why you cannot use a SELECT alias in WHERE: the alias does not exist yet. (ORDER BY can use it, because it runs after SELECT.)

Joins

1. Total spend per customer, including customers who never ordered

SELECT c.customer_id,
       c.name,
       COALESCE(SUM(o.amount), 0) AS total_spend
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.status = 'completed'
GROUP BY c.customer_id, c.name
ORDER BY total_spend DESC;

Two details interviewers look for. First, COALESCE turns the NULL sum for customers with no orders into 0. Second, the status filter sits in the ON clause. Put it in WHERE instead and the rows for customers without orders (where o.status is NULL) are thrown away, which silently turns your LEFT JOIN into an inner join.

2. Customers who have never placed an order (anti-join)

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

The LEFT JOIN … WHERE o.order_id IS NULL form gives the same result. Avoid NOT IN (SELECT customer_id FROM orders): if that subquery returns even one NULL, NOT IN returns no rows at all.

Grouping and filtering

3. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after grouping and can. Customers with more than three completed orders:

SELECT customer_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 3;

4. COUNT(*), COUNT(column) and COUNT(DISTINCT column)

• COUNT(*) counts rows.
• COUNT(email) counts rows where email is not NULL.
• COUNT(DISTINCT customer_id) counts unique non-null values: on the orders table, the number of customers who ordered.

5. Conditional aggregation with CASE

One pass over the table, several metrics at once:

SELECT customer_id,
       COUNT(*) AS all_orders,
       SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS revenue,
       SUM(CASE WHEN status = 'refunded'  THEN 1      ELSE 0 END) AS refunds
FROM orders
GROUP BY customer_id;

CASE also buckets values: CASE WHEN amount >= 5000 THEN 'large' WHEN amount >= 1000 THEN 'medium' ELSE 'small' END. The first matching branch wins, so order the conditions from most to least specific.

NULL handling

6. Why does WHERE email = NULL return nothing?

Any comparison with NULL gives "unknown", which WHERE treats as false. Use IS NULL and IS NOT NULL. Aggregates such as SUM and AVG skip NULLs, so AVG(amount) averages only the known values; use COALESCE(amount, 0) if missing should count as zero.

Window functions

Window functions compute across related rows without collapsing them, which is why they appear in nearly every analyst interview.

7. ROW_NUMBER, RANK and DENSE_RANK

For order amounts 500, 500 and 300, sorted descending:

• ROW_NUMBER() gives 1, 2, 3 – always unique, ties broken arbitrarily.
• RANK() gives 1, 1, 3 – ties share a rank, then a gap.
• DENSE_RANK() gives 1, 1, 2 – ties share a rank, no gap.

8. The second-highest order amount

SELECT MAX(amount) AS second_highest
FROM orders
WHERE amount < (SELECT MAX(amount) FROM orders);

For the Nth highest, use DENSE_RANK so ties do not skip a value:

SELECT DISTINCT amount
FROM (
  SELECT amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS rnk
  FROM orders
) ranked
WHERE rnk = 3;

9. Top two customers by spend in each city (top N per group)

WITH spend AS (
  SELECT c.city, c.customer_id, c.name, SUM(o.amount) AS total
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  WHERE o.status = 'completed'
  GROUP BY c.city, c.customer_id, c.name
),
ranked AS (
  SELECT city, name, total,
         ROW_NUMBER() OVER (PARTITION BY city ORDER BY total DESC) AS rn
  FROM spend
)
SELECT city, name, total
FROM ranked
WHERE rn <= 2
ORDER BY city, total DESC;

You cannot filter on a window function in the same query's WHERE (it has not been computed yet), hence the CTE. Say which function you chose and why: ROW_NUMBER returns exactly two per city; DENSE_RANK would include everyone tied for second place.

10. Running total of revenue

SELECT order_date, order_id, amount,
       SUM(amount) OVER (
         ORDER BY order_date, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
WHERE status = 'completed';

The explicit ROWS frame and the order_id tie-breaker matter. With an ORDER BY, the default frame is RANGE, which treats rows with the same sort value as one: sort by order_date alone and every order on the same date shows the same total.

11. Month-over-month revenue growth with LAG

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS previous,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;

NULLIF(…, 0) prevents a division-by-zero error; the first month has no previous value, so its growth is NULL. In MySQL, replace DATE_TRUNC('month', order_date) with DATE_FORMAT(order_date, '%Y-%m-01').

12. Each city's share of total revenue

SELECT c.city,
       SUM(o.amount) AS revenue,
       ROUND(100.0 * SUM(o.amount) / SUM(SUM(o.amount)) OVER (), 1) AS pct_of_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.city
ORDER BY revenue DESC;

SUM(SUM(o.amount)) OVER () looks odd but is correct: the inner SUM is the per-city aggregate, and the window SUM adds those up across all groups.

Data quality

13. Find duplicate customers by email

SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

To see which rows to keep, number each copy and keep the first:

SELECT customer_id, email,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS copy_no
FROM customers;
-- rows with copy_no > 1 are the duplicates

Mention that emails often differ only by case or spaces; LOWER(TRIM(email)) catches those.

Cohorts and retention

14. Of the customers who first ordered in a month, how many ordered again the next month?

WITH first_month AS (
  SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort
  FROM orders
  GROUP BY customer_id
),
active AS (
  SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS month
  FROM orders
)
SELECT f.cohort,
       COUNT(*) AS new_customers,
       COUNT(a.customer_id) AS returned_next_month
FROM first_month f
LEFT JOIN active a
       ON a.customer_id = f.customer_id
      AND a.month = f.cohort + INTERVAL '1 month'
GROUP BY f.cohort
ORDER BY f.cohort;

Each customer appears once in first_month and at most once per month in active, so the counts cannot double. This is PostgreSQL syntax; in MySQL use DATE_FORMAT for the month and DATE_ADD(cohort, INTERVAL 1 MONTH). Interviewers mainly want the shape: find each customer's first period, then join activity offset by one period.

Quick-fire questions

• UNION versus UNION ALL? UNION removes duplicates (and pays for a sort or hash to do it); UNION ALL keeps everything and is faster.
• What is a CTE for? Naming an intermediate result so a long query reads top to bottom. It also lets you filter on window-function results.
• DELETE, TRUNCATE or DROP? DELETE removes chosen rows; TRUNCATE empties the table quickly; DROP removes the table itself.
• What does an index do? It lets the database find rows without scanning the whole table, at the cost of slower writes and extra storage.
• Why is a function on an indexed column slow? WHERE YEAR(order_date) = 2026 usually cannot use an index on order_date. Write a range instead: order_date >= '2026-01-01' AND order_date < '2027-01-01'.

Common mistakes in SQL interviews

• Filtering the right-hand table of a LEFT JOIN in WHERE.
• Selecting a column that is neither grouped nor aggregated (PostgreSQL rejects it; older MySQL returned an arbitrary value).
• Using NOT IN with a subquery that can return NULL.
• Integer division: in PostgreSQL 5 / 2 is 2. Multiply by 100.0 first.
• Ignoring ties when asked for "the top" anything. Ask the interviewer how ties should be handled; it shows you thought about it.
• Joining two one-to-many tables and then summing, which multiplies rows and inflates totals. Aggregate each side first.

How to practise

Create these two tables in a free PostgreSQL or MySQL install, insert 20 rows including some NULLs, ties and customers without orders, and write every query above without looking. Then change the question slightly ("top three by order count instead of spend") and write it again. Explain each query out loud as you would in the interview.

For a structured set of notes and practice, Master SQL From Scratch has handwritten SQL and DBMS notes, cheat sheets and 200+ real interview questions. If you are heading towards data engineering, continue with Python for Data Engineers, the Data Engineering Starter Pack, and our walkthrough of building a data pipeline in Python.