Data analyst SQL rounds tend to test the same few skills under different stories: filtering and grouping, joins that keep or drop rows, ranking, running totals, period-over-period change and cleaning duplicates. The eight questions below cover those patterns on one small dataset. Every answer has been run on MySQL 8.0, with the exact output shown. They are practice questions written for this page, not a record of any company's actual interview.
The dataset
CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(15), signup_date DATE);
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE, amount DECIMAL(10,2));
INSERT INTO customers VALUES
(1,'Aarav','Chennai','2026-01-10'), (2,'Bhumi','Chennai','2026-02-03'),
(3,'Chetan','Kolkata','2026-02-20'), (4,'Diya','Kolkata','2026-03-15'),
(5,'Eshan','Chennai','2026-04-01');
INSERT INTO orders VALUES
(101,1,'2026-01-12',1200), (102,1,'2026-02-15',800), (103,2,'2026-02-05',2500),
(104,3,'2026-03-01',900), (105,1,'2026-03-20',1500), (106,3,'2026-03-25',900),
(107,4,'2026-04-02',3100), (108,2,'2026-04-10',700), (109,4,'2026-04-28',600),
(110,2,'2026-04-10',700);
Try each question yourself before reading the answer. Saying your approach out loud first ("I'll aggregate per customer, then rank within city") is a habit worth practising for the real round.
Q1. Find the second-highest order amount.
SELECT MAX(amount) AS second_highest
FROM orders
WHERE amount < (SELECT MAX(amount) FROM orders);
| second_highest |
|---|
| 2500.00 |
An alternative is SELECT DISTINCT amount FROM orders ORDER BY amount DESC LIMIT 1 OFFSET 1;, which also returns 2500.00. Mention DISTINCT: without it, a tie for the highest value would return the highest again. Expect the follow-up "what if there is no second value?" The MAX version returns NULL; the LIMIT version returns no row.
Q2. List customers who have never ordered.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
| name |
|---|
| Eshan |
This is the anti-join pattern. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) is equally good. Avoid NOT IN on a column that can contain NULL, because a single NULL makes it return nothing. See MySQL joins explained.
Q3. Who is the top-spending customer in each city?
SELECT city, name, spend
FROM (
SELECT c.city, c.name, SUM(o.amount) AS spend,
DENSE_RANK() OVER (PARTITION BY c.city ORDER BY SUM(o.amount) DESC) AS rnk
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.city, c.name
) t
WHERE rnk = 1
ORDER BY city;
| city | name | spend |
|---|---|---|
| Chennai | Bhumi | 3900.00 |
| Kolkata | Diya | 3700.00 |
Aggregate first, then rank within each city. DENSE_RANK keeps both customers if two tie for first; ROW_NUMBER would pick one arbitrarily. Being able to explain that choice matters as much as the query. See RANK vs DENSE_RANK vs ROW_NUMBER.
Q4. Show a running total of revenue for April.
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date, order_id) AS running_total
FROM orders
WHERE order_date >= '2026-04-01'
ORDER BY order_date, order_id;
| order_date | amount | running_total |
|---|---|---|
| 2026-04-02 | 3100.00 | 3100.00 |
| 2026-04-10 | 700.00 | 3800.00 |
| 2026-04-10 | 700.00 | 4500.00 |
| 2026-04-28 | 600.00 | 5100.00 |
The order_id tie-breaker matters. With ORDER BY order_date alone, the default window frame treats both 10 April rows as peers, and both would show 4500.00.
Q5. Find duplicate orders, then remove the extras.
SELECT customer_id, order_date, amount, COUNT(*) AS copies, GROUP_CONCAT(order_id) AS order_ids
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;
| customer_id | order_date | amount | copies | order_ids |
|---|---|---|---|---|
| 2 | 2026-04-10 | 700.00 | 2 | 108,110 |
To find the rows to delete while keeping the earliest copy:
SELECT order_id
FROM (
SELECT order_id,
ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, amount ORDER BY order_id) AS rn
FROM orders
) t
WHERE rn > 1;
| order_id |
|---|
| 110 |
Say out loud that you would check with the business whether two equal orders on one day are really duplicates. A customer can genuinely buy the same thing twice.
Q6. Calculate month-on-month revenue growth.
WITH m AS (
SELECT DATE_FORMAT(order_date, '%Y-%m') AS mon, SUM(amount) AS rev
FROM orders GROUP BY mon
)
SELECT mon, rev,
ROUND(100 * (rev - LAG(rev) OVER (ORDER BY mon)) / LAG(rev) OVER (ORDER BY mon), 1) AS mom_pct
FROM m
ORDER BY mon;
| mon | rev | mom_pct |
|---|---|---|
| 2026-01 | 1200.00 | NULL |
| 2026-02 | 3300.00 | 175.0 |
| 2026-03 | 3300.00 | 0.0 |
| 2026-04 | 5100.00 | 54.5 |
The first month has nothing to compare with, so it is NULL, not 0. A month with no orders would be missing entirely. In a real report, you would join to a calendar table so that gaps show as zero. More on LAG in the window functions tutorial.
Q7. Show each customer's first order.
SELECT c.name, o.order_date, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE (o.customer_id, o.order_date) IN (
SELECT customer_id, MIN(order_date) FROM orders GROUP BY customer_id
)
ORDER BY o.order_date;
| name | order_date | amount |
|---|---|---|
| Aarav | 2026-01-12 | 1200.00 |
| Bhumi | 2026-02-05 | 2500.00 |
| Chetan | 2026-03-01 | 900.00 |
| Diya | 2026-04-02 | 3100.00 |
If a customer had two orders on their first day, this returns both. ROW_NUMBER() ... WHERE rn = 1 returns exactly one. Know both, and say which behaviour the question needs.
Q8. What share of signed-up customers are repeat buyers?
SELECT COUNT(*) AS customers,
SUM(order_count >= 2) AS repeat_customers,
ROUND(100 * SUM(order_count >= 2) / COUNT(*), 1) AS repeat_pct
FROM (
SELECT c.customer_id, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
) t;
| customers | repeat_customers | repeat_pct |
|---|---|---|
| 5 | 4 | 80.0 |
The denominator is the trap. Starting from orders would leave out Eshan and report every customer as a repeat buyer. Use COUNT(o.order_id), not COUNT(*), so that a customer with no orders counts 0, not 1.
Before the interview
- Practise writing queries without autocomplete, and talk through edge cases: ties, NULLs, empty months, duplicates.
- Revise WHERE vs HAVING and the order in which clauses run. Both are common warm-up questions.
- For the skill list analyst roles usually ask for beyond queries, see SQL skills for data analyst jobs.