1.23.sql

with MERCHANT AS (
    SELECT
        c.OS_SOURCE_SCHEMA AS SOURCE_SCHEMA,
        om.OS_MERCHANT AS MERCHANT,
        om.OS_STORE_ID AS STORE_ID,
        om.os_store_url AS STORE_URL
    FROM
        ANALYTICS.PROD_INTAKE_DB_PUBLIC.CONNECTOR c
        JOIN analytics.main.os_merchants om ON om.OS_STORE_ID = c.OS_STORE_ID
    WHERE
        OS_CONNECTOR_TYPE = 'shopify_bulk'
        AND is_closed
        AND om.dt_closed is not null
    ORDER BY
        merchant
),
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,
        c.is_bundle,
        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,
        product_open_store_notes.value as product_open_store_notes,
        variant_open_store_notes.value as variant_open_store_notes,
        CASE WHEN product_open_store_notes ilike '%product_category=false%' THEN TRUE ELSE FALSE END as exclude_product_category,
        CASE WHEN product_open_store_notes ilike '%product_supplier=false%' THEN TRUE ELSE FALSE END as exclude_product_supplier,
        CASE WHEN product_open_store_notes ilike '%product_moq=false%' THEN TRUE ELSE FALSE END as exclude_product_moq,
        CASE WHEN product_open_store_notes ilike '%product_lead_time=false%' THEN TRUE ELSE FALSE END as exclude_product_lead_time,
        CASE WHEN product_open_store_notes ilike '%duplicate_sku=false%' THEN TRUE ELSE FALSE END as exclude_variant_duplicate_sku,
        CASE WHEN product_open_store_notes ilike '%variant_moq=false%' THEN TRUE ELSE FALSE END as exclude_variant_moq,
        CASE WHEN product_fulfillment_type ilike '%dropship%' THEN TRUE ELSE FALSE END as exclude_dropship,
        CASE WHEN upper(v.sku) REGEXP '^SKU-[0-9]{5,}$' THEN TRUE ELSE FALSE END as exclude_collective_skus
    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 (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,is_bundle 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
        INNER JOIN merchant m on m.source_schema = v.source_schema
        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 product_metafields where namespace in ('open_store_internal') AND key in ('open_store_notes')) product_open_store_notes on product_open_store_notes.source_schema = v.SOURCE_SCHEMA and product_open_store_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
        FULL JOIN (SELECT * from variant_metafields where namespace in ('open_store_internal') AND key in ('open_store_notes')) variant_open_store_notes on variant_open_store_notes.source_schema = v.SOURCE_SCHEMA and variant_open_store_notes.variant_id = v.id

    WHERE 
        m.store_id NOT IN ('e5d14958-d920-4bda-987f-300c78cd22ec','a8e4e5ef-12bd-4e12-a821-f35b023f6ac2')
        AND NOT exclude_collective_skus
    ORDER BY SKU
)
 , variants as (
    select merchant,
    source_schema,
    count(variant_id) as total_variants
    from catalog
    group by 1,2
 )
