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.
// SELECT A CASE STUDY
SQL case study · Blinkit
Match overstocked warehouses to understocked warehouses per product and recommend the cheapest inter-warehouse transfer to fix the imbalance.
Blinkit's ops team faces a classic network imbalance: some warehouses drown in stock while others stare at empty shelves for the same product. Moving units between warehouses is cheaper than emergency purchases and faster than supplier lead times — if you pick the right moves.
The method: for each warehouse × product, compute days of supply (stock ÷ daily demand) against a 14-day buffer target. Warehouses above 20 days hold surplus; below 5 days run a deficit. Every deficit is then matched against every same-product surplus, priced by the lane's per-unit transfer cost, and only the cheapest inbound lane wins (a window function ranking candidates per deficit).
The output is an executable transfer sheet — from-warehouse, to-warehouse, units, cost — with urgency attached (Delhi's product 101 at 0.5 days of supply can't wait for the optimal truck). Flag the two judgment calls the model punts on: the flat 14-day target (volatile products deserve fatter buffers) and the missing don't-move-at-all cutoff when freight exceeds the stockout cost.
warehouse_inventory (current stock by warehouse and product), warehouse_demand (average daily demand by warehouse and product, used to calculate days of supply), and warehouse_distances (distance and per-unit transfer cost between every warehouse pair).
CREATE TABLE warehouse_inventory (
warehouse_id TEXT,
product_id INTEGER,
units_on_hand INTEGER,
PRIMARY KEY (warehouse_id, product_id)
);
CREATE TABLE warehouse_demand (
warehouse_id TEXT,
product_id INTEGER,
avg_daily_demand NUMERIC(6,2),
PRIMARY KEY (warehouse_id, product_id)
);
CREATE TABLE warehouse_distances (
from_warehouse TEXT,
to_warehouse TEXT,
distance_km INTEGER,
transfer_cost_per_unit NUMERIC(6,2),
PRIMARY KEY (from_warehouse, to_warehouse)
);Your target
Match overstocked warehouses to understocked warehouses per product and recommend the cheapest inter-warehouse transfer to fix the imbalance.
Loading the expected output…
WITH stock_position AS (
SELECT
i.warehouse_id,
i.product_id,
i.units_on_hand,
d.avg_daily_demand,
ROUND(i.units_on_hand / NULLIF(d.avg_daily_demand, 0), 1) AS days_of_supply,
ROUND(14 * d.avg_daily_demand) AS target_buffer_units
FROM warehouse_inventory i
JOIN warehouse_demand d
ON d.warehouse_id = i.warehouse_id AND d.product_id = i.product_id
),
surplus AS (
SELECT warehouse_id, product_id,
units_on_hand - target_buffer_units AS excess_units
FROM stock_position
WHERE days_of_supply > 20
),
deficit AS (
SELECT warehouse_id, product_id,
target_buffer_units - units_on_hand AS shortfall_units,
days_of_supply
FROM stock_position
WHERE days_of_supply < 5
),
candidate_transfers AS (
SELECT
def.product_id,
def.warehouse_id AS to_warehouse,
sur.warehouse_id AS from_warehouse,
MIN(sur.excess_units, def.shortfall_units) AS recommended_transfer_units,
wd.distance_km,
wd.transfer_cost_per_unit,
def.days_of_supply AS current_days_of_supply,
ROW_NUMBER() OVER (
PARTITION BY def.warehouse_id, def.product_id
ORDER BY wd.transfer_cost_per_unit ASC
) AS cost_rank
FROM deficit def
JOIN surplus sur ON sur.product_id = def.product_id
JOIN warehouse_distances wd
ON wd.from_warehouse = sur.warehouse_id AND wd.to_warehouse = def.warehouse_id
)
SELECT
product_id,
from_warehouse,
to_warehouse,
recommended_transfer_units,
distance_km,
transfer_cost_per_unit,
ROUND(recommended_transfer_units * transfer_cost_per_unit, 2) AS estimated_transfer_cost,
current_days_of_supply
FROM candidate_transfers
WHERE cost_rank = 1
ORDER BY product_id, to_warehouse;
Computing the reference output…
Step-by-step Solution:
The stock_position CTE joins inventory to demand and calculates days_of_supply (units on hand divided by average daily demand) and a 14-day target_buffer_units for every warehouse-product pair.
The surplus CTE flags warehouses with more than 20 days of supply and calculates their excess above the 14-day buffer; the deficit CTE flags warehouses with under 5 days of supply and calculates their shortfall below the buffer.
candidate_transfers cross-joins every deficit warehouse to every surplus warehouse carrying the same product, pulls the transfer cost between that specific pair, and uses LEAST() to cap the recommended transfer at whichever is smaller — the surplus available or the shortfall needed.
A ROW_NUMBER() window, partitioned by the receiving warehouse and product and ordered by transfer_cost_per_unit, ranks candidate source warehouses so the final WHERE cost_rank = 1 keeps only the cheapest source for each deficit location.
Five cheapest transfers clear every deficit for ~Rs.26.5K total — most urgent is product 101 for Delhi at 0.5 days of supply, replenished from Bangalore surplus (270 units, Rs.12,150). Execute the Delhi and Hyderabad inbound moves first; the rest ride along on the same freight logic.
What the interviewer asks next
“Delhi's product 101 sits at 0.5 days of supply and your transfer takes days — transfer, or emergency local purchase?”
Strong answer covers: Lead-time vs stockout math: the cheapest transfer loses if shelves empty first — compare transfer ETA against stockout cost per day and split the decision by urgency.
“Bangalore to Delhi costs Rs.45/unit over 2,150 km — at what point does moving stock destroy more value than it saves?”
Strong answer covers: Transfer economics: per-unit freight against per-unit margin and expiry risk; the model minimizes freight among valid moves, but someone must set the 'don't move at all' cutoff.
“The 14-day buffer target is identical for every product — how would you set it per product instead?”
Strong answer covers: Demand-variability thinking: buffers should scale with demand volatility and lead-time reliability (fast movers and flaky suppliers earn bigger cushions), not a flat constant.
“Bangalore→Delhi (270 units @ Rs.45) costs Rs.12,150 — nearly half the entire transfer bill. Split it across weeks, expedite it, or eat the Delhi stockout?”
Strong answer covers: Cost concentration: one lane dominates the economics, so it deserves its own decision — phased transfers smooth cash flow, expediting buys speed at premium, and the stockout cost per day is the tiebreaker.
“Mumbai sits at ~12.5 days of supply on all three products — neither surplus nor deficit. Coincidence, or an emergent role?”
Strong answer covers: Reading balance as signal: the naturally balanced node is the network's shock absorber — formalize Mumbai as the buffer hub instead of treating its neutrality as uninteresting.
“Hyderabad holds 600 units of product 103 (66 days of supply) while Delhi starves at 2.5 days — transfers treat the symptom. What forecasting failure created it?”
Strong answer covers: Root-cause over logistics: persistent 25x imbalances between nodes mean demand forecasts, not trucks, are broken — fix the forecast or fund transfers forever.
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.