SQL practice › SQL interview questions › Data analyst SQL interview questions
SQL interview questions for data analysts, with answers
Eight SQL questions of the kind a data analyst gets in an interview, on one small set of orders: revenue per month, average order value, top customers, repeat customers, first orders, size buckets, month-over-month change, new versus returning. You answer by writing the query. It runs on a real database in this page and is checked on the spot, on the tables shown and again on changed data. The answer and the explanation to give the interviewer are under each question.
They are not taken from any company's question list. The page runs SQLite, where a month is cut out of a date with strftime; the PostgreSQL and MySQL spelling is in the questions at the end.
The tables
A small office-furniture shop: five customers, and eight orders from January to March 2026. Each order stores its customer's id in customer_id. Dates are text, year-month-day. Eve has never ordered.
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Revenue: totals and averages
An analyst's first questions are sums over time. The SQL is short; the traps are in what a month is and in how the database divides.
Return each month, as text like 2026-01, and the total amount ordered in it, oldest month first.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month| month | revenue |
|---|---|
| 2026-01 | 395 |
| 2026-02 | 455 |
| 2026-03 | 155 |
Cut each date down to its month with strftime('%Y-%m', order_date), group by that and add up the amounts. The trap is grouping by the month number alone, which merges January 2026 with January 2027 as soon as the data covers more than one year. A second point to make: a month with no orders is missing from the result, it doesn't show up with 0.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| SUM(amount) / COUNT(*) |
|---|
| 125 |
SUM(amount) * 1.0 / COUNT(*) and run it again.Return the average order value as one number: the total amount divided by the number of orders. Don't round it.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT AVG(amount)
FROM orders| AVG(amount) |
|---|
| 125.63 |
AVG(amount) is the total divided by the number of orders, and it returns a decimal even when every amount is an integer. The trap is doing the division yourself with two integers. Also say what AVG does with NULL: it skips those rows, so they count neither in the total nor in the number of orders.
Customers
Per-customer questions add a join and a grouping, and with them two decisions: what to group by, and what happens to the customers with no orders.
Return the name and total amount of the three customers who have spent the most, biggest spender first.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total DESC
LIMIT 3| name | total |
|---|---|
| Ava | 395 |
| Chloe | 300 |
| Ben | 275 |
Join the orders to their customers, add up each customer's amounts, sort by that total and keep three rows. The traps: sorting by the amount of a single order instead of the customer's total, and a tie for third place, where LIMIT 3 keeps one of the tied customers and drops the other without telling you. Ask whether ties matter; if they do, rank the totals with DENSE_RANK() and filter on the rank.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| name | total |
|---|---|
| Ava | 395 |
| Ben | 275 |
| Chloe | 300 |
| Dan | 35 |
| Eve | NULL |
SUM over no values returns NULL. Add that to another number and the result is NULL as well. COALESCE returns the first of its arguments that isn't NULL: write COALESCE(SUM(o.amount), 0) and run it again.Return the name and number of orders of every customer who has placed more than one order. Two different customers can have the same name: count per customer, not per name.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT c.name, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(*) > 1| name | orders |
|---|---|
| Ava | 3 |
| Ben | 2 |
| Chloe | 2 |
Count the orders of each customer, then keep the groups with more than one: the count exists only after the grouping, so the condition goes in HAVING. The trap is grouping by name: two customers called Dan with one order each would come out as one customer with two. Group by the key, c.id, and carry the name along.
For every customer, return their name and the date of their first order. A customer who has never ordered must be in the result, with NULL as the date.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT c.name, MIN(o.order_date) AS first_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name| name | first_order |
|---|---|
| Ava | 2026-01-05 |
| Ben | 2026-01-09 |
| Chloe | 2026-02-02 |
| Dan | 2026-02-20 |
| Eve | NULL |
MIN(order_date) per customer is the first order, and the LEFT JOIN keeps Eve, whose MIN over no orders is NULL. It works on text dates because year-month-day text sorts in date order; dates stored as 03/14/2026 would not, which is the point to raise. First-order dates are also the base of the last question on this page, new versus returning customers.
Buckets and trends
The last three turn rows into the shape of a report: labels made with CASE, each month next to the one before it, and customers split by whether they have ordered before.
Put each order in a size bucket by its amount: small (under 100), medium (100 to 199) or large (200 or more). Return each bucket, the number of orders in it and their total amount.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT bucket, COUNT(*), SUM(amount)
FROM (
SELECT amount,
CASE WHEN amount < 100 THEN 'small'
WHEN amount < 200 THEN 'medium'
ELSE 'large' END AS bucket
FROM orders
) t
GROUP BY bucket| bucket | COUNT(*) | SUM(amount) |
|---|---|---|
| large | 2 | 480 |
| medium | 3 | 420 |
| small | 3 | 105 |
CASE turns each amount into a label, and the labels are then grouped like any other column. It reads top to bottom and stops at the first condition that is true, so the order of the WHEN lines matters, and a row that matches none of them gets NULL unless there is an ELSE. The trap is the edges: decide where exactly 100 and exactly 200 go, and check < against <=.
For each month, oldest first, return the month (like 2026-01), its revenue and the change from the previous month: this month's revenue minus the previous month's. The first month has no previous month, so its change is NULL.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month| month | revenue | change |
|---|---|---|
| 2026-01 | 395 | NULL |
| 2026-02 | 455 | 60 |
| 2026-03 | 155 | -300 |
The CTE folds the orders into one row per month, and LAG(revenue) OVER (ORDER BY month) fetches the revenue of the row before, which doesn't exist for the first month, so its change is NULL. The trap to raise: LAG reads the previous row, not the previous month. If a month had no orders at all it would have no row, and the month after it would be compared with the wrong one.
For each month, return the month (like 2026-01), the number of new customers (their first-ever order is in that month) and the number of returning customers (they ordered in that month, and their first order was in an earlier month). Count customers, not orders.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
GROUP BY customer_id
)
SELECT strftime('%Y-%m', o.order_date) AS month,
COUNT(DISTINCT CASE WHEN strftime('%Y-%m', f.first_date) = strftime('%Y-%m', o.order_date) THEN o.customer_id END) AS new_customers,
COUNT(DISTINCT CASE WHEN strftime('%Y-%m', f.first_date) < strftime('%Y-%m', o.order_date) THEN o.customer_id END) AS returning_customers
FROM orders o
JOIN firsts f ON f.customer_id = o.customer_id
GROUP BY strftime('%Y-%m', o.order_date)| month | new_customers | returning_customers |
|---|---|---|
| 2026-01 | 2 | 0 |
| 2026-02 | 2 | 1 |
| 2026-03 | 0 | 2 |
Find each customer's first order date, join it back to the orders, and in each month count the distinct customers whose first month is this one (new) or an earlier one (returning). The traps: counting orders instead of customers, so that Ava's two January orders make her two new customers, and comparing full dates instead of months, which turns her second January order into a returning visit. State your definition of new and returning before you write anything: the query follows from it.
Questions people ask
What SQL does a data analyst interview test?
This page can't tell you what one company will ask, and nothing here comes from a company's list. The eight questions are of the kind an analyst gets: totals over time, numbers per customer, joins that keep or drop rows, labels made with CASE, and a window function for comparing a row with the one before it. The classics asked of everyone who writes SQL (second highest salary, duplicates, top row per group) are on the general SQL interview questions page.
How do I group by month in PostgreSQL or MySQL?
strftime('%Y-%m', order_date) is SQLite's way. In PostgreSQL, to_char(order_date, 'YYYY-MM') gives the same text from a timestamp, and date_trunc('month', order_date) gives the first day of that month, as a timestamp. In MySQL it is DATE_FORMAT(order_date, '%Y-%m'). Whatever the function, keep the year in it.
Why does SUM(amount) / COUNT(*) give a whole number?
Because both sides are integers. SQLite then does an integer division and drops the fraction, and PostgreSQL does the same for integer types (5 / 2 is 2 there). MySQL is different: its / returns a decimal (3/5 is 0.60) and integer division has its own operator, DIV. To be safe anywhere, use AVG or multiply one side by 1.0 first.
Is there an SQL test for data analysts?
The free SQL test here is ten questions on a real database, from SELECT to JOINs and subqueries, with no sign-up. It isn't written for analysts only, but it covers the SQL these questions are built from.
What should I practice before a data analyst SQL interview?
Grouping and joins first, since every question on this page uses one or both. Lessons 1 to 4 are free: SELECT and WHERE, GROUP BY and HAVING, JOINs, and subqueries and CTEs. Then the reference pages on window functions and CASE WHEN.