,missing_tax as (
    select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (tax_code is null or trim(tax_code) = '')
    group by 1
),
missing_skus as (
    select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (sku is null or trim(sku) = '')
    group by 1
),
missing_cost as (
    select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (unit_cost is null or trim(unit_cost) = '')
    group by 1
),
missing_hts as (
    select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (hts_code is null or trim(hts_code) = '')
    AND requires_shipping
        group by 1

),
missing_category as (
    select merchant, count(distinct variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (category_full_name is null or trim(category_full_name) = '')
    AND UPPER(title) NOT like 'ROUTE%'
    and UPPER(title) not like 'ONWARD%'
    AND NOT exclude_product_category
    group by 1
),missing_barcode as (
    select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (barcode is null or trim(barcode) = '')
    AND UPPER(title) NOT like 'ROUTE%'
    and UPPER(title) not like 'ONWARD%'
    group by merchant
),missing_product_holiday as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_holiday is null or trim(product_holiday) = '')
    AND requires_shipping
    group by 1
),missing_product_season as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_season is null or trim(product_season) = '')
    AND requires_shipping
    group by 1
),missing_product_gender as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_gender is null or trim(product_gender) = '')
    AND requires_shipping
    group by 1
),missing_product_age as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_age is null or trim(product_age) = '')
    AND requires_shipping
    group by 1
),missing_product_supplier as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_supplier is null or trim(product_supplier) = '')
    AND requires_shipping
    AND NOT exclude_product_supplier
    group by 1
),
missing_product_fulfillment_type as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_fulfillment_type is null or trim(product_fulfillment_type) = '')
    AND requires_shipping
    group by 1
),
missing_product_lead_time as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_lead_time is null or trim(product_lead_time) = '')
    AND NOT exclude_dropship
    AND NOT exclude_product_lead_time
    AND requires_shipping
    AND not is_bundle
    group by 1
),
missing_product_moq as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_moq is null or trim(product_moq) = '')
    AND (variant_moq is null or trim(variant_moq) = '')
    AND NOT exclude_dropship
    AND NOT exclude_product_moq
    AND requires_shipping
    AND not is_bundle
    group by 1
),
missing_variant_moq as (
    select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (variant_moq is null or trim(variant_moq) = '')
    AND (product_moq is null or trim(product_moq) = '')
    AND NOT exclude_dropship
    AND NOT exclude_variant_moq
    AND requires_shipping
    AND not is_bundle
    group by 1
),
missing_product_fulfillment_partner as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_fulfillment_partner is null or trim(product_fulfillment_partner) = '')
    AND requires_shipping
    group by 1
),
duplicate_skus AS (
    SELECT
        sku,
        count(sku) AS _COUNT
    FROM catalog
    where 
    sku <> ''
    AND NOT exclude_variant_duplicate_sku
    GROUP BY
        1
    ORDER BY
        1
),
dup_skus AS (
    SELECT
        merchant,
        count(DISTINCT c.variant_id) AS TOTAL_DUPLICATES
    FROM
        duplicate_skus d
        inner join (select merchant, sku,variant_id from catalog) c on c.sku = d.sku
    WHERE
        d._COUNT > 1
    GROUP BY
        1
    ORDER BY
        1
),
duplicate_barcodes AS (
    SELECT
        barcode,
        count(barcode) AS _COUNT
    FROM
        catalog
    WHERE 
      barcode <> ''
    GROUP BY
        1
    ORDER BY
        1
),

