Inventory cover

1-- core.adobe_commerce.inventory_cover
2-- Stock on hand against recent sales: how many days the current stock lasts at
3-- the last 30 days' pace. The admin has a Low Stock report; this adds the rate of
4-- sale it lacks.
5--
6-- Grain: store x product x stock x website, as of the last sync. Point in time:
7-- the stock table holds current quantities only, so there is no stock history.
8-- Materialise it on a daily schedule and keep the runs if you need a trend.
9--
10-- Sales velocity is counted on the item rows that carry stock. For a configurable
11-- product that is the CHILD (simple product) row, not the parent row that
12-- sales_over_time and product_sales_over_time read, because stock belongs to the
13-- simple product. Configurable, bundle and grouped parents hold no stock and are
14-- left out.
15--
16-- A product with stock rows for several websites gets the same units_sold_30d on
17-- each row: velocity is per product, cover is per stock row. Do not sum
18-- units_sold_30d across rows.
19--
20-- Multi-Source Inventory: the legacy stock table holds the default source only.
21-- See staging/stock_item.sql.
22--
23-- Depends on: staging.adobe_commerce.stock_item, .product, .sales_order,
24--             .sales_order_item, .store_timezone
25
26WITH today AS (
27    SELECT adobe_commerce_store, CURRENT_DATE(report_timezone) AS today, report_timezone
28    FROM {{staging.adobe_commerce.store_timezone}}
29),
30
31units_sold AS (
32    SELECT
33        i.adobe_commerce_store,
34        i.product_id,
35        SUM(i.qty_ordered - i.qty_canceled - i.qty_refunded) AS units_sold_30d
36    FROM {{staging.adobe_commerce.sales_order_item}} i
37    JOIN {{staging.adobe_commerce.sales_order}} o USING (adobe_commerce_store, order_id)
38    JOIN today t ON t.adobe_commerce_store = i.adobe_commerce_store
39    WHERE i.product_type NOT IN ('configurable', 'bundle', 'grouped')
40      AND o.state NOT IN ('new', 'pending_payment', 'canceled')