Update Inventory Tool Demand Forecast

Query

-- Refresh INPUT_FORECAST_DEMAND from current sources.
--
-- Volume (daily_units_sold):
--   * Current month  -> Shopify MTD units / completed days (day-of-month - 1).
--                       Falls back to budget when the month has no completed days
--                       or no Shopify orders yet.
--   * Future months  -> BUDGET_CURRENT.units_sold (NON_FINANCIAL row) / days in month.
--
-- Color mix (pcnt_aqua / pcnt_lemon / pcnt_white / pcnt_lilac):
--   * Trailing-90-day SKU split from RAW_DB.SHOPIFY.orders (same mix for every row).
--
-- Idempotent: run any time the budget or trailing mix shifts.

merge into utilities_db.supply_chain.input_forecast_demand as tgt
using (
    with color_mix as (
        select
            round(
                sum(case when item.value:sku::varchar = 'TCMOD1AQUA'  then item.value:quantity::integer else 0 end)
                / nullif(sum(item.value:quantity::integer), 0),
                4
            ) as pcnt_aqua,
            round(
                sum(case when item.value:sku::varchar = 'TCMOD1LEMON' then item.value:quantity::integer else 0 end)
                / nullif(sum(item.value:quantity::integer), 0),
                4
            ) as pcnt_lemon,
            round(
                sum(case when item.value:sku::varchar = 'TCMOD1WHITE' then item.value:quantity::integer else 0 end)
                / nullif(sum(item.value:quantity::integer), 0),
                4
            ) as pcnt_white,
            round(
                sum(case when item.value:sku::varchar = 'TCMOD1LILAC' then item.value:quantity::integer else 0 end)
                / nullif(sum(item.value:quantity::integer), 0),
                4
            ) as pcnt_lilac
        from raw_db.shopify.orders as ord,
            lateral flatten(input => ord.line_items) as item
        where ord.cancelled_at is null
            and ord.test = false
            and ord.created_at >= dateadd(day, -90, current_date)
            and item.value:sku::varchar in ('TCMOD1AQUA', 'TCMOD1LEMON', 'TCMOD1WHITE', 'TCMOD1LILAC')
    ),

    mtd_actual as (
        -- Units sold in the current month through end of yesterday.
        select
            sum(item.value:quantity::integer) as mtd_units,
            greatest(day(current_date) - 1, 0)  as completed_days
        from raw_db.shopify.orders as ord,
            lateral flatten(input => ord.line_items) as item
        where ord.cancelled_at is null
            and ord.test = false
            and ord.created_at >= date_trunc('month', current_date)
            and ord.created_at <  current_date
            and item.value:sku::varchar in ('TCMOD1AQUA', 'TCMOD1LEMON', 'TCMOD1WHITE', 'TCMOD1LILAC')
    ),

    budget_monthly as (
        -- Current + future months from the active budget, NON_FINANCIAL row.
        select
            bud.month,
            bud.units_sold,
            day(last_day(bud.month)) as days_in_month
        from utilities_db.finance.budget_current as bud
        where bud.recognition_type = 'NON_FINANCIAL'
            and bud.month >= date_trunc('month', current_date)
    ),

    current_month_demand as (
        select
            date_trunc('month', current_date)::date as month,
            round(
                coalesce(
                    case
                        when mtd.completed_days > 0 and mtd.mtd_units is not null
                            then mtd.mtd_units / mtd.completed_days
                    end,
                    bud.units_sold / nullif(bud.days_in_month, 0)
                )
            )::integer as daily_units_sold
        from mtd_actual as mtd
        left join budget_monthly as bud
            on bud.month = date_trunc('month', current_date)
    ),

    future_month_demand as (
        select
            bud.month,
            round(bud.units_sold / nullif(bud.days_in_month, 0))::integer as daily_units_sold
        from budget_monthly as bud
        where bud.month > date_trunc('month', current_date)
    ),

    monthly_demand as (
        select month, daily_units_sold from current_month_demand
        union all
        select month, daily_units_sold from future_month_demand
    )

    select
        dem.month,
        dem.daily_units_sold,
        mix.pcnt_aqua,
        mix.pcnt_lemon,
        mix.pcnt_white,
        mix.pcnt_lilac
    from monthly_demand as dem
    cross join color_mix as mix
    where dem.daily_units_sold is not null
) as src
on tgt.month = src.month
when matched then update set
    daily_units_sold = src.daily_units_sold,
    pcnt_aqua        = src.pcnt_aqua,
    pcnt_lemon       = src.pcnt_lemon,
    pcnt_white       = src.pcnt_white,
    pcnt_lilac       = src.pcnt_lilac