dup_barcodes AS (
    SELECT
        merchant,
        count(DISTINCT c.variant_id) AS TOTAL_DUPLICATES
    FROM
        duplicate_barcodes d
        inner join (select merchant, barcode,variant_id from catalog) c on c.barcode = d.barcode
    WHERE
        d._COUNT > 1
    GROUP BY
        1
    ORDER BY
        1
)
select 
    c.merchant,
    f.assistant_email as assignee_1,
    f.assistant_helper_email as assignee_2,
    c.total_variants,
    mt.total_missing as missing_tax_codes_count,
    mc.total_missing as missing_unit_cost_count,
    mh.total_missing as missing_hts_codes_count,
    mct.total_missing as missing_categories_count,
    mb.total_missing as missing_barcodes_count,
    ms.total_missing as missing_skus_count,
    ph.total_missing as missing_product_holiday_count,
    ps.total_missing as missing_product_season_count,
    pg.total_missing as missing_product_gender_count,
    pa.total_missing as missing_product_age_count,
    db.total_duplicates as duplicate_barcodes_count,
    ds.TOTAL_DUPLICAtes as duplicate_skus_count,
    psp.total_missing as missing_product_supplier_count,
    pft.total_missing as  missing_product_fulfillment_type_count,
    plt.total_missing as  missing_product_lead_time_count,
    pmq.total_missing as  missing_product_moq_count,
    vmq.total_missing as  missing_variant_moq_count,
    mpfp.total_missing as missing_product_fulfillment_partner_count,
    (mt.total_missing / c.total_variants) as missing_tax_codes_percent,
    (mc.total_missing / c.total_variants)  as missing_unit_cost_percent,
    (mh.total_missing / c.total_variants)  as missing_hts_codes_percent,
    (mct.total_missing / c.total_variants) as missing_categories_percent,
    (mb.total_missing / c.total_variants)  as missing_barcodes_percent,
    (ms.total_missing / c.total_variants)  as missing_skus_percent,
    (ph.total_missing / c.total_variants)  as missing_product_holiday_percent,
    (ps.total_missing / c.total_variants)  as missing_product_season_percent,
    (pg.total_missing / c.total_variants)  as missing_product_gender_percent,
    (pa.total_missing / c.total_variants)  as missing_product_age_percent,
    (db.total_duplicates / c.total_variants) as duplicate_barcodes_percent,
    (ds.TOTAL_DUPLICATES / c.total_variants) as duplicate_skus_percent,
    (psp.total_missing /c.total_variants) as missing_product_supplier_percent,
    (pft.total_missing /c.total_variants) as  missing_product_fulfillment_type_percent,
    (plt.total_missing /c.total_variants) as  missing_product_lead_time_percent,
    (pmq.total_missing /c.total_variants) as  missing_product_moq_percent,
    (vmq.total_missing /c.total_variants) as  missing_variant_moq_percent,
    (mpfp.total_missing /c.total_variants) as missing_product_fulfillment_partner_percent,
    CASE
        WHEN missing_tax_codes_percent <= 0.01
        AND missing_tax_codes_percent > 0 THEN 4
        WHEN missing_tax_codes_percent > 0.01
        AND missing_tax_codes_percent < 0.25 THEN 3
        WHEN missing_tax_codes_percent >= 0.25
        AND missing_tax_codes_percent < 0.50 THEN 2
        WHEN missing_tax_codes_percent >= 0.5 THEN 1
        WHEN missing_tax_codes_percent IS NULL THEN 5
    END AS MISSING_TAX_CODES_SCORE,
    CASE
        WHEN missing_unit_cost_percent <= 0.01
        AND missing_unit_cost_percent > 0 THEN 4
        WHEN missing_unit_cost_percent > 0.01
        AND missing_unit_cost_percent < 0.25 THEN 3
        WHEN missing_unit_cost_percent >= 0.25
        AND missing_unit_cost_percent < 0.50 THEN 2
        WHEN missing_unit_cost_percent >= 0.5 THEN 1
        WHEN missing_unit_cost_percent IS NULL THEN 5
    END AS MISSING_UNIT_COST_SCORE,
    CASE
        WHEN missing_hts_codes_percent <= 0.01
        AND missing_hts_codes_percent > 0 THEN 4
        WHEN missing_hts_codes_percent > 0.01
        AND missing_hts_codes_percent < 0.25 THEN 3
        WHEN missing_hts_codes_percent >= 0.25
        AND missing_hts_codes_percent < 0.50 THEN 2
        WHEN missing_hts_codes_percent >= 0.5 THEN 1
        WHEN missing_hts_codes_percent IS NULL THEN 5
    END AS MISSING_HTS_CODES_SCORE,
    CASE
        WHEN missing_barcodes_percent <= 0.01
        AND missing_barcodes_percent > 0 THEN 4
        WHEN missing_barcodes_percent > 0.01
        AND missing_barcodes_percent < 0.25 THEN 3
        WHEN missing_barcodes_percent >= 0.25
        AND missing_barcodes_percent < 0.50 THEN 2
        WHEN missing_barcodes_percent >= 0.5 THEN 1
        WHEN missing_barcodes_percent IS NULL THEN 5
    END AS missing_barcodes_score,
    CASE
        WHEN missing_categories_percent <= 0.01
        AND missing_categories_percent > 0 THEN 4
        WHEN missing_categories_percent > 0.01
        AND missing_categories_percent < 0.25 THEN 3
        WHEN missing_categories_percent >= 0.25
        AND missing_categories_percent < 0.50 THEN 2
        WHEN missing_categories_percent >= 0.5 THEN 1
        WHEN missing_categories_percent IS NULL THEN 5
    END AS MISSING_CATEGORIES_SCORE,
    CASE
        WHEN missing_skus_percent <= 0.01
        AND missing_skus_percent > 0 THEN 4
        WHEN missing_skus_percent > 0.01
        AND missing_skus_percent < 0.25 THEN 3
        WHEN missing_skus_percent >= 0.25
        AND missing_skus_percent < 0.50 THEN 2
        WHEN missing_skus_percent >= 0.5 THEN 1
        WHEN missing_skus_percent IS NULL THEN 5
    END AS MISSING_SKUS_SCORE,
    CASE
        WHEN duplicate_barcodes_percent <= 0.01
        AND duplicate_barcodes_percent > 0 THEN 4
        WHEN duplicate_barcodes_percent > 0.01
        AND duplicate_barcodes_percent < 0.25 THEN 3
        WHEN duplicate_barcodes_percent >= 0.25
        AND duplicate_barcodes_percent < 0.50 THEN 2
        WHEN duplicate_barcodes_percent >= 0.5 THEN 1
        WHEN duplicate_barcodes_percent IS NULL THEN 5
    END AS DUPLICATE_BARCODES_SCORE,
    CASE
        WHEN duplicate_skus_percent <= 0.01
        AND duplicate_skus_percent > 0 THEN 4
        WHEN duplicate_skus_percent > 0.01
        AND duplicate_skus_percent < 0.25 THEN 3
        WHEN duplicate_skus_percent >= 0.25
        AND duplicate_skus_percent < 0.50 THEN 2
        WHEN duplicate_skus_percent >= 0.5 THEN 1
        WHEN duplicate_skus_percent IS NULL THEN 5
    END AS DUPLICATE_SKUS_SCORE,
    CASE
        WHEN missing_product_holiday_percent <= 0.01
        AND missing_product_holiday_percent > 0 THEN 4
        WHEN missing_product_holiday_percent > 0.01
        AND missing_product_holiday_percent < 0.25 THEN 3
        WHEN missing_product_holiday_percent >= 0.25
        AND missing_product_holiday_percent < 0.50 THEN 2
        WHEN missing_product_holiday_percent >= 0.5 THEN 1
        WHEN missing_product_holiday_percent IS NULL THEN 5
    END AS missing_product_holiday_score,
    CASE
        WHEN missing_product_season_percent <= 0.01
        AND missing_product_season_percent > 0 THEN 4
        WHEN missing_product_season_percent > 0.01
        AND missing_product_season_percent < 0.25 THEN 3
        WHEN missing_product_season_percent >= 0.25
        AND missing_product_season_percent < 0.50 THEN 2
        WHEN missing_product_season_percent >= 0.5 THEN 1
        WHEN missing_product_season_percent IS NULL THEN 5
    END AS missing_product_season_score,
    CASE
        WHEN missing_product_gender_percent <= 0.01
        AND missing_product_gender_percent > 0 THEN 4
        WHEN missing_product_gender_percent > 0.01
        AND missing_product_gender_percent < 0.25 THEN 3
        WHEN missing_product_gender_percent >= 0.25
        AND missing_product_gender_percent < 0.50 THEN 2
        WHEN missing_product_gender_percent >= 0.5 THEN 1
        WHEN missing_product_gender_percent IS NULL THEN 5
    END AS missing_product_gender_score,
     CASE
        WHEN missing_product_age_percent <= 0.01
        AND missing_product_age_percent > 0 THEN 4
        WHEN missing_product_age_percent > 0.01
        AND missing_product_age_percent < 0.25 THEN 3
        WHEN missing_product_age_percent >= 0.25
        AND missing_product_age_percent < 0.50 THEN 2
        WHEN missing_product_age_percent >= 0.5 THEN 1
        WHEN missing_product_age_percent IS NULL THEN 5
    END AS missing_product_age_score,
         CASE
        WHEN missing_product_supplier_percent <= 0.01
        AND missing_product_supplier_percent > 0 THEN 4
        WHEN missing_product_supplier_percent > 0.01
        AND missing_product_supplier_percent < 0.25 THEN 3
        WHEN missing_product_supplier_percent >= 0.25
        AND missing_product_supplier_percent < 0.50 THEN 2
        WHEN missing_product_supplier_percent >= 0.5 THEN 1
        WHEN missing_product_supplier_percent IS NULL THEN 5
    END AS missing_product_supplier_score
    ,
         CASE
        WHEN missing_product_fulfillment_type_percent <= 0.01
        AND missing_product_fulfillment_type_percent > 0 THEN 4
        WHEN missing_product_fulfillment_type_percent > 0.01
        AND missing_product_fulfillment_type_percent < 0.25 THEN 3
        WHEN missing_product_fulfillment_type_percent >= 0.25
        AND missing_product_fulfillment_type_percent < 0.50 THEN 2
        WHEN missing_product_fulfillment_type_percent >= 0.5 THEN 1
        WHEN missing_product_fulfillment_type_percent IS NULL THEN 5
    END AS missing_product_fulfillment_type_score
    ,
         CASE
        WHEN missing_product_lead_time_percent <= 0.01
        AND missing_product_lead_time_percent > 0 THEN 4
        WHEN missing_product_lead_time_percent > 0.01
        AND missing_product_lead_time_percent < 0.25 THEN 3
        WHEN missing_product_lead_time_percent >= 0.25
        AND missing_product_lead_time_percent < 0.50 THEN 2
        WHEN missing_product_lead_time_percent >= 0.5 THEN 1
        WHEN missing_product_lead_time_percent IS NULL THEN 5
    END AS missing_product_lead_time_score
    ,        CASE
        WHEN missing_product_moq_percent <= 0.01
        AND missing_product_moq_percent > 0 THEN 4
        WHEN missing_product_moq_percent > 0.01
        AND missing_product_moq_percent < 0.25 THEN 3
        WHEN missing_product_moq_percent >= 0.25
        AND missing_product_moq_percent < 0.50 THEN 2
        WHEN missing_product_moq_percent >= 0.5 THEN 1
        WHEN missing_product_moq_percent IS NULL THEN 5
    END AS missing_product_moq_score
