SQL Business Problems — Practice Set¶
These problems mirror real interview take-home tests and on-the-job analytics work. Each has a business context, the tables involved, and the expected output structure. Answers are hidden — try them before looking.
Use the same schema from 05-basic-query-practice: customers, products, orders.
Problem 1 — Identify Your Best Customers¶
Scenario: You're a data analyst at a subscription e-commerce company. The marketing team wants a list of your "Power Buyers" — customers who meet ALL of these criteria: - Placed at least 5 completed orders - Total spend above £500 - Made a purchase in the last 30 days (use '2024-03-01' as "today")
Return: customer_id, full_name, total_orders, total_spend, days_since_last_order
Sort by total_spend descending.
Show answer
WITH customer_stats AS (
SELECT
o.customer_id,
COUNT(*) AS total_orders,
SUM(o.order_total) AS total_spend,
MAX(o.order_date) AS last_order_date,
DATEDIFF('2024-03-01', MAX(o.order_date)) AS days_since_last
FROM orders o
WHERE o.status = 'completed'
GROUP BY o.customer_id
HAVING COUNT(*) >= 5
AND SUM(o.order_total) > 500
AND DATEDIFF('2024-03-01', MAX(o.order_date)) <= 30
)
SELECT
cs.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS full_name,
cs.total_orders,
ROUND(cs.total_spend, 2) AS total_spend,
cs.days_since_last AS days_since_last_order
FROM customer_stats cs
JOIN customers c ON cs.customer_id = c.customer_id
ORDER BY cs.total_spend DESC;
Problem 2 — Product Sell-Through Rate¶
Scenario: Operations wants to know which products are selling too slowly relative to their stock. Calculate the "sell-through rate" — units sold as a percentage of current stock.
Flag products as: - 'Critical' — sell-through > 80% (nearly out of stock) - 'Healthy' — sell-through 30-80% - 'Slow Mover' — sell-through < 30%
Return: product_id, product_name, category, stock_qty, units_sold, sell_through_pct, status_flag
Show answer
WITH product_sales AS (
SELECT
product_id,
SUM(quantity) AS units_sold
FROM orders
WHERE status = 'completed'
GROUP BY product_id
)
SELECT
p.product_id,
p.product_name,
p.category,
p.stock_qty,
COALESCE(ps.units_sold, 0) AS units_sold,
ROUND(
COALESCE(ps.units_sold, 0) * 100.0 / NULLIF(p.stock_qty + COALESCE(ps.units_sold, 0), 0), 1
) AS sell_through_pct,
CASE
WHEN COALESCE(ps.units_sold, 0) * 100.0
/ NULLIF(p.stock_qty + COALESCE(ps.units_sold, 0), 0) > 80 THEN 'Critical'
WHEN COALESCE(ps.units_sold, 0) * 100.0
/ NULLIF(p.stock_qty + COALESCE(ps.units_sold, 0), 0) >= 30 THEN 'Healthy'
ELSE 'Slow Mover'
END AS status_flag
FROM products p
LEFT JOIN product_sales ps ON p.product_id = ps.product_id
ORDER BY sell_through_pct DESC;
Problem 3 — Weekly Revenue Report with Growth¶
Scenario: Finance asks for a weekly revenue report for Q1 2024 (Jan–Mar). They want: week start date, total revenue, number of orders, average order value, and week-over-week revenue growth as a percentage.
Show answer
WITH weekly AS (
SELECT
DATE_FORMAT(order_date - INTERVAL WEEKDAY(order_date) DAY, '%Y-%m-%d') AS week_start,
COUNT(*) AS orders,
SUM(order_total) AS revenue,
AVG(order_total) AS avg_order_value
FROM orders
WHERE status = 'completed'
AND order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY DATE_FORMAT(order_date - INTERVAL WEEKDAY(order_date) DAY, '%Y-%m-%d')
)
SELECT
week_start,
orders,
ROUND(revenue, 2) AS revenue,
ROUND(avg_order_value, 2) AS avg_order_value,
LAG(revenue) OVER (ORDER BY week_start) AS prev_week_revenue,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY week_start))
/ NULLIF(LAG(revenue) OVER (ORDER BY week_start), 0) * 100, 1
) AS wow_growth_pct
FROM weekly
ORDER BY week_start;
Problem 4 — Find the Gap (Customers at Risk of Churning)¶
Scenario: A customer is "at risk" if they were active (placed ≥2 orders) in H1 2023 (Jan–Jun) but placed ZERO orders in Q3+Q4 2023 (Jul–Dec).
Return: customer_id, full_name, h1_orders, h1_revenue, last order date in H1.
Show answer
WITH h1_active AS (
SELECT
customer_id,
COUNT(*) AS h1_orders,
SUM(order_total) AS h1_revenue,
MAX(order_date) AS last_h1_order
FROM orders
WHERE status = 'completed'
AND order_date BETWEEN '2023-01-01' AND '2023-06-30'
GROUP BY customer_id
HAVING COUNT(*) >= 2
),
h2_buyers AS (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date BETWEEN '2023-07-01' AND '2023-12-31'
AND status = 'completed'
)
SELECT
h.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS full_name,
h.h1_orders,
ROUND(h.h1_revenue, 2) AS h1_revenue,
h.last_h1_order
FROM h1_active h
JOIN customers c ON h.customer_id = c.customer_id
WHERE h.customer_id NOT IN (SELECT customer_id FROM h2_buyers)
ORDER BY h.h1_revenue DESC;
Problem 5 — Rank Products Within Category (Interview Classic)¶
Scenario: Return all products with a rank within their category based on total revenue. Show the top 3 per category only.
Show answer
WITH product_revenue AS (
SELECT
p.product_id,
p.product_name,
p.category,
SUM(o.quantity * o.unit_price) AS revenue
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id AND o.status = 'completed'
GROUP BY p.product_id, p.product_name, p.category
),
ranked AS (
SELECT
product_id,
product_name,
category,
revenue,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
FROM product_revenue
)
SELECT product_id, product_name, category, revenue, rn AS rank_in_category
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;
Problem 6 — Calculate Repeat Purchase Rate¶
Scenario: What percentage of customers who made their first purchase in each month came back and made a second purchase?
Return: acquisition_month, new_customers, repeat_customers, repeat_rate_pct
Show answer
WITH first_purchase AS (
SELECT customer_id, MIN(order_date) AS first_order_date
FROM orders WHERE status = 'completed'
GROUP BY customer_id
),
second_purchase AS (
SELECT fp.customer_id, fp.first_order_date
FROM first_purchase fp
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = fp.customer_id
AND o.order_date > fp.first_order_date
AND o.status = 'completed'
)
)
SELECT
DATE_FORMAT(fp.first_order_date, '%Y-%m') AS acquisition_month,
COUNT(*) AS new_customers,
COUNT(sp.customer_id) AS repeat_customers,
ROUND(COUNT(sp.customer_id) * 100.0 / COUNT(*), 1) AS repeat_rate_pct
FROM first_purchase fp
LEFT JOIN second_purchase sp ON fp.customer_id = sp.customer_id
GROUP BY DATE_FORMAT(fp.first_order_date, '%Y-%m')
ORDER BY acquisition_month;