views.sql

-- Generated from config/business_rules.yaml. Do not hand-edit --
-- regenerate by re-running data/load.py's build_views().

-- shrink.formula: units_shipped - units_sold
-- shrink.units_shipped_formula: cases_received * case_size
-- shrink.default_measure: cost
-- shrink.default_scope_depts: ['Produce', 'Dairy', 'Meat', 'Bakery', 'Deli']
-- shrink.rate_denominator_default: units_shipped

CREATE OR REPLACE VIEW shrink_facts AS
WITH shipped AS (
    SELECT
        sh.date,
        sh.store_id,
        sh.banner,
        sh.region,
        sh.item_id,
        SUM(sh.cases_received * i.case_size) AS units_shipped
    FROM shipments sh
    JOIN items i USING (item_id)
    GROUP BY sh.date, sh.store_id, sh.banner, sh.region, sh.item_id
),
sold AS (
    SELECT
        date, store_id, banner, region, item_id,
        SUM(units_sold) AS units_sold
    FROM sales_daily
    GROUP BY date, store_id, banner, region, item_id
)
SELECT
    COALESCE(shipped.date, sold.date) AS date,
    COALESCE(shipped.store_id, sold.store_id) AS store_id,
    COALESCE(shipped.banner, sold.banner) AS banner,
    COALESCE(shipped.region, sold.region) AS region,
    COALESCE(shipped.item_id, sold.item_id) AS item_id,
    i.description,
    i.dept,
    i.category,
    COALESCE(shipped.units_shipped, 0) AS units_shipped,
    COALESCE(sold.units_sold, 0) AS units_sold,
    (COALESCE(shipped.units_shipped, 0) - COALESCE(sold.units_sold, 0))
        AS unit_shrink,
    (COALESCE(shipped.units_shipped, 0) - COALESCE(sold.units_sold, 0))
        * i.unit_cost AS cost_shrink,
    i.dept IN ('Produce', 'Dairy', 'Meat', 'Bakery', 'Deli') AS in_default_scope
FROM shipped
FULL OUTER JOIN sold
    ON shipped.date = sold.date
    AND shipped.store_id = sold.store_id
    AND shipped.item_id = sold.item_id
JOIN items i ON i.item_id = COALESCE(shipped.item_id, sold.item_id);