Last active
May 12, 2022 21:36
-
-
Save devanmoylan/90a2cb849b54f3a3acd6cb8651b3d660 to your computer and use it in GitHub Desktop.
[WIP] Transmetro and fashionthing allocated purchase order line query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- TODOS: | |
-- filter out nike | |
-- filter out unpublished | |
-- improve allocated po number splitting | |
-- update item type data source to be transmetro | |
-- NOTES: | |
-- money: cost and price values are in lowest unit for the currency (e.g. cents for USD) | |
-- update date: a revision to update date does not necessarily mean a relevant attribute changed; but instead something on the data source changed | |
SELECT | |
po_line.* | |
FROM ( | |
SELECT | |
---- po line attributes | |
-- sku | |
tm_as.sku_id AS sku_id, | |
tm_as.units_allocated AS quantity, | |
tm_as.units_allocated * ft_mb.unit_cost_value AS total_cost, | |
ft_os.vendor_part_number AS vendor_part_number, | |
ft_os.universal_product_code AS universal_product_code, | |
ft_os.european_article_number AS european_article_number, | |
'Ea' AS uom, | |
-- buy | |
CASE WHEN tm_mb.cancelled_at IS NULL THEN false ELSE true END AS cancel_flag, | |
tm_mb.brand_sku AS brand_sku, | |
ft_mb.commodity_code AS commodity_code, | |
ft_mb.unit_cost_value AS cost, | |
ft_mb.initial_retail_price_value AS initial_retail_price_value, | |
-- purchase order | |
CONCAT(SUBSTR(ft_po.oracle_purchase_order_number, 1, 8), '-', tm_h.code) AS po_number, | |
-- item type | |
ft_it.name AS class, | |
ft_it_department.name AS department, | |
ft_it_division.name AS division, | |
ft_it_mi.name AS merch_intent, | |
ft_it_bl.name AS gender_intent, | |
-- style | |
ft_ft_sv.price_override_value AS price_value, | |
ft_ft_s.retail_price_value AS retail_price_value, | |
ft_ft_sv.size_spectrum AS size_spectrum, | |
ft_ft_sv.primary_client_focus AS primary_client_focus, | |
JSON_EXTRACT_SCALAR(ft_ft_sv.characteristics, '$["seasonal_intent"]') AS seasonal_intent, | |
ft_ft_sv.business_group AS business_group, | |
---- create and update dates | |
-- sku | |
tm_as.created_at as tm_allocated_sku_create_date, | |
tm_as.updated_at as tm_allocated_sku_update_date, | |
tm_os.created_at as tm_ordered_sku_create_date, | |
tm_os.updated_at as tm_ordered_sku_update_date, | |
ft_os.created_at as ft_ordered_sku_create_date, | |
ft_os.updated_at as ft_ordered_sku_update_date, | |
-- merch buy | |
tm_mb.created_at as tm_merch_buy_create_date, | |
tm_mb.updated_at as tm_merch_buy_update_date, | |
ft_mb.created_at as ft_merch_buy_create_date, | |
ft_mb.updated_at as ft_merch_buy_update_date, | |
-- purchase order | |
tm_po.created_at as tm_purchase_order_create_date, | |
tm_po.updated_at as tm_purchase_order_update_date, | |
ft_po.created_at as ft_purchase_order_create_date, | |
ft_po.updated_at as ft_purchase_order_update_date, | |
-- style | |
ft_s.created_at as ft_style_create_date, | |
ft_s.updated_at as ft_style_update_date, | |
ft_ft_s.created_at as ft_ft_style_create_date, | |
ft_ft_s.updated_at as ft_ft_style_update_date, | |
-- style variant | |
ft_sv.created_at as ft_style_variant_create_date, | |
ft_sv.updated_at as ft_style_variant_update_date, | |
-- item type | |
ft_it.created_at as ft_item_type_create_date, | |
ft_it.updated_at as ft_item_type_update_date | |
---- data sources | |
-- subject: allocated po line | |
FROM hive.transmetropolitan_production.allocated_skus tm_as | |
-- hizzy attributes | |
INNER JOIN hive.transmetropolitan_production.buy_hizzy_allocations tm_bha ON tm_bha.id = tm_as.buy_hizzy_allocation_id | |
INNER JOIN hive.transmetropolitan_production.hizzies tm_h ON tm_h.id = tm_bha.hizzy_id | |
-- purchase attributes | |
INNER JOIN hive.transmetropolitan_production.ordered_skus tm_os ON tm_os.id = tm_as.ordered_sku_id | |
INNER JOIN hive.fashionthing_production.ft_ordered_skus ft_os ON ft_os.ordered_sku_id = tm_os.id | |
INNER JOIN hive.transmetropolitan_production.merch_buys tm_mb ON tm_mb.id = tm_os.merch_buy_id | |
INNER JOIN hive.fashionthing_production.ft_merch_buys ft_mb ON ft_mb.merch_buy_id = tm_mb.id | |
INNER JOIN hive.transmetropolitan_production.purchase_orders tm_po ON tm_po.id = tm_mb.purchase_order_id | |
INNER JOIN hive.fashionthing_production.ft_purchase_orders ft_po ON ft_po.purchase_order_id = tm_po.id | |
-- styles attributes | |
INNER JOIN hive.fashionthing_production.styles ft_s ON ft_s.id = tm_mb.style_id | |
INNER JOIN hive.fashionthing_production.ft_styles ft_ft_s ON ft_ft_s.style_id = ft_s.id | |
INNER JOIN hive.fashionthing_production.style_variants ft_sv ON ft_sv.id = tm_mb.style_variant_id | |
INNER JOIN hive.fashionthing_production.ft_style_variants ft_ft_sv ON ft_ft_sv.style_variant_id = ft_sv.id | |
-- item type attributes | |
INNER JOIN hive.fashionthing_production.item_types ft_it ON ft_it.id = ft_s.item_type_id | |
INNER JOIN hive.fashionthing_production.ft_item_types ft_ft_it ON ft_ft_it.item_type_id = ft_it.id | |
INNER JOIN hive.fashionthing_production.item_types ft_it_department ON ft_it_department.id = ft_it.parent_id | |
INNER JOIN hive.fashionthing_production.item_types ft_it_division ON ft_it_division.id = ft_it_department.parent_id | |
INNER JOIN hive.fashionthing_production.item_types ft_it_bl ON ft_it_bl.id = ft_it_division.parent_id | |
INNER JOIN hive.fashionthing_production.item_types ft_it_mi ON ft_it_mi.id = ft_it_bl.parent_id | |
---- filtering | |
WHERE | |
tm_po.start_ship_on >= DATE '2021-08-04' | |
) as po_line |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Copy and paste into https://yanagishima.daylight.stitchfix.com/#datasource=prod-presto&engine=presto&tab=treeview for testing.