Data analyst SQL interviews are less about trivia and more about turning a business question into a query: "What was revenue last month?", "Who are our repeat customers?", "How fast are we growing?" Expect a few concept questions, then a live exercise with joins, GROUP BY and usually at least one window function.
This page walks through 19 questions in that style. Each has a short explanation, a query and its real output. If you want the general list (keys, normalization, DELETE vs TRUNCATE), see our 30 SQL interview questions and answers.
Jump to: set up the data · the questions · what analyst interviewers look for · FAQ
Set up the practice database
All queries use a small online shop with customers, products, orders and order_items. Paste this into a free SQLite client such as Beekeeper Studio, then try each question yourself before reading the answer. Queries use SQLite syntax; notes show how PostgreSQL, MySQL and SQL Server differ.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT, signup_date TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, status TEXT);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER);
INSERT INTO customers VALUES
(1, 'Ana Silva', 'Brazil', '2025-01-15'),
(2, 'Ben Carter', 'USA', '2025-02-03'),
(3, 'Chloe Dubois', 'France', '2025-02-20'),
(4, 'Dev Patel', 'India', '2025-03-11'),
(5, 'Emma Jones', 'USA', '2025-04-02'),
(6, 'Felix Wagner', 'Germany', '2025-05-18'),
(7, 'Grace Kim', 'USA', '2025-06-07'),
(8, 'Hiro Tanaka', 'Japan', '2025-06-30');
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 899.00),
(2, 'Headphones', 'Electronics', 129.50),
(3, 'Desk Lamp', 'Home', 34.99),
(4, 'Notebook', 'Stationery', 4.25),
(5, 'Pen Set', 'Stationery', 12.00),
(6, 'Office Chair', 'Home', 189.00),
(7, 'Webcam', 'Electronics', 59.90),
(8, 'Backpack', 'Accessories', 45.00);
INSERT INTO orders VALUES
(1, 1, '2025-07-01', 'shipped'),
(2, 2, '2025-07-03', 'shipped'),
(3, 2, '2025-07-15', 'cancelled'),
(4, 3, '2025-07-20', 'shipped'),
(5, 5, '2025-08-02', 'shipped'),
(6, 1, '2025-08-09', 'pending'),
(7, 4, '2025-08-12', 'shipped'),
(8, 5, '2025-08-21', 'shipped'),
(9, 7, '2025-09-04', 'shipped'),
(10, 2, '2025-09-10', 'shipped'),
(11, 3, '2025-09-18', 'pending'),
(12, 7, '2025-09-25', 'cancelled');
INSERT INTO order_items VALUES
(1, 1, 1), (1, 4, 3),
(2, 2, 1), (2, 7, 1),
(3, 6, 1),
(4, 3, 2), (4, 5, 2),
(5, 8, 1), (5, 4, 5),
(6, 2, 2),
(7, 1, 1), (7, 2, 1),
(8, 5, 3),
(9, 6, 1), (9, 3, 1),
(10, 4, 10), (10, 8, 2),
(11, 7, 1),
(12, 1, 1);Data analyst SQL interview questions
Questions 1 to 6 are business metrics, 7 to 12 cover customers and segments, and 13 to 19 are the window function and data-cleaning questions that separate strong candidates.
1. What is the difference between a metric, a dimension and a grain?
Analysts talk like this all the time, so interviewers check you do too. A metric is a number you measure (revenue, order count). A dimension is how you slice it (country, month, category). The grain is what one row in a table represents: here order_items has one row per product per order, while orders has one row per order. Mixing grains is the most common cause of wrong totals, because joining orders to order_items repeats each order once per item.
2. What is the total revenue from shipped orders?
Revenue lives at the order item level (quantity * price), and 'revenue' usually excludes cancelled and pending orders. State that assumption out loud.
SELECT ROUND(SUM(oi.quantity * p.price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped';Result (1 row):
| revenue |
|---|
| 2682.37 |
3. What is revenue by month?
Group by a date expression. In SQLite use strftime('%Y-%m', date); PostgreSQL uses DATE_TRUNC('month', date) and MySQL uses DATE_FORMAT(date, '%Y-%m').
SELECT strftime('%Y-%m', o.order_date) AS month,
ROUND(SUM(oi.quantity * p.price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY month
ORDER BY month;Result (3 rows):
| month | revenue |
|---|---|
| 2025-07 | 1195.13 |
| 2025-08 | 1130.75 |
| 2025-09 | 356.49 |
4. How do you calculate month-over-month growth?
Put the monthly totals in a CTE, then use LAG() to pull in the previous month's value. The first month has no previous month, so its growth is NULL. Read more in LAG and LEAD.
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * p.price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY month
)
SELECT month,
ROUND(revenue, 2) AS revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly;Result (3 rows):
| month | revenue | growth_pct |
|---|---|---|
| 2025-07 | 1195.13 | NULL |
| 2025-08 | 1130.75 | -5.4 |
| 2025-09 | 356.49 | -68.5 |
5. How do you calculate a running total?
A window function with SUM() OVER (ORDER BY ...) keeps every row and adds the cumulative sum.
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * p.price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY month
)
SELECT month,
ROUND(revenue, 2) AS revenue,
ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly;Result (3 rows):
| month | revenue | running_total |
|---|---|---|
| 2025-07 | 1195.13 | 1195.13 |
| 2025-08 | 1130.75 | 2325.88 |
| 2025-09 | 356.49 | 2682.37 |
6. What percentage of revenue does each product category contribute?
Divide each group's total by the grand total. A window function over the grouped result gives you the grand total without a second query.
SELECT p.category,
ROUND(SUM(oi.quantity * p.price), 2) AS revenue,
ROUND(100.0 * SUM(oi.quantity * p.price) / SUM(SUM(oi.quantity * p.price)) OVER (), 1) AS pct_of_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY p.category
ORDER BY revenue DESC;Result (4 rows):
| category | revenue | pct_of_total |
|---|---|---|
| Electronics | 2116.9 | 78.9 |
| Home | 293.97 | 11.0 |
| Stationery | 136.5 | 5.1 |
| Accessories | 135.0 | 5.0 |
Yes, a window function can wrap an aggregate: GROUP BY runs first, then SUM() OVER () adds up the grouped rows.
7. Who are the top 3 customers by revenue?
The classic. Join, group, sort, limit. Mention what you would do about ties (see the next question).
SELECT c.name,
ROUND(SUM(oi.quantity * p.price), 2) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY c.id, c.name
ORDER BY revenue DESC
LIMIT 3;Result (3 rows):
| name | revenue |
|---|---|
| Dev Patel | 1028.5 |
| Ana Silva | 911.75 |
| Ben Carter | 321.9 |
8. What is the best-selling product in each category?
'Top N per group' needs a window function. ROW_NUMBER() with PARTITION BY restarts the numbering for each category, then you keep row 1. Use RANK() instead if ties should both be kept. See DENSE_RANK and RANK.
WITH product_sales AS (
SELECT p.category, p.name, SUM(oi.quantity) AS units
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'shipped'
GROUP BY p.id, p.category, p.name
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC) AS rn
FROM product_sales
)
SELECT category, name, units
FROM ranked
WHERE rn = 1
ORDER BY category;Result (4 rows):
| category | name | units |
|---|---|---|
| Accessories | Backpack | 3 |
| Electronics | Laptop | 2 |
| Home | Desk Lamp | 3 |
| Stationery | Notebook | 18 |
9. How many customers have placed more than one order (repeat customers)?
Aggregate to one row per customer first, then count how many pass the bar. Counting distinct orders protects you from join fan-out.
SELECT COUNT(*) AS repeat_customers
FROM (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
) AS repeaters;Result (1 row):
| repeat_customers |
|---|
| 5 |
10. Which customers have never placed an order?
Anti-join: LEFT JOIN and keep the rows where the right side is NULL. NOT EXISTS works too. See LEFT JOIN.
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Result (2 rows):
| id | name |
|---|---|
| 6 | Felix Wagner |
| 8 | Hiro Tanaka |
11. What is the order cancellation rate?
Conditional aggregation: a boolean expression is 1 or 0, so AVG of it is the share. Multiply by 100.0 (not 100) to avoid integer division.
SELECT COUNT(*) AS total_orders,
SUM(status = 'cancelled') AS cancelled,
ROUND(100.0 * AVG(status = 'cancelled'), 1) AS cancellation_pct
FROM orders;Result (1 row):
| total_orders | cancelled | cancellation_pct |
|---|---|---|
| 12 | 2 | 16.7 |
In PostgreSQL and SQL Server use SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END), which works everywhere. See CASE WHEN.
12. How do you count orders per status in separate columns (pivot)?
SQL has no universal PIVOT, so analysts use CASE WHEN inside aggregates. See also how to pivot in SQL.
SELECT strftime('%Y-%m', order_date) AS month,
SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY month
ORDER BY month;Result (3 rows):
| month | shipped | pending | cancelled |
|---|---|---|---|
| 2025-07 | 3 | 0 | 1 |
| 2025-08 | 3 | 1 | 0 |
| 2025-09 | 2 | 1 | 1 |
13. What is the average order value (AOV)?
Average of order totals, not of line items. Build order totals in a subquery, then average them. Averaging line items directly is the classic wrong answer.
SELECT ROUND(AVG(order_total), 2) AS avg_order_value
FROM (
SELECT o.id, SUM(oi.quantity * p.price) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY o.id
) AS order_totals;Result (1 row):
| avg_order_value |
|---|
| 335.3 |
14. How do you find duplicate rows?
Group by the columns that should be unique and keep groups with a count above one. The sample data has no duplicates, so here we look for customers who share a country.
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
HAVING COUNT(*) > 1;Result (1 row):
| country | customers |
|---|---|
| USA | 3 |
To delete duplicates, number the rows with ROW_NUMBER() OVER (PARTITION BY ... ORDER BY id) and delete everything where the number is greater than 1. Always SELECT first to check what you are about to remove.
15. What is each customer's first order date and days to their second order?
Number each customer's orders with ROW_NUMBER(), then compare dates. julianday() is SQLite's date difference; PostgreSQL subtracts dates directly and SQL Server uses DATEDIFF. Related: comparing dates.
WITH numbered AS (
SELECT customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS n
FROM orders
)
SELECT a.customer_id,
a.order_date AS first_order,
b.order_date AS second_order,
CAST(julianday(b.order_date) - julianday(a.order_date) AS INTEGER) AS days_between
FROM numbered a
JOIN numbered b ON b.customer_id = a.customer_id AND b.n = 2
WHERE a.n = 1
ORDER BY a.customer_id;Result (5 rows):
| customer_id | first_order | second_order | days_between |
|---|---|---|---|
| 1 | 2025-07-01 | 2025-08-09 | 39 |
| 2 | 2025-07-03 | 2025-07-15 | 12 |
| 3 | 2025-07-20 | 2025-09-18 | 60 |
| 5 | 2025-08-02 | 2025-08-21 | 19 |
| 7 | 2025-09-04 | 2025-09-25 | 21 |
16. How do you calculate a 2-order moving average?
Add a window frame: ROWS BETWEEN 1 PRECEDING AND CURRENT ROW averages the current and previous row. Read what a moving average is.
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * p.price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY month
)
SELECT month,
ROUND(revenue, 2) AS revenue,
ROUND(AVG(revenue) OVER (ORDER BY month
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 2) AS moving_avg
FROM monthly;Result (3 rows):
| month | revenue | moving_avg |
|---|---|---|
| 2025-07 | 1195.13 | 1195.13 |
| 2025-08 | 1130.75 | 1162.94 |
| 2025-09 | 356.49 | 743.62 |
17. How do you rank customers into quartiles?
NTILE(4) splits ordered rows into four equal buckets. Analysts use it for segmentation like 'top 25% of customers'.
WITH spend AS (
SELECT c.name, SUM(oi.quantity * p.price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY c.id, c.name
)
SELECT name,
ROUND(revenue, 2) AS revenue,
NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
FROM spend
ORDER BY revenue DESC;Result (6 rows):
| name | revenue | quartile |
|---|---|---|
| Dev Patel | 1028.5 | 1 |
| Ana Silva | 911.75 | 1 |
| Ben Carter | 321.9 | 2 |
| Grace Kim | 223.99 | 2 |
| Emma Jones | 102.25 | 3 |
| Chloe Dubois | 93.98 | 4 |
18. How do you handle NULLs in a report?
Use COALESCE to replace NULL with a default. Here a LEFT JOIN leaves customers without orders with a NULL count, and COUNT(o.id) correctly ignores them. See the COALESCE lesson.
SELECT c.name,
COUNT(o.id) AS orders,
COALESCE(MAX(o.order_date), 'never') AS last_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders DESC, c.name;Result (8 rows):
| name | orders | last_order |
|---|---|---|
| Ben Carter | 3 | 2025-09-10 |
| Ana Silva | 2 | 2025-08-09 |
| Chloe Dubois | 2 | 2025-09-18 |
| Emma Jones | 2 | 2025-08-21 |
| Grace Kim | 2 | 2025-09-25 |
| Dev Patel | 1 | 2025-08-12 |
| Felix Wagner | 0 | never |
| Hiro Tanaka | 0 | never |
19. Which customers have more orders than the average customer?
Compare each group to an overall figure with a scalar subquery. The subquery returns one number, the average orders per customer who ordered.
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > (SELECT COUNT(*) * 1.0 / COUNT(DISTINCT customer_id) FROM orders);Result (1 row):
| customer_id | orders |
|---|---|
| 2 | 3 |
What data analyst interviewers look for
- Definitions before code. Ask what counts as revenue, an active customer or a repeat order. Cancelled orders are the usual trap.
- Grain awareness. Say what one row represents after each join. Joining orders to order items repeats order-level values, which inflates sums and averages.
- Sanity checks. Compare your total against a simple query. If monthly revenue does not add up to total revenue, something is wrong.
- Readable SQL. Use CTEs with clear names instead of deeply nested subqueries, and alias every column you output.
- Explain the result. Finish with one sentence on what the numbers mean. Analysts are hired to communicate, not just query.
Where to go next
- Practice with 28 SQL practice problems with solutions.
- See more worked queries in 38 SQL query examples, and keep the SQL cheat sheet open while you study.
- Learn the fundamentals interactively: GROUP BY, joins and LEFT JOIN.
FAQ
What SQL do data analysts need for interviews?
Joins, GROUP BY with aggregates, CASE WHEN, subqueries and CTEs, date functions, and window functions (ROW_NUMBER, RANK, LAG, running totals). Performance tuning matters less than for engineers.
How hard are SQL interviews for data analysts?
Most are medium difficulty: a business question that needs two or three joins and one aggregate or window function. The hard part is usually interpreting the question, not the syntax.
Which SQL dialect should I practice for a data analyst interview?
Ask the recruiter. If they do not say, practice PostgreSQL-style standard SQL and learn the date function differences for MySQL and SQL Server.
How long does it take to prepare?
If you know basic SELECT and joins, one to two weeks of daily practice covers aggregates, CTEs and window functions. Starting from zero, plan for a month. See is SQL easy to learn?
Where can I practice for free?
Run the queries above in any SQLite client, or use SQL Easy's free interactive lessons, which run in your browser with instant feedback.