case studies · real world impact

Real-World SQL Case Studies

Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.

real world impact

Real-World SQL Case Studies

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

Full schema + dataset
Reference solutions

// SELECT A CASE STUDY

SQL case study · Amazon

Product Performance Dashboard: Sales, Growth, and Margin Analysis by Category

🏢 AmazonIntermediate

Analyze product performance across categories: sales velocity, growth trends, profit margins, and inventory health to guide inventory investment.

Business Problem

Amazon's category management team is deciding where to push extra inventory budget next quarter — and gut feel is no longer acceptable currency. They want the call backed by three months of evidence (January–March 2024), rolled up per category.

For each category the query must produce total Q1 units and profit, profit per unit (the efficiency figure: which category earns most per item moved), and month-over-month growth rates (Jan→Feb, Feb→Mar, and overall Jan→Mar), condensed into a trend label: High-Growth (overall above 15%), Moderate-Growth, Stable, or Declining.

Two traps to respect. First, growth math on integers silently zeroes out — always force decimal division before multiplying by 100, and hand-verify one category. Second, a trend built on two products and two months of history (look at Office) is a hint, not a verdict — call out confidence, not just labels.

Dataset & Schema

Products with product_id, category, cost_price, selling_price; sales with product_id, sale_date, units_sold; inventory with product_id, current_stock, warehouse_location.

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,
  sale_date DATE,
  units_sold INTEGER,
  FOREIGN KEY(product_id) REFERENCES products(product_id)
);
CREATE TABLE inventory (
  inventory_id INTEGER PRIMARY KEY,
  product_id INTEGER,
  current_stock INTEGER,
  warehouse_location TEXT,
  FOREIGN KEY(product_id) REFERENCES products(product_id)
);

Try it yourself

Your target

Analyze product performance across categories: sales velocity, growth trends, profit margins, and inventory health to guide inventory investment.

Loading the expected output…

Starting the in-browser database…

SQL Solution

WITH monthly_sales AS (
  SELECT 
    p.product_id,
    p.product_name,
    p.category,
    p.cost_price,
    p.selling_price,
    STRFTIME('%Y-%m', s.sale_date) as month,
    SUM(s.units_sold) as units_sold,
    ROUND(SUM(s.units_sold * (p.selling_price - p.cost_price)), 2) as gross_profit
  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, STRFTIME('%Y-%m', s.sale_date)
),
category_performance AS (
  SELECT 
    category,
    SUM(CASE WHEN month = '2024-01' THEN units_sold ELSE 0 END) as jan_units,
    SUM(CASE WHEN month = '2024-02' THEN units_sold ELSE 0 END) as feb_units,
    SUM(CASE WHEN month = '2024-03' THEN units_sold ELSE 0 END) as mar_units,
    SUM(CASE WHEN month = '2024-01' THEN gross_profit ELSE 0 END) as jan_profit,
    SUM(CASE WHEN month = '2024-02' THEN gross_profit ELSE 0 END) as feb_profit,
    SUM(CASE WHEN month = '2024-03' THEN gross_profit ELSE 0 END) as mar_profit,
    COUNT(DISTINCT product_id) as product_count
  FROM monthly_sales
  GROUP BY category
),
growth_analysis AS (
  SELECT 
    category,
    jan_units,
    feb_units,
    mar_units,
    jan_profit,
    feb_profit,
    mar_profit,
    product_count,
    ROUND((feb_units - jan_units) / NULLIF(jan_units, 0) * 100, 1) as jan_to_feb_growth_pct,
    ROUND((mar_units - feb_units) / NULLIF(feb_units, 0) * 100, 1) as feb_to_mar_growth_pct,
    ROUND((mar_units - jan_units) / NULLIF(jan_units, 0) * 100, 1) as overall_growth_pct
  FROM category_performance
)
SELECT 
  category,
  product_count as num_products,
  jan_units + feb_units + mar_units as total_q1_units,
  jan_profit + feb_profit + mar_profit as total_q1_profit,
  ROUND((jan_profit + feb_profit + mar_profit) / NULLIF(jan_units + feb_units + mar_units, 0), 2) as profit_per_unit,
  jan_to_feb_growth_pct,
  feb_to_mar_growth_pct,
  overall_growth_pct,
  CASE 
    WHEN overall_growth_pct > 15 THEN 'High-Growth'
    WHEN overall_growth_pct > 0 THEN 'Moderate-Growth'
    WHEN overall_growth_pct >= -5 THEN 'Stable'
    ELSE 'Declining'
  END as category_trend
