Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Shopify
Calculate total amount spent and number of orders per customer to understand customer value.
Shopify's merchant-success team wants a plain-English customer value ranking: for each customer, total orders, total spent, and average order value (AOV — total divided by orders), richest first. No models, no scores — just the facts that decide who gets the retention budget.
The ranking answers three practical questions. Who is most valuable right now? (highest total). Who buys biggest? (highest AOV — often a different name). And who is underperforming their history? — the single-order customer is the cheapest growth on the board, while the frequent-but-tiny buyer needs bundles, not discounts.
Its deliberate blind spot is time: totals can't tell a current customer from a historical one. Name that gap and the upgrade path (RFM — adding recency and frequency) rather than letting the ranking over-claim.
Customer data with customer_id, customer_name, city; orders with order_id, customer_id, order_amount, order_date.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name TEXT,
city TEXT
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_amount DECIMAL(10,2),
order_date DATE,
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);Your target
Calculate total amount spent and number of orders per customer to understand customer value.
Loading the expected output…
SELECT
c.customer_id,
c.customer_name,
c.city,
COUNT(o.order_id) as total_orders,
ROUND(SUM(o.order_amount), 2) as total_spent,
ROUND(AVG(o.order_amount), 2) as average_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.city
ORDER BY total_spent DESC;Step-by-step Solution:
Start with SELECT to choose columns: customer_id, customer_name, city from the customers table.
Use COUNT(o.order_id) to count the number of orders per customer.
Use SUM(o.order_amount) to calculate total amount spent. AVG(o.order_amount) shows average per order.
Use LEFT JOIN to connect customers with their orders. LEFT JOIN ensures all customers appear even if they have no orders (count = 0).
Use GROUP BY with customer_id, customer_name, city to aggregate metrics per customer.
ORDER BY total_spent DESC to show highest-value customers first. This simple query reveals your power users and helps identify which customers need attention.
Computing the reference output…
Vikram Desai is the most valuable customer (Rs.18,500 across 3 orders) while Amit Patel has the highest basket size (Rs.6,750 average). Neha Gupta's single Rs.3,000 order marks her as the upsell candidate, and Raj Kumar's 3 small orders suggest bundle offers to lift his Rs.3,667 average.
What the interviewer asks next
“Vikram tops total spend (Rs.18,500) but Amit has the bigger basket (Rs.6,750 AOV) — who is more valuable?”
Strong answer covers: Total vs trajectory: current value (Vikram) vs marginal value per future order (Amit) — and asking for margin and repeat probability before crowning anyone.
“Neha has one Rs.3,000 order — win-back campaign or write-off?”
Strong answer covers: Expected-value thinking: cost of one nudge vs probability of a second purchase; single-order customers are usually the cheapest growth if the product experience was fine.
“Your ranking ignores dates entirely — what does recency add that totals hide?”
Strong answer covers: Upgrade path to RFM: Rs.18,500 spent two years ago is not Rs.18,500 of live value — recency separates current customers from historical ones.
“Raj Kumar ordered 3 times but averages Rs.3,667 — the lowest AOV among repeat buyers. Bundle pitch: what lift, in rupees, makes the campaign worth running?”
Strong answer covers: Quantifying the nudge: lifting Raj to a Rs.5,000 average across his 3-order cadence is worth ~Rs.4,000 a cycle — put the expected value on the campaign before spending a rupee promoting it.
“Five customers across five cities — Mumbai, Delhi, Bangalore, Chennai, Pune. Segment geographically?”
Strong answer covers: n=5 discipline: city cuts on single observations are storytelling, not segmentation — name the sample size at which geography earns a column (hint: far more than five).
“Neha's single Rs.3,000 order vs Amit's two averaging Rs.6,750 — whose second order is more probable, and how would you estimate it?”
Strong answer covers: Second-order probability: high-AOV first buys historically repeat better (commitment signal) — propose validating with cohort repeat rates rather than assuming all first orders are equal.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.