What is inventory turnover, and how do you measure it in Metabase?

COGS per quarter over average snapshot value, scaled by 4.

-- Quarterly inventory turnover, annualized:
-- COGS from sold line items over average inventory value from daily snapshots.
WITH cogs AS (
  SELECT
    date_trunc('quarter', o.created_at)           AS quarter,
    SUM(li.quantity * li.unit_cost)               AS cogs
  FROM order_line_items li
  JOIN orders o ON o.id = li.order_id
  WHERE o.status NOT IN ('cancelled', 'refunded')
  GROUP BY date_trunc('quarter', o.created_at)
),
inventory AS (
  SELECT
    date_trunc('quarter', snapshot_date)          AS quarter,
    AVG(daily_value)                              AS avg_inventory_value
  FROM (
    SELECT
      snapshot_date,
      SUM(on_hand * unit_cost)                    AS daily_value
    FROM inventory_snapshots
    GROUP BY snapshot_date
  ) daily
  GROUP BY date_trunc('quarter', snapshot_date)
)
SELECT
  c.quarter,
  ROUND(c.cogs, 0)                                AS cogs,
  ROUND(i.avg_inventory_value, 0)                 AS avg_inventory_value,
  ROUND(4.0 * c.cogs / NULLIF(i.avg_inventory_value, 0), 2)
    AS annualized_turnover
FROM cogs c
JOIN inventory i ON i.quarter = c.quarter
ORDER BY c.quarter;

Rank categories by how long their stock lasts — the top of this list is your overstock.

-- Days of inventory (DSI) by category over the trailing 90 days:
-- how long the current stock would last at the recent sell-through rate.
WITH cogs AS (
  SELECT
    p.category,
    SUM(li.quantity * li.unit_cost)               AS cogs_90d
  FROM order_line_items li
  JOIN orders o   ON o.id = li.order_id
  JOIN products p ON p.id = li.product_id
  WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
    AND o.status NOT IN ('cancelled', 'refunded')
  GROUP BY p.category
),
inventory AS (
  SELECT
    category,
    AVG(daily_value)                              AS avg_inventory_value
  FROM (
    SELECT
      p.category,
      s.snapshot_date,
      SUM(s.on_hand * s.unit_cost)                AS daily_value
    FROM inventory_snapshots s
    JOIN products p ON p.id = s.product_id
    WHERE s.snapshot_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY p.category, s.snapshot_date
  ) daily
  GROUP BY category
)
SELECT
  i.category,
  ROUND(i.avg_inventory_value, 0)                 AS avg_inventory_value,
  ROUND(90.0 * i.avg_inventory_value / NULLIF(c.cogs_90d, 0), 0)
    AS days_of_inventory
FROM inventory i
JOIN cogs c ON c.category = i.category
ORDER BY days_of_inventory DESC;