assignees.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,
    vm.source_schema,
    product_variant_id as variant_id,
    namespace,
    key,
    value
    from
        PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.SHOPIFY_PRODUCT_VARIANT_METAFIELD vm
        inner join merchant m on m.source_schema = vm.source_schema
       qualify row_number() over (
      PARTITION by vm.product_variant_id,vm.id
      ORDER BY
        vm.updated_at DESC
    ) = 1
),
product_metafields as (
SELECT
    m.store_id,
    pm.source_schema,
    product_id,
    namespace,
    key,
    value
    from
        PROSPECTIVE_MERCHANTS_PROD_DATABASE.OS_BIFROST.shopify_product_metafield pm
        inner join merchant m on m.source_schema = pm.source_schema
       qualify row_number() over (
      PARTITION by pm.product_id,pm.id
      ORDER BY
        pm.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_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
    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_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

    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
     WHERE 
      m.store_id not in ('e5d14958-d920-4bda-987f-300c78cd22ec','a8e4e5ef-12bd-4e12-a821-f35b023f6ac2')
     AND left(v.sku,4) <> '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%'
    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
    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 requires_shipping
    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 requires_shipping
    group by 1
),
missing_product_moq_notes as (
        select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (product_moq_notes is null or trim(product_moq_notes) = '')
    AND (variant_moq_notes is null or trim(variant_moq_notes) = '')
    AND requires_shipping
    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 requires_shipping
    group by 1
),
missing_variant_moq_notes as (
    select merchant, count(variant_id) as total_missing
    from catalog
    where status in ('ACTIVE')
    AND (variant_moq_notes is null or trim(variant_moq_notes) = '')
    AND (product_moq_notes is null or trim(product_moq_notes) = '')
    AND requires_shipping
    group by 1
),
duplicate_skus AS (
    SELECT
        sku,
        count(sku) AS _COUNT
    FROM catalog
    where 
    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 assignee1,
    f.assistant_helper_email as assignee2,
    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,
    pmqn.total_missing as  missing_product_moq_notes_count,
    vmq.total_missing as  missing_variant_moq_count,
    vmqn.total_missing as  missing_variant_moq_notes_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,
    (pmqn.total_missing /c.total_variants) as  missing_product_moq_notes_percent,
    (vmq.total_missing /c.total_variants) as  missing_variant_moq_percent,
    (vmqn.total_missing /c.total_variants) as  missing_variant_moq_notes_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_product_moq_notes_percent <= 0.01
        AND missing_product_moq_notes_percent > 0 THEN 4
        WHEN missing_product_moq_notes_percent > 0.01
        AND missing_product_moq_notes_percent < 0.25 THEN 3
        WHEN missing_product_moq_notes_percent >= 0.25
        AND missing_product_moq_notes_percent < 0.50 THEN 2
        WHEN missing_product_moq_notes_percent >= 0.5 THEN 1
        WHEN missing_product_moq_notes_percent IS NULL THEN 5
    END AS missing_product_moq_notes_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_variant_moq_notes_percent <= 0.01
        AND missing_variant_moq_notes_percent > 0 THEN 4
        WHEN missing_variant_moq_notes_percent > 0.01
        AND missing_variant_moq_notes_percent < 0.25 THEN 3
        WHEN missing_variant_moq_notes_percent >= 0.25
        AND missing_variant_moq_notes_percent < 0.50 THEN 2
        WHEN missing_variant_moq_notes_percent >= 0.5 THEN 1
        WHEN missing_variant_moq_notes_percent IS NULL THEN 5
    END AS missing_variant_moq_notes_score
from variants c
    inner join analytics.google_sheets.scm_fulfillment_partners 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_product_moq_notes pmqn  on pmqn.merchant = c.merchant
    full join missing_variant_moq vmq  on vmq.merchant = c.merchant
    full join missing_variant_moq_notes vmqn on vmqn.merchant = c.merchant