,        CASE
        WHEN missing_variant_moq_percent <= 0.01
        AND missing_variant_moq_percent > 0 THEN 4
        WHEN missing_variant_moq_percent > 0.01
        AND missing_variant_moq_percent < 0.25 THEN 3
        WHEN missing_variant_moq_percent >= 0.25
        AND missing_variant_moq_percent < 0.50 THEN 2
        WHEN missing_variant_moq_percent >= 0.5 THEN 1
        WHEN missing_variant_moq_percent IS NULL THEN 5
    END AS missing_variant_moq_score
,        CASE
        WHEN missing_product_fulfillment_partner_percent <= 0.01
        AND missing_product_fulfillment_partner_percent > 0 THEN 4
        WHEN missing_product_fulfillment_partner_percent > 0.01
        AND missing_product_fulfillment_partner_percent < 0.25 THEN 3
        WHEN missing_product_fulfillment_partner_percent >= 0.25
        AND missing_product_fulfillment_partner_percent < 0.50 THEN 2
        WHEN missing_product_fulfillment_partner_percent >= 0.5 THEN 1
        WHEN missing_product_fulfillment_partner_percent IS NULL THEN 5
    END AS missing_product_fulfillment_partner_score
from variants c
    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 = c.source_schema
    full join missing_tax mt on mt.merchant  = c.merchant
    full join missing_cost mc on mc.merchant = c.merchant
    full join missing_hts mh on mh.merchant  = c.merchant
    full join missing_skus ms on ms.merchant = c.merchant
    full join missing_category mct on mct.merchant = c.merchant
    full join missing_barcode mb on mb.merchant = c.merchant
    full join missing_product_holiday ph on ph.merchant = c.merchant
    full join missing_product_season ps on ps.merchant = c.merchant
    full join missing_product_gender pg on pg.merchant = c.merchant
    full join missing_product_age pa on pa.merchant = c.merchant
    full join dup_barcodes db on db.merchant = c.merchant
    full join dup_skus ds on ds.merchant = c.merchant
    full join missing_product_supplier psp on psp.merchant = c.merchant
    full join missing_product_fulfillment_type  pft on pft.merchant = c.merchant
    full join missing_product_lead_time plt  on plt.merchant = c.merchant
    full join missing_product_moq pmq  on pmq.merchant = c.merchant
    full join missing_variant_moq vmq  on vmq.merchant = c.merchant
    full join missing_product_fulfillment_partner mpfp on mpfp.merchant = c.merchant