Business Problem

Blinkit's supply chain team is dealing with a familiar quick-commerce headache: one dark store is sitting on weeks of excess stock for a SKU while another nearby store is about to run out of the exact same product. They want a query that, for every product, finds warehouses with more than 20 days of supply on hand, matches them against warehouses with less than 5 days of supply, and recommends the cheapest inter-warehouse transfer (by transfer cost per unit) to fix the imbalance — including how many units to move and the estimated cost — so ops can approve transfers instead of placing an emergency reorder from the supplier. Multi-warehouse retailers and quick-commerce platforms constantly have inventory imbalances — one location overstocked, another about to stock out — and moving stock between warehouses is almost always faster and cheaper than an emergency supplier reorder. This requires matching surplus locations to deficit locations for the same product, factoring in the transfer cost between specific warehouse pairs, which is a self-referencing optimization problem rather than a simple aggregate.

Dataset & Schema

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)
);

SQL Solution

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,
    LEAST(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;

Explanation