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 · Amazon
List all products with their total revenue, units sold, and profit margin to identify best-performing products.
An Amazon category manager walks into the weekly merchandising meeting needing one thing: a defensible leaderboard of every product, ranked by what actually matters.
For each SKU the team wants total units sold, total revenue (units × selling price), total profit (units × selling minus cost), and crucially the profit margin percentage — that is, how many paise of profit each rupee of cost produces, computed as (selling_price - cost_price) / cost_price × 100. Margin is what separates a genuine winner from a product that merely looks busy: a high-volume SKU at razor-thin margin can quietly lose money once placement and handling costs pile on.
The decision on the table is homepage placement — which SKUs deserve the hero slots, and which ones are coasting on volume while bleeding margin. One row per product, richest revenue first.
Product data with product_id, product_name, category, cost_price, selling_price; sales with sale_id, product_id, units_sold, sale_date.
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT,
category TEXT,
cost_price DECIMAL(10,2),
selling_price DECIMAL(10,2)
);
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY,
product_id INTEGER,
units_sold INTEGER,
sale_date DATE,
FOREIGN KEY(product_id) REFERENCES products(product_id)
);Your target
List all products with their total revenue, units sold, and profit margin to identify best-performing products.
Loading the expected output…
SELECT
p.product_id,
p.product_name,
p.category,
p.cost_price,
p.selling_price,
SUM(s.units_sold) as total_units_sold,
ROUND(SUM(s.units_sold * p.selling_price), 2) as total_revenue,
ROUND(SUM(s.units_sold * (p.selling_price - p.cost_price)), 2) as total_profit,
ROUND((p.selling_price - p.cost_price) * 100.0 / p.cost_price, 2) as profit_margin_pct
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name, p.category, p.cost_price, p.selling_price
ORDER BY total_revenue DESC;Step-by-step Solution:
SELECT product details: product_id, product_name, category, cost_price, selling_price.
Calculate total_units_sold using SUM(s.units_sold) to add up all units sold for each product.
Calculate total_revenue using SUM(s.units_sold * p.selling_price). This multiplies units by selling price per unit.
Calculate total_profit using SUM(s.units_sold * (p.selling_price - p.cost_price)). This is the actual profit per unit times quantity.
Calculate profit_margin_pct as ((selling_price - cost_price) / cost_price * 100) to show percentage profit.
Use LEFT JOIN to connect products with sales. GROUP BY all product columns to aggregate per product.
ORDER BY total_revenue DESC to show top revenue-generating products first. This helps identify your bestsellers and profit drivers.
Computing the reference output…
Laptop is the revenue engine (Rs.14.4L revenue, Rs.5.4L profit on just 18 units) while Mouse wins on volume (95 units). Headphones and Keyboard carry the best margins at 150% — give Laptop and Headphones the homepage slots and let Mouse monetize through volume.
What the interviewer asks next
“A product with zero sales shows NULLs under your LEFT JOIN — how do you present that row to the merchandising team?”
Strong answer covers: NULL handling as communication: COALESCE totals to 0, keep the product visible, and label it 'no sales yet' rather than silently dropping it with an INNER JOIN or confusing the room with blanks.
“Margin here divides by cost_price — what breaks if a cost is zero, stale, or averaged across suppliers?”
Strong answer covers: Input validation instinct: guard with NULLIF, spot-check one SKU end to end (Laptop: 30000/50000 = 60%), and state the assumption that costs are current before anyone acts on the leaderboard.
“Monitor has the fewest units but the second-highest revenue, while Mouse wins on volume — you get one homepage slot. Who gets it?”
Strong answer covers: Constrained-slot thinking: revenue per slot vs margin per slot vs strategic goals (Headphones at 150% margin may beat both), and naming what data would flip the call.
“Headphones earns 150% margin on 38 units; Laptop earns 60% on 18 but 5x the profit rupees — which margin do you optimize for homepage placement?”
Strong answer covers: Margin-percent vs margin-rupees tension: percentages flatter small tickets, rupees pay the bills — argue which the homepage slot should maximize, and when you'd flip.
“Every product here is Electronics — what category-mix question can this dataset never answer?”
Strong answer covers: Knowing the data's blind spots: single-category data can't justify cross-category budget shifts, so scope the recommendation to SKU prioritization and ask for the missing categories.
“March contributes a single sale row (Headphones, Mar-05) — can you say anything about momentum or seasonality?”
Strong answer covers: Temporal humility: two real months plus a stub cannot support trend claims — report the window's limits and request full-quarter coverage before narrating momentum.
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.