Item and Inventory Queries

Overview

These queries read uc_items and uc_item_inventory_history. Items carry their distribution center settings as a nested array, so the per-warehouse SKU and cost of goods live one UNNEST(shipping.distribution_centers) below the item row.

Inventory Value at Start Of Each Month

Reconstructs the inventory level of each active item at each distribution center at the start of every month for the last year, then multiplies it by cost of goods to value the inventory. Cost of goods is not stored historically, so the valuation uses whatever COGS the item carries today.

-- 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 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_month_level.* except (inventory_level), coalesce(item_dc_month_level.inventory_level, item_dc_first_level.inventory_level) as inventory_level
  from item_dc_month_level
  left join item_dc_first_level on item_dc_first_level.merchant_item_id = item_dc_month_level.merchant_item_id and item_dc_first_level.distribution_center_code = item_dc_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 merchant_item_id, distribution_center_code, month

SKUs Sent to 3PL

Maps each non-kit item to the SKU your warehouse actually receives for it at each distribution center, falling back to the item ID when no distribution-center SKU is set.

with sku_map_rows as (
  SELECT merchant_item_id, dc.distribution_center_code, coalesce(sku, merchant_item_id) as wms_receives_sku
  FROM `ultracart_dw.uc_items` 
  CROSS JOIN UNNEST(shipping.distribution_centers) dc
  where kit is false
)
select * from sku_map_rows order by merchant_item_id, distribution_center_code, wms_receives_sku

StoreFront Hostname Per Item

Lists the StoreFront host each item is assigned to. An item can be assigned to more than one StoreFront, so content.assignments is an array: the query flattens it with CROSS JOIN UNNEST and returns one row per item and StoreFront pairing.

SELECT
  merchant_item_id,
  assignment.host AS storefront_host_name
FROM
  `ultracart_dw.uc_items`
CROSS JOIN UNNEST(content.assignments) AS assignment

Each assignment also carries group_oid, group_path, url_part, sort_order, and default_assignment if you need the page the item sits on rather than just the host.