Page Comparison

-- Generate a date range of the prior starting 12 months
WITH date_range AS (
  SELECT
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AS start_date,
    CURRENT_DATE() AS end_date
),
months as (
    SELECT DATE_TRUNC(DATE_ADD(start_date, INTERVAL n MONTH), MONTH) as month
    FROM date_range, UNNEST(GENERATE_ARRAY(0, DATE_DIFF(end_date, start_date, MONTH))) AS n
),
-- for each item, create the base table with the month
item_dc_months_base as (
  select distinct month, merchant_item_id, distribution_center_code
  from months
  LEFT JOIN `ultracart_dw.uc_item_inventory_history` iih on 1=1
  group by merchant_item_id, distribution_center_code, month
),
-- for each item, figure out the most recent history record before the start of the month
item_dc_months as (
  select item_dc_months_base.month, item_dc_months_base.merchant_item_id, item_dc_months_base.distribution_center_code, max(history_dts) as most_recent_history_dts
  from item_dc_months_base
  LEFT JOIN `ultracart_dw.uc_item_inventory_history` iih on iih.history_dts <= item_dc_months_base.month and iih.merchant_item_id = item_dc_months_base.merchant_item_id and iih.distribution_center_code = item_dc_months_base.distribution_center_code
  group by merchant_item_id, distribution_center_code, month
),
-- for each item, figure out the inventory level associated with the most recent history record before the start of the montheach item, figure out the inventory level associated with the most recent history record before the start of the month
item_dc_month_level as (
  select item_dc_months.* except (most_recent_history_dts), iih.after_inventory_level as inventory_level
  from item_dc_months
  LEFT JOIN `ultracart_dw.uc_item_inventory_history` iih on iih.merchant_item_id = item_dc_months.merchant_item_id
  and iih.distribution_center_code = item_dc_months.distribution_center_code
  and iih.history_dts = item_dc_months.most_recent_history_dts
  where item_dc_months.merchant_item_id is not null
),
-- create a table that has the first inventory level ever seen to back fill nulls
item_dc_first_level_base as (
  select merchant_item_id, distribution_center_code, inventory_level, ROW_NUMBER() OVER (PARTITION BY merchant_item_id, distribution_center_code ORDER BY month ASC) AS finish_rank 
  from item_dc_month_level 
  where inventory_level is not null
  union all
  select merchant_item_id, distribution_center_code, inventory_level, 999 as finish_rank
  from `ultracart_dw.uc_items`  
  CROSS JOIN UNNEST(shipping.distribution_centers) dc 
  where dc.handles is true
),
item_dc_first_level_base2 as (
  select * except(finish_rank), 
  ROW_NUMBER() OVER (PARTITION BY merchant_item_id, distribution_center_code ORDER BY finish_rank ASC) as finish_rank2
  from item_dc_first_level_base
),
item_dc_first_level as (
  select * except (finish_rank2) from item_dc_first_level_base2 where finish_rank2 = 1
),
-- backfill item_dc_month_level
item_dc_month_level_backfill as (
  select item_dc_monthsmonth_level.* except (inventory_level), coalesce(mostitem_dc_recentmonth_history_dts), iih.after_level.inventory_level, item_dc_first_level.inventory_level) as inventory_level
  from item_dc_monthsmonth_level
  LEFTleft JOINjoin `ultracartitem_dw.ucdc_itemfirst_inventory_history`level iih on iihitem_dc_first_level.merchant_item_id = item_dc_month_monthslevel.merchant_item_id   and iih.distribution_center_code = item_dc_first_monthslevel.distribution_center_code   and iih.history_dts = item_dc_months.most_recent_history_dts
  where item_dc_months.merchant_item_id is not null
month_level.distribution_center_code
),
-- figure out which DCs for a particular item had ANY activity in the time period
active_item_dc as (
  select merchant_item_id, distribution_center_code, cast(max(inventory_level) as int) as max_inventory_level 
  from item_dc_month_level_backfill
  group by merchant_item_id, distribution_center_code
  having max_inventory_level > 0
),
-- filter down to active items and bring in the cogs.  This is not stored historically so it's the active value as configured on the item currently
items_with_cogs as (
  select item_dc_month_level_backfill.*, 
  COALESCE((
    select cogs from UNNEST(shipping.distribution_centers) dc where dc.distribution_center_code = item_dc_month_level_backfill.distribution_center_code
  ), i.pricing.cogs) as cogs
  from item_dc_month_level_backfill
  right join active_item_dc on active_item_dc.merchant_item_id = item_dc_month_level_backfill.merchant_item_id and active_item_dc.distribution_center_code = item_dc_month_level_backfill.distribution_center_code
  left join `ultracart_dw.uc_items` i on i.merchant_item_id = item_dc_month_level_backfill.merchant_item_id
)
-- Bring it all together and calculate the value of the inventory
select *, inventory_level * cogs as inventory_value
from items_with_cogs
order by month, merchant_item_id, distribution_center_code, month