by 
In a BI scenario, arrays of multisets can be useful for representing and analyzing data with multiple values for a single attribute. Multiset arrays can be particularly relevant in scenarios where you have multivalued or hierarchical data. Here we deal with a common use case, namely product category management, where a product may belong to multiple categories.
Start up Code
-- Create the Sales table
CREATE OR REPLACE TABLE Sales (
Sale_ID INT PRIMARY KEY,
Product_IDs VARIANT --INT
);
-- Inserting sample sales data
INSERT INTO Sales (Sale_ID, Product_IDs) SELECT 1, PARSE_JSON('[1, 3]');-- Products A and C in the same sale
INSERT INTO Sales (Sale_ID, Product_IDs) SELECT 2, PARSE_JSON('[2, 4]');-- Products B and D in the same sale
-- Create the Products table
CREATE OR REPLACE TABLE Products (
Product_ID INT PRIMARY KEY,
Product_Name VARCHAR,
Product_Categories VARIANT --VARCHAR
);
-- Inserting sample data into Products
INSERT INTO Products (Product_ID, Product_Name, Product_Categories) SELECT 1, 'Product A', ARRAY_CONSTRUCT('Electronics', 'Gadgets');
INSERT INTO Products (Product_ID, Product_Name, Product_Categories) SELECT 2, 'Product B', ARRAY_CONSTRUCT('Clothing', 'Accessories');
INSERT INTO Products (Product_ID, Product_Name, Product_Categories) SELECT 3, 'Product C', ARRAY_CONSTRUCT('Electronics', 'Appliances');
INSERT INTO Products (Product_ID, Product_Name, Product_Categories) SELECT 4, 'Product D', ARRAY_CONSTRUCT('Clothing');
Using the tables that we have just created, how can we find common categories among products sold together in a single transaction? Like in the example shown below.
5 responses to “Week 71 – Intermediate”
-
Another week, another fun challenge. My solution this week outputs two results, as the screenshot doesn’t match my interpretation of “common categories” and I couldn’t help but output my interpretation too.
Thanks!
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
Agree with ChrisHastie, wouldn’t call it common categories seeing as this output is effectively just a join directly to the product category and turning it into an array.
I’ve done a different answer as well which first looks at the count of categories per sale and only outputs a single array with categories having count > 1 within a single sale. Giving an output of:
SALE_ID – COMMONCATEGORIES
1 – [ “Electronics” ]
2 – [ “Clothing” ]- Solution URL – https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_71.sql
-
Thanks for the challenge! Good to explore those array functions.
I like how this “simple” challenge has already started a debate on “what do you mean by `common`?” 🙂
I went with:
* answer_in_the_prompt,
* all_categories_in_sale,
* categories_common_to_all_elements_in_sale- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_71_03_common_categories.sql
-
Nice chance to play around with arrays!
https://github.com/marioveld/frosty_friday/blob/main/ffw71/ffw71.sql
- Solution URL – https://github.com/marioveld/frosty_friday/blob/main/ffw71/ffw71.sql
-
I remember that clause in the SnowPro Core exam but I never used it, until now
- Solution URL – https://github.com/darko-nimbus/FROSTYFRIDAYCHALLENGES/blob/51efe7bee167f8c491c26b60df9dbb884e81f96c/71%20-%20Intermediate%20-%20Arrays/71%20-%20Intermediate%20-%20Arrays.sql
Leave a Reply
You must be logged in to post a comment.