SQL interview questions, with the queries written out.

SQL interviews usually start with a query you write on the spot, then move to a messier real-world task. Here’s one of each.

Free question 1 (query): Customers with three or more orders

You have two tables. customers (customer_id, name) and orders (order_id, customer_id, order_date, amount). Return each customer who placed at least 3 orders in 2025, with their number of orders and total spent, highest spender first.

Model answer

SELECT c.customer_id,
       c.name,
       COUNT(*)      AS order_count,
       SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-01-01'
  AND o.order_date <  '2026-01-01'
GROUP BY c.customer_id, c.name
HAVING COUNT(*) >= 3
ORDER BY total_spent DESC;

Walk through it out loud:

• JOIN connects each order to its customer. An inner join is right here, because a customer with no orders can’t have three.
• WHERE filters rows before grouping, so only 2025 orders are counted.
• The date range uses “on or after January 1, 2025” and “before January 1, 2026”. That catches every moment of December 31, even if order_date stores a time. Writing “<= ‘2025-12-31’” would miss orders placed later that day.
• GROUP BY makes one row per customer. Include name too, since it’s in the SELECT.
• HAVING filters after grouping. You can’t use COUNT in WHERE, because the count doesn’t exist until the rows are grouped.

Follow-ups to expect: “What if you also need customers with zero orders?” Use a LEFT JOIN, move the date condition into the ON clause so customers without 2025 orders aren’t dropped, and count o.order_id instead of every row. “How would you make this fast on millions of rows?” An index on orders (customer_id, order_date) helps.

What interviewers listen for: you explain WHERE versus HAVING, you handle the date range carefully, and you talk through edge cases before being asked.

Free question 2 (data cleanup): Find and remove duplicates

A users table (user_id, email, created_at) has duplicate accounts, some with the same email in different capitals, like Sam@Mail.com and sam@mail.com. Find the duplicates, then delete them, keeping the oldest account for each email.

Model answer

Step 1: find them. Compare emails in lower case so capitals don’t hide duplicates.

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

Step 2: rank each copy. ROW_NUMBER gives 1 to the oldest row for each email, 2 to the next, and so on. Adding user_id to the ORDER BY breaks ties if two rows have the same created_at.

WITH ranked AS (
  SELECT user_id,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(email)
           ORDER BY created_at, user_id
         ) AS rn
  FROM users
)
SELECT user_id FROM ranked WHERE rn > 1;

Step 3: delete, carefully. Run the SELECT above first and check the rows. Then swap the last line for a delete:

WITH ranked AS (
  SELECT user_id,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(email)
           ORDER BY created_at, user_id
         ) AS rn
  FROM users
)
DELETE FROM users
WHERE user_id IN (SELECT user_id FROM ranked WHERE rn > 1);

This works in PostgreSQL and SQL Server. MySQL may refuse to delete from a table it’s reading in the same statement, so there you’d first save the IDs in a temporary table, then delete using that.

Then say what a careful person adds:

• Run it inside a transaction, or back up the table first, so you can undo a mistake.
• Check whether other tables point to the duplicate user_ids, like orders. Those may need moving to the kept account first.
• Stop it happening again: add a unique index on the lower-case email.

What interviewers listen for: you check before deleting, you think about the tie-breaker and related tables, and you fix the cause, not just the symptom.