Business Problem

Amazon's category management team is deciding where to push extra inventory budget next quarter and wants the decision backed by data rather than gut feel. For each product category, calculate total units sold, total profit, profit per unit, and month-over-month growth rates across three recent months, rolled up into an overall trend label (Growing / Stable / Declining) so the team can quickly separate categories worth doubling down on from ones that need a clearance strategy instead.

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

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