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.

Skip to the 8 exercises ↓

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.

customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-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.

Exercise 1Revenue per month

Return each month, as text like 2026-01, and the total amount ordered in it, oldest month first.

Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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
Result · 3 rows
monthrevenue
2026-01395
2026-02455
2026-03155

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.

Live queryDividing two integers: the decimals are gone
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 1 row
SUM(amount) / COUNT(*)
125
Exercise 2Average order value

Return the average order value as one number: the total amount divided by the number of orders. Don't round it.

Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT AVG(amount)
FROM orders
Result · 1 row
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.

Exercise 3Top customers by spend

Return the name and total amount of the three customers who have spent the most, biggest spender first.

Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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
Result · 3 rows
nametotal
Ava395
Chloe300
Ben275

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.

Live queryA total over no orders is NULL, not 0
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 5 rows
nametotal
Ava395
Ben275
Chloe300
Dan35
EveNULL
Exercise 4Customers with more than one order

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
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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
Result · 3 rows
nameorders
Ava3
Ben2
Chloe2

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.

Exercise 5Each customer's first order date

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
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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
Result · 5 rows
namefirst_order
Ava2026-01-05
Ben2026-01-09
Chloe2026-02-02
Dan2026-02-20
EveNULL

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.

Exercise 6Order size buckets

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
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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
Result · 3 rows
bucketCOUNT(*)SUM(amount)
large2480
medium3420
small3105

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 <=.

Exercise 7Month-over-month change

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
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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
Result · 3 rows
monthrevenuechange
2026-01395NULL
2026-0245560
2026-03155-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.

Exercise 8New versus returning customers

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
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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)
Result · 3 rows
monthnew_customersreturning_customers
2026-0120
2026-0221
2026-0302

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.

Keep going

Ten questions on a real database, from SELECT to JOINs and subqueries. Free, no sign-up.

Related

SQL interview questions, with answersSQL GROUP BY, with live queriesSQL JOIN: INNER and LEFT, with live queriesSQL CASE WHEN: conditions, ELSE and NULLSQL NULL: IS NULL, COALESCE and the NOT IN trapSQL window functions: OVER and PARTITION BY, live queriesSQL top N per group, with live queriesSQL query to find duplicatesSQL second highest salary, with tiesSQL skills testSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite: Date And Time Functions, SQLite: Built-in Aggregate Functions, SQLite: SQL Language Expressions, SQLite: Built-In Scalar SQL Functions, SQLite: Window Functions, PostgreSQL documentation: Data Type Formatting Functions, PostgreSQL documentation: Date/Time Functions and Operators, PostgreSQL documentation: Mathematical Functions and Operators, MySQL 8.4 Reference Manual: Date and Time Functions, MySQL 8.4 Reference Manual: Arithmetic Operators. Last checked November 2026.