Almost every data analyst interview has a SQL round, and the questions repeat more than you'd think. The same dozen patterns show up in different costumes: top N per group, running totals, retention, finding duplicates, comparing to a previous period. This guide walks through 20 questions in rising difficulty, each with a working answer and the idea behind it.
All questions use the same small schema, which mirrors what you'll practice on Crossway Arena:
customers(id, name, country, signup_date)orders(id, customer_id, order_date, amount, status)where status is completed, refunded or pendingevents(user_id, event, event_date)employees(id, name, dept, salary, manager_id)
Basics: filtering, grouping and joins
1. Revenue by country from completed orders
SELECT c.country, ROUND(SUM(o.amount), 2) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.country
ORDER BY revenue DESC;
The filter on status goes in WHERE, before grouping. Forgetting it is the most common mistake in this question.
2. Customers who have never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
A LEFT JOIN keeps every customer, and rows with no match have NULL order columns. NOT EXISTS works too and is often faster on big tables.
3. Number of orders and total spend per customer, including zero
SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.amount), 0) AS spend
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'completed'
GROUP BY c.id, c.name;
Note the status filter is in the ON clause. Putting it in WHERE would drop customers with no completed orders, turning the LEFT JOIN back into an inner join.
4. Countries with more than 5 customers
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
HAVING COUNT(*) > 5;
HAVING filters after grouping. WHERE can't see aggregates.
5. Refund rate by country
SELECT c.country,
ROUND(100.0 * SUM(CASE WHEN o.status = 'refunded' THEN 1 ELSE 0 END) / COUNT(*), 1) AS refund_pct
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.country;
Multiply by 100.0, not 100, so the division isn't integer division in databases that truncate.
Window functions
6. Rank customers by total spend
SELECT name, spend, DENSE_RANK() OVER (ORDER BY spend DESC) AS rnk
FROM (
SELECT c.name, SUM(o.amount) AS spend
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.id, c.name
) t;
Know the difference: ROW_NUMBER gives unique numbers, RANK leaves gaps after ties, DENSE_RANK doesn't.
7. Top customer in each country
WITH s AS (
SELECT c.country, c.name, SUM(o.amount) AS spend,
ROW_NUMBER() OVER (PARTITION BY c.country ORDER BY SUM(o.amount) DESC) AS rn
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.country, c.id, c.name
)
SELECT country, name, spend FROM s WHERE rn = 1;
This "top N per group" pattern is the single most common window function question.
8. Running revenue by day
SELECT order_date,
SUM(daily) OVER (ORDER BY order_date) AS running_revenue
FROM (
SELECT order_date, SUM(amount) AS daily
FROM orders WHERE status = 'completed'
GROUP BY order_date
) d;
9. Each customer's first order
SELECT customer_id, id AS order_id, order_date
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, id) AS rn
FROM orders
) t WHERE rn = 1;
Adding id to the ORDER BY breaks ties so the answer is stable.
10. Days between each customer's orders
SELECT customer_id, id,
julianday(order_date) - julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS gap_days
FROM orders WHERE status = 'completed';
LAG looks at the previous row in the window. Date math syntax differs by database: DATEDIFF in many, julianday in SQLite, subtraction of dates in Postgres.
11. Month over month revenue change
WITH m AS (
SELECT substr(order_date, 1, 7) AS month, SUM(amount) AS revenue
FROM orders WHERE status = 'completed' GROUP BY month
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM m;
Product and growth questions
12. Daily active users
SELECT event_date, COUNT(DISTINCT user_id) AS dau
FROM events GROUP BY event_date ORDER BY event_date;
13. Users active on two consecutive days
SELECT DISTINCT a.user_id
FROM events a
JOIN events b ON b.user_id = a.user_id
AND julianday(b.event_date) - julianday(a.event_date) = 1;
14. Customers who came back within 30 days of their first order
WITH f AS (SELECT customer_id, MIN(order_date) AS first_date FROM orders GROUP BY customer_id)
SELECT COUNT(DISTINCT o.customer_id)
FROM orders o JOIN f ON f.customer_id = o.customer_id
WHERE o.order_date > f.first_date
AND julianday(o.order_date) - julianday(f.first_date) <= 30;
15. Signup cohort revenue
SELECT substr(c.signup_date, 1, 7) AS cohort,
COUNT(DISTINCT c.id) AS customers,
ROUND(COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.amount END), 0), 2) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY cohort ORDER BY cohort;
COUNT(DISTINCT c.id) matters here: the join creates one row per order, so a plain COUNT would count orders, not customers.
16. Longest streak of consecutive active days per user
WITH d AS (SELECT DISTINCT user_id, event_date FROM events),
g AS (
SELECT user_id,
julianday(event_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM d
)
SELECT user_id, MAX(n) AS streak
FROM (SELECT user_id, grp, COUNT(*) AS n FROM g GROUP BY user_id, grp)
GROUP BY user_id ORDER BY streak DESC;
The "date minus row number" trick turns each run of consecutive days into a constant group key. It's a classic hard question.
Employee and hierarchy questions
17. Second highest salary
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Or use DENSE_RANK and pick rank 2, which generalizes to the Nth highest.
18. Employees who earn more than their manager
SELECT e.name
FROM employees e JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;
A self join: the same table joined to itself with different roles.
19. Everyone's level in the org chart
WITH RECURSIVE t(id, name, lvl) AS (
SELECT id, name, 0 FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, t.lvl + 1 FROM employees e JOIN t ON e.manager_id = t.id
)
SELECT name, lvl FROM t ORDER BY lvl, name;
Messy data
20. Duplicate accounts by email
SELECT LOWER(TRIM(email)) AS email, COUNT(*) AS n
FROM raw_signups
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;
Real data has spaces and mixed case. Interviewers increasingly test cleaning because it's most of the real job.
How to practice so it sticks
- Practice on real tables, not in your head. Run every query and check the output.
- Say the plan before writing: "I'll find each customer's first order, then join back to count later orders."
- Check edge cases: ties, NULLs, customers with zero orders, refunds.
- Time yourself. Most screens give 15 to 20 minutes per question.
For a step by step learning plan, see how to practice SQL for interviews.
The mistakes interviewers watch for
- Counting rows instead of entities after a join. Use COUNT(DISTINCT) when a join multiplies rows.
- Filtering the wrong table in a LEFT JOIN, which silently turns it into an inner join.
- Integer division in percentages. Multiply by 100.0.
- Ignoring NULLs: AVG skips NULLs, COUNT(column) skips NULLs, COUNT(*) doesn't.
- Unstable ordering: ties in ROW_NUMBER without a tie breaker give different answers on different runs.
- Forgetting refunds or test accounts in revenue questions.
How to talk through a SQL question live
- Restate the question and confirm the output: "One row per country with total completed revenue, highest first?"
- Ask about edge cases: refunds, duplicates, time zones, ties.
- Describe your plan in plain words before typing.
- Write it in steps with CTEs, so each piece is easy to check.
- Sanity check the result: does the total match a simple sum? Are there NULLs where there shouldn't be?
Interviewers often care more about this process than about the final syntax.
Bonus: three questions that trip people up
Median order value
Many databases lack a MEDIAN function. Use ROW_NUMBER and COUNT in a window, then pick the middle row or the average of the two middle rows. It's a good test of window function fluency.
Customers who bought every product in a category
Group by customer, count distinct products in the category, and compare to the total number of products in that category with HAVING. It's relational division in disguise.
Users whose activity dropped month over month
Aggregate activity per user per month, then use LAG to compare each month with the previous one and filter where it fell.
Questions
What SQL questions are asked in data analyst interviews?
Joins, grouping with HAVING, top N per group with window functions, running totals, period over period change, retention and cohorts, self joins, and cleaning messy text.
What's the difference between RANK and DENSE_RANK?
RANK leaves gaps after ties (1, 2, 2, 4) while DENSE_RANK doesn't (1, 2, 2, 3). ROW_NUMBER always gives unique numbers.
Why does my LEFT JOIN act like an inner join?
A filter on the right table in the WHERE clause removes rows where it's NULL. Move that filter into the ON clause.
How do I find the longest streak in SQL?
Take distinct user and date pairs, subtract a ROW_NUMBER from the date to create a group key for each run, then count rows per group.
How long should I practice SQL before interviews?
Most people need 3 to 4 weeks of daily practice on realistic tables to solve medium questions in 15 minutes.