FROM growth_analysis
ORDER BY total_q1_profit DESC;

Explanation

Step-by-step Solution:

Create monthly_sales CTE by joining products with sales. Extract month using STRFTIME. Aggregate units_sold and calculate gross_profit (SUM of units * (selling_price - cost_price)) per product per month.

In category_performance CTE, aggregate monthly sales by category using conditional SUM: jan_units = SUM(units WHEN month='2024-01'), etc. This pivots months into columns. Count products per category.

Build growth_analysis to calculate month-over-month growth rates: jan_to_feb_growth_pct = ((feb - jan) / jan) * 100. Also calculate overall Q1 growth: (mar - jan) / jan * 100.

In final SELECT, sum Q1 units and profit by category. Calculate profit_per_unit to identify high-margin categories. Classify trend: >15% growth = High-Growth (invest), >0% = Moderate, -5% to 0% = Stable, <-5% = Declining (clearance).

Order by total_q1_profit DESC to prioritize categories for strategic planning. This guides inventory reallocation and promotional strategy.

Expected result

Computing the reference output…

So what? — Business Insight

Electronics is the profit leader (Rs.6.28L Q1 profit, Rs.2,651 per unit) and Office converts best per unit sold (Rs.2,365) despite tiny volume (132 units). Accessories moves the most units (1,310) but at the thinnest margin (Rs.298 per unit) — push inventory budget into Electronics and Office depth, and fix the Accessories mix before scaling it.

Interview lens — on this exact case

What the interviewer asks next

“Every category reads Stable at 0% growth, yet January-to-March units rise in all three — do you trust the trend column?”

Strong answer covers: Never trust output you haven't reconciled: hand-compute one category (Electronics 70 > 77 > 90 units), find the integer-division truncation in the growth math, and say so before recommending anything.

“Accessories moves 1,310 units at Rs.298 per unit while Office moves 132 at Rs.2,365 — where does the next inventory crore go?”

Strong answer covers: Volume vs unit-economics tradeoff: fund Electronics depth and Office growth, and fix the Accessories mix (which SKUs drag the average?) before scaling its volume.

“Office's trend rests on 2 products and Monitor Arm has two months of history — how confident are you?”

Strong answer covers: Small-sample humility: thin histories make trends noisy; ask for more months or wider SKU coverage before calling Office a growth story.

“Monitor Arm has no January row at all — growth computed from a zero base. Exclude it, footnote it, or impute it?”

Strong answer covers: New-product handling: zero-base growth is mathematically infinite but analytically meaningless — state the treatment explicitly instead of letting NULLs silently become zeros.

“The inventory table (300–1,200 units per SKU) never enters the solution — what investment question stays unanswered without it?”

Strong answer covers: Spotting the unused asset: profit without stock position can't size the budget ask — joining inventory would reveal which winner is about to stock out and which loser is tying up cash.

“Electronics earns Rs.6.28L on 237 units; Accessories Rs.3.9L on 1,310 — express the budget ask as rupees of profit per rupee of inventory. Which category wins?”

Strong answer covers: Normalizing the decision: absolute profit favors Electronics, but capital efficiency (profit per unit stocked) is the inventory budget's true objective function.

Interview lens — how to tackle any SQL case study

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.

Loading…

