Business Problem

Flipkart's supply chain team keeps getting blindsided by stockouts on trending products right before a sale event, costing both revenue and seller trust. Build a query that estimates each product's average daily sales velocity, projects how much stock will be consumed during the supplier's lead time, and compares that against current inventory to flag products at risk of running out before the next restock arrives — with a clear 'days of supply remaining' figure and an action flag (reorder now / monitor / safe) the procurement team can act on directly.

Dataset & Schema

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

SQL Solution

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,
    STDDEV(units_sold) as sales_variance
  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 sales_velocity 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;

Explanation