Snowflake Trial

Week 71 – Intermediate

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.

-- 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.

Intended solution

3 responses to “Week 71 – Intermediate”

  1. ChrisHastie avatar
    ChrisHastie

    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
  2. Chris B avatar
    Chris B

    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
  3. dsmdavid avatar
    dsmdavid

    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

Leave a Reply