SKUs without Contemporary style

sans_contemporary.sql

CREATE TABLE discover_bi_development.sku_style_from_collections_dup_1 AS
WITH sku_collection_positions AS (
    SELECT
      collection_variants.collection_id,
      spree.variants.id AS variant_id,
      spree.variants.sku,
      collection_variants.position,
      ((collection_variants.position / 3)+1) as row,
      ((collection_variants.position % 3)+1) as column
    FROM spree.collection_variants
      JOIN spree.variants ON spree.collection_variants.variant_id = spree.variants.id
    WHERE collection_variants.position IS NOT NULL
),
    collection_max_rows AS (
      SELECT
        collection_variants.collection_id,
        collections.permalink,
        ((MAX(collection_variants.position) / 3)+1) as max_row
      FROM spree.collection_variants
        JOIN spree.collections ON spree.collections.id = collection_variants.collection_id
        JOIN discover_bi_development.collections_data ON collections_data.permalink = collections.permalink
      GROUP BY 1, 2
  ),
    collection_num_styles AS (
      SELECT
        collections.id AS collection_id,
        collections.permalink,
        SUM(COALESCE(REGEXP_COUNT((REPLACE(REPLACE(discover_bi_development.collections_data.style, 'Contemporary,', ''), ', Contemporary', '')), '[\,]') + 1,0)) AS num_styles
      FROM discover_bi_development.collections_data
        JOIN spree.collections ON spree.collections.permalink = collections_data.permalink
      GROUP BY 1, 2
  ),
    sku_collection_style_counts AS (
      select
        spree.variants.sku,
        SUM(num_styles) AS num_styles,
        LISTAGG(discover_bi_development.collections_data.series, ',') AS series,
        LISTAGG(discover_bi_development.collections_data.place, ',') AS places,
        LISTAGG(discover_bi_development.collections_data.room, ',') AS rooms,
        LISTAGG(discover_bi_development.collections_data.theme, ',') AS themes,
        LISTAGG(discover_bi_development.collections_data.home, ',') AS homes,
        LISTAGG(discover_bi_development.collections_data.color_palette, ',') AS color_palettes,
        LISTAGG(discover_bi_development.collections_data.texture_materials, ',') AS materials,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%boho%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) as boho_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%eclectic%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) as eclectic_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%industrial%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) as industrial_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%mid-century%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) as mid_century_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%modern%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) as modern_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%organic%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) as organic_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%scandinavian%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) as scandinavian_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%shabby%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) as shabby_chic_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%southwest%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) as southwest_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%traditional%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) AS traditional_count,
        SUM(CASE WHEN discover_bi_development.collections_data.style ILIKE '%rustic%'
          THEN (CASE WHEN ((max_row::numeric + 1 - row::numeric)/max_row::float) > 0.25 THEN ((max_row::numeric + 1 - row::numeric)/max_row::float) ELSE 0.25 END) ELSE 0 END) AS rustic_count
      FROM sku_collection_positions
        JOIN spree.variants ON sku_collection_positions.variant_id = spree.variants.id
        JOIN collection_max_rows ON collection_max_rows.collection_id = sku_collection_positions.collection_id
        JOIN discover_bi_development.collections_data ON collections_data.permalink = collection_max_rows.permalink
        JOIN collection_num_styles ON collection_num_styles.collection_id = sku_collection_positions.collection_id
      WHERE spree.variants.sku != ''
      GROUP BY spree.variants.sku
  )
SELECT
  sku,
  num_styles,
  series,
  places,
  rooms,
  themes,
  homes,
  color_palettes,
  materials,
  (float4(boho_count)/float4(num_styles)*100)::numeric(7,2) AS boho_percent,
  (float4(eclectic_count)/float4(num_styles)*100)::numeric(7,2) AS eclectic_percent,
  (float4(industrial_count)/float4(num_styles)*100)::numeric(7,2) AS industrial_percent,
  (float4(mid_century_count)/float4(num_styles)*100)::numeric(7,2) AS mid_century_percent,
  (float4(modern_count)/float4(num_styles)*100)::numeric(7,2) AS modern_percent,
  (float4(organic_count)/float4(num_styles)*100)::numeric(7,2) AS organic_percent,
  (float4(scandinavian_count)/float4(num_styles)*100)::numeric(7,2) AS scandinavian_percent,
  (float4(shabby_chic_count)/float4(num_styles)*100)::numeric(7,2) AS shabby_chic_percent,
  (float4(southwest_count)/float4(num_styles)*100)::numeric(7,2) AS southwest_percent,
  (float4(traditional_count)/float4(num_styles)*100)::numeric(7,2) AS traditional_percent,
  (float4(rustic_count)/float4(num_styles)*100)::numeric(7,2) AS rustic_percent
FROM sku_collection_style_counts;