when not matched then insert (
    month,
    daily_units_sold,
    pcnt_aqua,
    pcnt_lemon,
    pcnt_white,
    pcnt_lilac
) values (
    src.month,
    src.daily_units_sold,
    src.pcnt_aqua,
    src.pcnt_lemon,
    src.pcnt_white,
    src.pcnt_lilac
);

Overview

Regenerates UTILITIES_DB.SUPPLY_CHAIN.INPUT_FORECAST_DEMAND — the monthly demand table that drives per-SKU daily demand in the supply-chain projection — from two authoritative live sources so the forecast stays in sync with the company plan and the most recent mix of color purchases. Safe to rerun any time the budget or trailing mix shifts.

A single MERGE keyed on month upserts one row per month from the current month forward. Existing rows are updated in place; missing months are inserted. Past months are left untouched.

Depends on: - UTILITIES_DB.SUPPLY_CHAIN.INPUT_FORECAST_DEMAND (target) - UTILITIES_DB.FINANCE.BUDGET_CURRENT (future-month volume) - RAW_DB.SHOPIFY.ORDERS (current-month MTD volume + trailing-90-day color mix)

---

Target Columns

Column	Description
month	First day of the month being forecasted
daily_units_sold	Total daily demand across all four SKUs for that month
pcnt_aqua	Share of daily demand going to TCMOD1AQUA (decimal, e.g. 0.3870)
pcnt_lemon	Share going to TCMOD1LEMON
pcnt_white	Share going to TCMOD1WHITE
pcnt_lilac	Share going to TCMOD1LILAC

---

CTEs

color_mix

Source: RAW_DB.SHOPIFY.ORDERS

Trailing-90-day SKU split. Flattens the line_items variant, filters to non-cancelled, non-test orders with created_at >= dateadd(day, -90, current_date), and restricts to the four Tin Can SKUs. Returns a single row with pcnt_aqua / pcnt_lemon / pcnt_white / pcnt_lilac, each rounded to four decimals. This mix is applied uniformly to every month written.

mtd_actual

Source: RAW_DB.SHOPIFY.ORDERS

Month-to-date actuals for the current month. Sums units from line_items across the four SKUs where created_at is on or after the first of the current month and strictly before today (i.e., completed days only). Returns mtd_units and completed_days = day(current_date) - 1.

budget_monthly

Source: UTILITIES_DB.FINANCE.BUDGET_CURRENT

Pulls units_sold from the NON_FINANCIAL row for the current month and every future month in the active budget version, alongside days_in_month = day(last_day(month)) for the divisor.

current_month_demand

Left-joins mtd_actual to budget_monthly on the current month and computes daily_units_sold as:

coalesce(
    mtd_units / completed_days,          -- preferred: MTD actual pace
    units_sold / days_in_month           -- fallback: budget
)

The MTD branch is skipped if completed_days = 0 (script run on the 1st) or if there are no qualifying Shopify orders yet, in which case the budget fallback kicks in.

future_month_demand

For every month strictly after the current month, computes daily_units_sold = round(units_sold / days_in_month) from budget_monthly.

monthly_demand

UNION ALL of the current-month and future-month CTEs — one row per month with daily_units_sold.

The final select cross-joins monthly_demand with color_mix to produce the MERGE source: one row per month with volume + the shared trailing-90-day SKU shares.

---

Notes

• Volume logic differs by month: current month uses Shopify MTD pace (what's actually happening), future months use the budget plan. This keeps the in-progress month grounded in reality while letting future months reflect the planned ramp.
• Color mix is a single trailing-90-day snapshot applied to every row — the query does not forecast mix shifts over time. Rerun the script when the mix drifts.
• NON_FINANCIAL row requirement: BUDGET_CURRENT must have a NON_FINANCIAL row per month for future-month volume to resolve. If a future month has no NON_FINANCIAL row, units_sold will be null, daily_units_sold will be null, and the where dem.daily_units_sold is not null filter drops that month from the MERGE source — it will be left untouched rather than written with nulls (which would violate the table's NOT NULL constraints).
• Past months are never touched — the where clauses on budget_monthly and future_month_demand restrict to month >= date_trunc('month', current_date).
• Idempotent — rerunning produces the same result given the same source data.
• Rounding: pcnt_* rounded to 4 decimals; daily_units_sold rounded to the nearest integer. Minor sub-percent drift in the sum of the four pcnt_* columns is expected and accepted.