Introduction
Knowing SQL syntax is useful, but a data analyst interview rarely stops at SELECT, JOIN or GROUP BY. Interviewers want to see whether you can turn an unclear business request into reliable analysis, choose the right query structure and explain what the result means.
The ten SQL practice problems below are built around decisions a real team may need to make. The examples use PostgreSQL-style syntax, so some date functions may need small changes in MySQL, SQL Server or another database.
A Simple Practice Schema
Use the following tables as a reference while working through the problems:
| Table | Important columns |
| customers | customer_id, signup_date |
| orders | order_id, customer_id, order_date, status, revenue, campaign_id |
| order_items | order_id, product_id, quantity, unit_price |
| products | product_id, product_name, category |
| events | user_id, event_name, event_time |
| deliveries | order_id, promised_date, delivered_date |
| campaigns | campaign_id, campaign_name, spend |
Before writing any query, confirm the reporting period, the definition of the metric and the grain of each table. A technically valid query can still produce a wrong business answer if, for example, campaign spend is repeated once for every order.
1. Calculate Month-over-Month Revenue Growth
Business problem: Leadership wants to know whether revenue is growing or declining each month.
First aggregate revenue by month. Then use LAG() to bring the previous month's value onto the current row.
WITH monthly_revenue AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(revenue) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS growth_pct
FROM monthly_revenue; An interviewer may ask why the first row is null or what happens when a previous month has no revenue. Explain that NULLIF prevents division by zero and that a complete calendar table may be required when months are missing.
2. Find the Top Three Products in Each Category
Business problem: The merchandising team needs its strongest products within every category, not just across the entire catalogue.
WITH product_sales AS (
SELECT p.category, p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY 1, 2
), ranked AS (
SELECT *, DENSE_RANK() OVER
(PARTITION BY category ORDER BY revenue DESC) AS rank_in_category
FROM product_sales
)
SELECT * FROM ranked WHERE rank_in_category <= 3; The important detail is PARTITION BY category. Also clarify whether ties should produce more than three results. DENSE_RANK() keeps tied products, while ROW_NUMBER() forces exactly three rows.
3. Measure the Repeat Customer Rate
Business problem: The retention team wants the percentage of customers who have completed at least two orders.
WITH customer_orders AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
)
SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE order_count >= 2)
/ NULLIF(COUNT(*), 0), 2) AS repeat_customer_rate
FROM customer_orders; Do not begin until the denominator is clear. Should it include registered customers with no orders, all purchasers, or only customers acquired in a particular period? Metric definition matters as much as query syntax.
4. Identify Customers Who Have Become Inactive
Business problem: The CRM team wants customers whose last completed purchase was more than 90 days ago.
SELECT customer_id, MAX(order_date) AS last_order_date
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING MAX(order_date) < CURRENT_DATE - INTERVAL '90 days'; This query identifies lapsed purchasers, but not customers who registered and never bought. Ask whether those people belong in a separate segment. In a repeatable report, use a fixed analysis date instead of CURRENT_DATE so historical results remain reproducible.
5. Build a Website Conversion Funnel
Business problem: The product team needs to see how many users viewed a product, added an item to their cart, began checkout and purchased.
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'product_view') AS viewers,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'add_to_cart') AS cart_users,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'checkout') AS checkout_users,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'purchase') AS purchasers
FROM events; This is an unordered funnel. A user could purchase before the selected product-view event and still be counted at every stage. If the business requires a sequential funnel, compare event timestamps per user and enforce the expected order.
6. Detect Possible Duplicate Transactions
Business problem: Finance suspects that some customers may have been charged twice.
SELECT customer_id, revenue, order_date::date, COUNT(*) AS occurrences
FROM orders
WHERE status = 'completed'
GROUP BY customer_id, revenue, order_date::date
HAVING COUNT(*) > 1; The result is a review list, not proof of duplicate charges. A customer can legitimately place two same-value orders on one day. In an interview, recommend checking payment IDs, product combinations and timestamps before removing records.
7. Calculate a Seven-Day Rolling Average
Business problem: Daily sales fluctuate, so the operations team needs a smoother view of the trend.
WITH daily_sales AS (
SELECT order_date::date AS day, SUM(revenue) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY 1
)
SELECT day, revenue,
AVG(revenue) OVER (
ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS seven_day_average
FROM daily_sales; The window covers seven rows, which only equals seven calendar days when every date exists. Joining to a calendar table and filling missing dates with zero prevents gaps from distorting the interpretation.
8. Evaluate On-Time Delivery Performance
Business problem: Operations wants the percentage of eligible orders delivered on or before the promised date.
SELECT ROUND(100.0 *
COUNT(*) FILTER (WHERE delivered_date <= promised_date)
/ NULLIF(COUNT(*) FILTER (WHERE delivered_date IS NOT NULL), 0), 2)
AS on_time_delivery_pct
FROM deliveries; Here, undelivered orders are excluded from the denominator. That may hide overdue open orders, so confirm the intended policy. You may need separate measures for completed deliveries, currently overdue orders and cancelled orders.
9. Compare Campaign Revenue With Marketing Spend
Business problem: Marketing wants to compare attributed revenue with the cost of each campaign.
WITH campaign_revenue AS (
SELECT campaign_id, SUM(revenue) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY campaign_id
)
SELECT c.campaign_name, c.spend, COALESCE(r.revenue, 0) AS revenue,
ROUND(COALESCE(r.revenue, 0) / NULLIF(c.spend, 0), 2) AS roas
FROM campaigns c
LEFT JOIN campaign_revenue r USING (campaign_id); Aggregating orders before the join protects campaign spend from duplication. Also state that return on ad spend uses attributed revenue, not profit, and depends on the organisation's attribution rules.
10. Find Customers Who Bought Product A but Not Product B
Business problem: The sales team wants customers who purchased one product but have never purchased a complementary product.
SELECT DISTINCT o.customer_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE oi.product_id = 'A'
AND o.status = 'completed'
AND NOT EXISTS (
SELECT 1
FROM orders o2
JOIN order_items oi2 ON oi2.order_id = o2.order_id
WHERE o2.customer_id = o.customer_id
AND oi2.product_id = 'B'
AND o2.status = 'completed'
); NOT EXISTS expresses the exclusion clearly and avoids the null complications that can make NOT IN return unexpected results.
How to Explain Your SQL During the Interview
A good solution is more than a working query. Talk through your reasoning in this order:
- Define the metric and expected output grain.
- Name the tables and join keys you need.
- State assumptions about status, dates, duplicates and null values.
- Explain why you selected a CTE, window function or anti-join.
- Describe one validation check, such as reconciling totals or reviewing sample records.
- End with the decision the result can support.
If requirements are ambiguous, ask a focused question. Interviewers often value sound clarification more than a fast query based on an unsafe assumption.
Turn SQL Practice Into Demonstrable Skill With PangaeaX
Reading solutions helps you recognise patterns, but solving unfamiliar problems shows whether you can apply them. CompeteX offers SQL, data analytics and scenario-based challenges designed around practical problem-solving, with scoring and feedback to help participants identify strengths and gaps.
Within the wider PangaeaX ecosystem, AuthenX supports skill authentication through portfolio screening and AI-led interviews, while ConnectX connects data professionals through a dedicated community. Together, these products can support practice, skill demonstration and professional connection without replacing the preparation you need to do yourself.
Final Takeaway
The strongest SQL interview candidates do not simply memorise functions. They define the business question, protect the accuracy of the metric, anticipate edge cases and communicate what the result means. Practise these ten problems by first writing your own solution, then compare the logic, test it against unusual records and explain your choices aloud. That process will prepare you for the part of the interview that syntax alone cannot solve.