Created
August 31, 2016 22:50
-
-
Save sumanththikka/6d3edec610ddb14d9fd3e3afad9bc0e0 to your computer and use it in GitHub Desktop.
SKUs without Contemporary style
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
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment