v1.00001.sql

with MERCHANT AS (
    SELECT 
        m.OS_MERCHANT MERCHANT,
        ROW_NUMBER() OVER(ORDER BY m.dt_closed ASC) AS Rownum,
        c.OS_SOURCE_SCHEMA SOURCE_SCHEMA,
        m.OS_STORE_ID STORE_ID,
        m.os_store_url STORE_URL
    FROM ANALYTICS.PROD_INTAKE_DB_PUBLIC.CONNECTOR c
        JOIN analytics.main.os_merchants m ON m.OS_STORE_ID = c.OS_STORE_ID
    WHERE OS_CONNECTOR_TYPE in ('shopify_bulk')
        AND is_closed
    ORDER BY rownum
),
VARIANT_METAFIELDS as (
    SELECT m.store_id,
        mf.source_schema,
        product_variant_id as variant_id,
        namespace,
        key,
        value
    FROM
        PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.SHOPIFY_PRODUCT_VARIANT_METAFIELD mf
        inner join merchant m on m.source_schema = mf.source_schema
    QUALIFY row_number() over ( PARTITION by mf.source_schema, mf.product_variant_id,mf.key ORDER BY mf.updated_at DESC) = 1
),
PRODUCT_METAFIELDS as (
    SELECT m.store_id,
        mf.source_schema,
        product_id,
        namespace,
        key,
        value
    FROM 
        PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.shopify_product_metafield mf
        inner join merchant m on m.source_schema = mf.source_schema
    QUALIFY row_number() over ( PARTITION by mf.source_schema, mf.product_id,mf.key ORDER BY mf.updated_at DESC) = 1
),
CATALOG as (
    SELECT m.merchant,
        m.source_schema,
        m.store_id,
        p.title,
        p.product_type,
        p.id as product_id,
        v.id as variant_id,
        i.id as item_id,
        v.sku,
        p.status,
        v.barcode,
        i.unit_cost_amount as unit_cost,
        v.tax_code,
        c.hts_code,
        i.requires_shipping,
        c.category_full_name,
        c.category_name,
        product_holiday.value as product_holiday,
        product_season.value as product_season,
        product_gender.value as product_gender,
        product_age.value as product_age,
        product_supplier.value as product_supplier,
        product_fulfillment_type.value as product_fulfillment_type,
        product_fulfillment_partner.value as product_fulfillment_partner,
        product_lead_time.value as product_lead_time,
        product_moq.value as product_moq,
        product_moq_notes.value as product_moq_notes,
        variant_moq.value as variant_moq,
        variant_moq_notes.value as variant_moq_notes
    FROM
        PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.SHOPIFY_PRODUCT_VARIANT v
        INNER JOIN PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.SHOPIFY_PRODUCT p on p.source_schema = v.source_schema and p.id = v.product_id
        INNER JOIN PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.SHOPIFY_INVENTORY_ITEM i on i.source_schema = v.source_schema and i.id = v.inventory_item_id
        INNER JOIN merchant m on m.source_schema = v.source_schema
        INNER JOIN (select v.shopify_external_id as variant_id, m.source_schema, v.harmonized_system_code as hts_code, p.category_full_name, p.category_name from CATALOG_SERVICE_PROD.AURORA_POSTGRES_PUBLIC.VARIANT v inner join CATALOG_SERVICE_PROD.AURORA_POSTGRES_PUBLIC.PRODUCT p on p.id = v.product_id inner join CATALOG_SERVICE_PROD.AURORA_POSTGRES_PUBLIC.STORE_PRODUCT sp ON sp.PRODUCT_ID = v.product_id inner join merchant m on m.store_id = sp.store_id WHERE v.deleted_at is null and v._fivetran_deleted <> true ) c on c.source_schema = v.source_schema and c.variant_id = v.id
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('holiday')) product_holiday on product_holiday.source_schema = v.SOURCE_SCHEMA and product_holiday.product_id = v.product_id
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('season')) product_season on product_season.source_schema = v.SOURCE_SCHEMA and product_season.product_id = v.product_id
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('gender')) product_gender on product_gender.source_schema = v.SOURCE_SCHEMA and product_gender.product_id = v.product_id
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('age')) product_age on product_age.source_schema = v.SOURCE_SCHEMA and product_age.product_id = v.product_id    
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('supplier')) product_supplier on product_supplier.source_schema = v.SOURCE_SCHEMA and product_supplier.product_id = v.product_id    
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('fulfillment_partner')) product_fulfillment_partner on product_fulfillment_partner.source_schema = v.SOURCE_SCHEMA and product_fulfillment_partner.product_id = v.product_id
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('fulfillment_type')) product_fulfillment_type on product_fulfillment_type.source_schema = v.SOURCE_SCHEMA and product_fulfillment_type.product_id = v.product_id
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('lead_time')) product_lead_time on product_lead_time.source_schema = v.SOURCE_SCHEMA and product_lead_time.product_id = v.product_id    
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('moq')) product_moq on product_moq.source_schema = v.SOURCE_SCHEMA and product_moq.product_id = v.product_id    
        FULL JOIN (SELECT * from product_metafields where namespace in ('open_store_internal') AND key in ('moq_notes')) product_moq_notes on product_moq_notes.source_schema = v.SOURCE_SCHEMA and product_moq_notes.product_id = v.product_id    
        FULL JOIN (SELECT * from variant_metafields where namespace in ('open_store_internal') AND key in ('moq')) variant_moq on variant_moq.source_schema = v.SOURCE_SCHEMA and variant_moq.variant_id = v.id
        FULL JOIN (SELECT * from variant_metafields where namespace in ('open_store_internal') AND key in ('moq_notes')) variant_moq_notes on variant_moq_notes.source_schema = v.SOURCE_SCHEMA and variant_moq_notes.variant_id = v.id
    WHERE 
        m.store_id NOT IN ('e5d14958-d920-4bda-987f-300c78cd22ec','a8e4e5ef-12bd-4e12-a821-f35b023f6ac2')
        AND NOT (LEFT(V.SKU, 4) ilike '%SKU-%')
    ORDER BY SKU
), orders as (
SELECT 
    m.merchant,
    l.order_id,
    o.created_at,
    o._OS_SOURCE_ROW_CONTENTS ['CONTENT'] ['cancelledAt']::datetime AS canceled_at,
    l.sku,
    (l.ORIGINAL_UNIT_PRICE_SHOPIFY_AMOUNT - l.TOTAL_DISCOUNT_SHOPIFY_AMOUNT) as price,
    l._OS_SOURCE_ROW_CONTENTS ['CONTENT'] ['unfulfilledQuantity']::number AS unfulfilled_qty,
    f.fulfillment_partner,
    f.fulfillment_partner_email,
    f.sla_days,
    DATEADD(DAY, CASE WHEN DAYOFWEEK(o.created_at) IN (6) THEN 2 WHEN DAYOFWEEK(o.created_at) IN (7) THEN 1  ELSE 0 END, o.created_at) AS created_at_wd,
    CASE WHEN DATEDIFF(day, created_at_wd, GETDATE()) > SLA_DAYS THEN TRUE ELSE FALSE END AS missed_sla,
    CASE WHEN missed_sla then DATEDIFF(day, created_at_wd, GETDATE()) else 0 END AS days_past_sla
FROM 
    (SELECT *, _OS_SOURCE_ROW_CONTENTS ['CONTENT'] ['sku']::TEXT AS sku,_OS_SOURCE_ROW_CONTENTS ['CONTENT'] ['requiresShipping']::boolean as requires_shipping FROM PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.SHOPIFY_ORDER_LINE) l
    INNER JOIN PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.SHOPIFY_ORDER o ON o.source_schema = l.source_schema AND o.id = l.order_id
    INNER JOIN (SELECT SOURCE_SCHEMA,SKU,PRODUCT_FULFILLMENT_TYPE, PRODUCT_FULFILLMENT_PARTNER FROM CATALOG QUALIFY row_number() OVER ( PARTITION by SOURCE_SCHEMA, SKU ORDER BY PRODUCT_FULFILLMENT_PARTNER ASC,PRODUCT_FULFILLMENT_TYPE ASC) = 1) c ON c.source_schema = l.source_schema AND c.sku = l.sku
    FULL JOIN analytics.google_sheets.scm_fulfillment_partners f ON f.source_schema = l.source_schema and f.fulfillment_partner = c.product_fulfillment_partner
    FULL JOIN PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.SHOPIFY_FULFILLMENT_LINE_ITEM fl ON fl.line_item_id = l.id AND fl.source_schema = l.source_schema
    FULL join PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.SHOPIFY_ORDER_LINE_REFUND olr on olr._os_source_schema = l.source_schema and olr.order_line_id = l.id
    INNER JOIN merchant m on m.source_schema = l.source_schema
WHERE 
    YEAR(CREATED_AT) > 2022
    AND NOT (LEFT(L.SKU, 4) ilike '%SKU-%')
    AND UNFULFILLED_QTY > 0
    AND l.requires_shipping
    AND canceled_at is null
    AND OLR.QUANTITY IS NULL
ORDER BY order_id
),


score_1 as (
select 
  merchant,
  COUNT(distinct order_id) as orders,
  sum(all unfulfilled_qty) as unfulfilled_units,
  count(all sku) as unfulfilled_skus,
  sum(all price) as total_value
from orders
where days_past_sla > 1
and missed_sla
group BY 
1),
score_2 as (
select 
  merchant,
  COUNT(distinct order_id) as orders,
  sum(all unfulfilled_qty) as unfulfilled_units,
  count(all sku) as unfulfilled_skus,
  sum(all price) as total_value
from orders
where days_past_sla <= 1
AND days_past_sla <> 0
and missed_sla
group BY 
1),
score_3 as (
select 
  merchant,
  COUNT(distinct order_id) as orders,
  sum(all unfulfilled_qty) as unfulfilled_units,
  count(all sku) as unfulfilled_skus,
  sum(all price) as total_value
from orders
where days_past_sla = 0
and not missed_sla
group BY 
1)

select m.merchant,
    f.assistant_email as assignee_1,
    f.assistant_helper_email as assignee_2,
s1.orders as score_1_unfulfilled_order_count,
s1.unfulfilled_skus as score_1_unfulfilled_skus,
s1.total_value as score_1_unfulfilled_value,
s2.orders as score_2_unfulfilled_order_count,
s2.unfulfilled_skus as score_2_unfulfilled_skus,
s2.total_value as score_2_unfulfilled_value,
s3.orders as score_3_unfulfilled_order_count,
s3.unfulfilled_skus as score_3_unfulfilled_skus,
s3.total_value as score_3_unfulfilled_value,
case when (score_1_unfulfilled_order_count is not null or score_2_unfulfilled_order_count is not null) then true else false end as NEEDS_ALERT
from merchant m
full join score_1  s1 on s1.merchant = m.merchant 
full join score_2  s2 on s2.merchant = m.merchant 
full join score_3  s3 on s3.merchant = m.merchant 
full join (select * from analytics.google_sheets.scm_fulfillment_partners where store_id not in ('e5d14958-d920-4bda-987f-300c78cd22ec','a8e4e5ef-12bd-4e12-a821-f35b023f6ac2') QUALIFY row_number() OVER ( PARTITION by SOURCE_SCHEMA,assistant_email, assistant_helper_email ORDER BY id ASC) = 1) f on f.source_schema = m.source_schema
where m.store_id not in ('e5d14958-d920-4bda-987f-300c78cd22ec','a8e4e5ef-12bd-4e12-a821-f35b023f6ac2')
Order by m.rownum