02_inventory_allocation.sql

WITH store_product AS (
  SELECT store_id, product_id
  FROM data.stores CROSS JOIN data.products
),
stock AS (
  SELECT store_product.store_id,
          store_product.product_id, 
          COALESCE(quantity, 0) AS quantity
  FROM store_product
      LEFT JOIN data.stocks AS stock
      ON store_product.store_id = stock.store_id
      AND store_product.product_id = stock.product_id
),
stock_by_cat AS (
  SELECT cat.category_id, store_id, SUM(quantity) total_stock
  FROM stock FULL JOIN data.products AS prod
      ON stock.product_id = prod.product_id
      FULL JOIN data.categories AS cat
      ON prod.category_id = cat.category_id
  GROUP BY GROUPING SETS ((category_id, store_id),(store_id))
),
sales_by_cat AS (
  SELECT category_id,
          store_id, 
          SUM(quantity) AS quantity_sold
  FROM data.orders AS orders JOIN data.order_items AS order_items
      ON orders.order_id = order_items.order_id JOIN data.products AS prod
      ON order_items.product_id = prod.product_id
  GROUP BY GROUPING SETS ((category_id, store_id),(store_id))
),
full_stock_to_sales AS (
  SELECT COALESCE (category_name, 'Total') AS category, 
          stock_by_cat.store_id, 
          total_stock,
          total_stock / (SUM(total_stock) OVER(PARTITION BY category_name)) AS share_of_stock, 
          quantity_sold,
          quantity_sold / (SUM(quantity_sold) OVER(PARTITION BY category_name)) AS share_of_sales 
  FROM stock_by_cat LEFT JOIN sales_by_cat
      ON stock_by_cat.store_id = sales_by_cat.store_id
      AND (stock_by_cat.category_id = sales_by_cat.category_id
          OR (stock_by_cat.category_id IS NULL AND sales_by_cat.category_id IS NULL))
      LEFT JOIN data.categories AS cat
      ON stock_by_cat.category_id = cat.category_id
)
SELECT category, 
        store_id, 
        ROUND(share_of_stock, 2) share_of_stock, 
        ROUND(share_of_sales, 2) share_of_sales,
        ROUND(share_of_stock - share_of_sales, 2) AS difference
FROM full_stock_to_sales
ORDER BY category DESC, store_id