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 · Flipkart
Predict products likely to go out of stock based on sales velocity, current inventory levels, and lead time, enabling proactive replenishment.
Flipkart's supply chain team keeps getting blindsided by stockouts on trending products right before sale events — empty shelves that cost revenue and seller trust at the worst possible moment. They need an early-warning query, not another post-mortem.
The logic runs in three layers. First, sales velocity: each product's average daily units sold, estimated from recent history. Second, lead-time consumption: velocity × the supplier's lead time in days (the gap between placing a purchase order and the stock arriving) — this is how much will sell out while waiting for the restock. Third, the verdict: current stock minus that consumption gives projected stock at receipt, and dividing stock by velocity gives days of supply remaining.
Each product lands in a risk bucket — CRITICAL (stock won't survive the lead time), HIGH, MEDIUM, or LOW — mapped to an action procurement can execute directly: REPLENISH_IMMEDIATELY, REPLENISH_WITHIN_7_DAYS, or MONITOR. The output is a purchase-order list, not a report.
Inventory data with product_id, current_stock, warehouse_location; sales data with product_id, units_sold, sale_date; and supplier data with product_id, lead_time_days, reorder_quantity.
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT,
current_stock INTEGER,
warehouse_location TEXT
);
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)
);
CREATE TABLE suppliers (
product_id INTEGER PRIMARY KEY,
lead_time_days INTEGER,
reorder_quantity INTEGER,
FOREIGN KEY(product_id) REFERENCES products(product_id)
);Your target
Predict products likely to go out of stock based on sales velocity, current inventory levels, and lead time, enabling proactive replenishment.
Loading the expected output…
WITH daily_sales AS (
SELECT
product_id,
SUM(units_sold) as total_sales,
COUNT(DISTINCT sale_date) as sale_days,
AVG(units_sold) as avg_daily_sales
FROM sales
WHERE sale_date >= DATE('2024-03-10', '-30 days')
GROUP BY product_id
),
sales_velocity AS (
SELECT
p.product_id,
p.product_name,
p.current_stock,
s.lead_time_days,
s.reorder_quantity,
ds.avg_daily_sales,
ROUND(ds.avg_daily_sales * s.lead_time_days, 2) as consumption_during_lead_time,
ROUND(p.current_stock - (ds.avg_daily_sales * s.lead_time_days), 2) as projected_stock_at_receipt,
CASE
WHEN p.current_stock <= (ds.avg_daily_sales * s.lead_time_days * 1.5) THEN 'CRITICAL'
WHEN p.current_stock <= (ds.avg_daily_sales * s.lead_time_days * 2) THEN 'HIGH'
WHEN p.current_stock <= (ds.avg_daily_sales * 7) THEN 'MEDIUM'
ELSE 'LOW'
END as stockout_risk,
ROUND(p.current_stock / NULLIF(ds.avg_daily_sales, 0), 0) as days_of_supply
FROM products p
LEFT JOIN daily_sales ds ON p.product_id = ds.product_id
INNER JOIN suppliers s ON p.product_id = s.product_id
)
SELECT
product_id,
product_name,
current_stock,
lead_time_days,
reorder_quantity,
ROUND(avg_daily_sales, 2) as avg_daily_sales,
consumption_during_lead_time,
projected_stock_at_receipt,
stockout_risk,
days_of_supply,
CASE
WHEN stockout_risk IN ('CRITICAL', 'HIGH') THEN 'REPLENISH_IMMEDIATELY'
WHEN stockout_risk = 'MEDIUM' THEN 'REPLENISH_WITHIN_7_DAYS'
ELSE 'MONITOR'
END as action_required
FROM sales_velocity
ORDER BY stockout_risk DESC, days_of_supply ASC;
Step-by-step Solution:
Calculate daily_sales CTE by aggregating sales data from the past 30 days. We compute total sales, number of sale days, average daily sales (avg_daily_sales), and sales variance to understand demand patterns.
In sales_velocity CTE, join products with suppliers to get lead times. Multiply avg_daily_sales by lead_time_days to estimate consumption during supplier lead time. Subtract this from current stock to predict inventory at new shipment receipt.
Apply risk classification: CRITICAL if current stock <= 1.5x lead-time consumption, HIGH if <= 2x, MEDIUM if <= 7 days of stock, otherwise LOW.
Calculate days_of_supply (current_stock / avg_daily_sales) to show runway.
Generate action recommendations: REPLENISH_IMMEDIATELY for CRITICAL/HIGH, within 7 days for MEDIUM, monitor others. This enables proactive ordering and prevents stockouts.
Computing the reference output…
Three SKUs need purchase orders today: Wireless Earbuds, USB-C Cable, and Laptop Stand sit at ~4 days of supply with negative projected stock at receipt — Laptop Stand is worst (21-day lead time, -122 units projected). Phone Case and Screen Protector are safe to monitor.
What the interviewer asks next
“Laptop Stand has a 21-day lead time and -122 projected stock — do you expedite freight or accept the stockout?”
Strong answer covers: Cost-of-stockout vs cost-of-expedite reasoning: estimate lost margin per stockout day against air-freight premiums, and name the cheaper pain.
“Velocity here rests on ~4 days of sales history — what breaks the week a sale event starts?”
Strong answer covers: Window critique: short windows overreact to spikes and miss seasonality; propose blending recent velocity with event uplifts or longer baselines.
“Projected stock goes negative (-33, -26, -122) — is a negative number meaningful or a modeling smell?”
Strong answer covers: Interpreting model output: negatives correctly signal 'already late', but the real question is days-of-supply (all ~4 days) — floor projections at zero for procurement and act on the days figure.
“The reorder quantities (100 / 150 / 50) sit next to projected shortfalls — do the POs actually cover the gap? Earbuds need ~79 units for the lead time alone plus ongoing velocity.”
Strong answer covers: Checking the fix, not just the flag: compare reorder qty against consumption-during-lead-time plus buffer — a REPLENISH flag with an undersized PO just schedules the next stockout.
“Screen Protector holds 133 days of supply with a 500-unit reorder incoming — is there an overstock problem hiding behind the stockout story?”
Strong answer covers: Both tails matter: cash locked in 200 units of slow mover plus 500 more inbound is working-capital waste — propose trimming its PO while expediting the critical three.
“All sales history spans 4 days, yet lead times run 5–21 days — the forecast window is shorter than the commitment window. How fragile is that?”
Strong answer covers: Window mismatch: velocity from 4 days extrapolated over 21-day leads amplifies noise — ask for longer history or safety-stock multipliers before betting POs on it.
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.