case studies · real world impact

Real-World SQL Case Studies

Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.

real world impact

Real-World SQL Case Studies

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

Full schema + dataset
Reference solutions

// SELECT A CASE STUDY

SQL case study · Amazon

Product Performance Dashboard: Sales, Growth, and Margin Analysis by Category

🏢 AmazonIntermediate

Analyze product performance across categories: sales velocity, growth trends, profit margins, and inventory health to guide inventory investment.

Business Problem

Dataset & Schema

Products with product_id, category, cost_price, selling_price; sales with product_id, sale_date, units_sold; inventory with product_id, current_stock, warehouse_location.

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,
  sale_date DATE,
  units_sold INTEGER,
  FOREIGN KEY(product_id) REFERENCES products(product_id)
);
CREATE TABLE inventory (
  inventory_id INTEGER PRIMARY KEY,
  product_id INTEGER,
  current_stock INTEGER,
  warehouse_location TEXT,
  FOREIGN KEY(product_id) REFERENCES products(product_id)
);

Try it yourself

Your target

Analyze product performance across categories: sales velocity, growth trends, profit margins, and inventory health to guide inventory investment.

Loading the expected output…

Starting the in-browser database…

SQL Solution

WITH monthly_sales AS (
  SELECT 
    p.product_id,
    p.product_name,
    p.category,
    p.cost_price,
    p.selling_price,
    STRFTIME('%Y-%m', s.sale_date) as month,
    SUM(s.units_sold) as units_sold,
    ROUND(SUM(s.units_sold * (p.selling_price - p.cost_price)), 2) as gross_profit
  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, STRFTIME('%Y-%m', s.sale_date)
),
category_performance AS (
  SELECT 
    category,
    SUM(CASE WHEN month = '2024-01' THEN units_sold ELSE 0 END) as jan_units,
    SUM(CASE WHEN month = '2024-02' THEN units_sold ELSE 0 END) as feb_units,
    SUM(CASE WHEN month = '2024-03' THEN units_sold ELSE 0 END) as mar_units,
    SUM(CASE WHEN month = '2024-01' THEN gross_profit ELSE 0 END) as jan_profit,
    SUM(CASE WHEN month = '2024-02' THEN gross_profit ELSE 0 END) as feb_profit,
    SUM(CASE WHEN month = '2024-03' THEN gross_profit ELSE 0 END) as mar_profit,
    COUNT(DISTINCT product_id) as product_count
  FROM monthly_sales
  GROUP BY category
),
growth_analysis AS (
  SELECT 
    category,
    jan_units,
    feb_units,
    mar_units,
    jan_profit,
    feb_profit,
    mar_profit,
    product_count,
    ROUND((feb_units - jan_units) / NULLIF(jan_units, 0) * 100, 1) as jan_to_feb_growth_pct,
    ROUND((mar_units - feb_units) / NULLIF(feb_units, 0) * 100, 1) as feb_to_mar_growth_pct,
    ROUND((mar_units - jan_units) / NULLIF(jan_units, 0) * 100, 1) as overall_growth_pct
  FROM category_performance
)
SELECT 
  category,
  product_count as num_products,
  jan_units + feb_units + mar_units as total_q1_units,
  jan_profit + feb_profit + mar_profit as total_q1_profit,
  ROUND((jan_profit + feb_profit + mar_profit) / NULLIF(jan_units + feb_units + mar_units, 0), 2) as profit_per_unit,
  jan_to_feb_growth_pct,
  feb_to_mar_growth_pct,
  overall_growth_pct,
  CASE 
    WHEN overall_growth_pct > 15 THEN 'High-Growth'
    WHEN overall_growth_pct > 0 THEN 'Moderate-Growth'
    WHEN overall_growth_pct >= -5 THEN 'Stable'
    ELSE 'Declining'
  END as category_trend
FROM growth_analysis
ORDER BY total_q1_profit DESC;