Inventory health

1-- amazon_seller_inventory_health - what is in Amazon's warehouses, how long it lasts,
2-- and how much of it is dead.
3--
4-- Grain: seller x marketplace x SKU. Point in time - see the note below.
5-- Depends on: staging.amazon_seller.fba_inventory, .listings,
6--             core.amazon_seller.product_sales_over_time
7--
8-- POINT IN TIME, NOT A TIME SERIES. The FBA inventory report is a snapshot and Amazon
9-- does not retain yesterday's, so this model describes now. It carries no date column
10-- for that reason: a date would imply history that does not exist, and someone would
11-- eventually GROUP BY it. To trend cover or ageing, enable Weld history tables on the
12-- fba_inventory_summary stream and build the equivalent over
13-- `fba_inventory_summary__history` - there is no way to reconstruct it later.
14--
15-- The velocity window is the last 30 days of SALE rows. Short enough to reflect
16-- current demand, long enough to survive a slow week. For seasonal catalogues, 30
17-- days across a seasonal boundary will mislead - widen it or compare against the same
18-- weeks last year.
19
20WITH velocity AS (
21    SELECT
22        amazon_seller,
23        marketplace,
24        sku,
25        SUM(quantity)                              AS units_30d,
26        SAFE_DIVIDE(SUM(quantity), 30.0)           AS units_per_day
27    FROM {{core.amazon_seller.product_sales_over_time}}
28    WHERE report_row_type = 'SALE'
29      AND date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
30    GROUP BY 1, 2, 3
31)
32
33SELECT
34    i.amazon_seller,
35    i.marketplace,
36    i.sku,
37    i.asin,
38    i.fnsku,
39    COALESCE(l.product_name, i.product_name) AS product_name,
40    l.listing_status,