Frosty Friday

Snowflake Weekly Challenges

You are here: Home / Uncategorized / Week 70 – Intermediate

by on 5 Comments

Week 70 – Intermediate

Today’s challenge is a guest challenge from Marcel Schwarze!

One typical task during performance and cost optimization is identifying common, heavily executed query patterns and trying to optimize them afterwards. The following challenge is exactly about this: The identification of frequently executed query patterns in combination with the associated execution times. The following queries, which need to be executed as part of the challenge-setup, provide an example what is considered as “queries following the same pattern”. In our scenario, queries with the same pattern have the same query text and only the parameter values used in the WHERE-clauses are allowed to differ (see examples).

Challenge: Complete the following SQL statement to identify queries following the same pattern (as per the mentioned definition/examples), count the execution frequency per pattern and sum the execution time. For each group show one sample query. The expected result can be seen in the screenshot. 

Setup script:


ALTER SESSION SET query_tag = 'ff_challenge';
/* Random queries without common pattern */
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
SELECT COUNT(*) FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_NATIONKEY = 15;
SELECT C_NAME FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_PHONE = '19-144-468-5416';

/* First set of queries following the same pattern. Pattern = All columns from customers with one WHERE-condition for C_MKTSEGMENT. */
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'BUILDING';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'AUTOMOBILE';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'MACHINERY';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'HOUSEHOLD';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'BUILDING';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'BUILDING';

/*Second set of queries following the same pattern. Pattern= C_NAME and C_NATIONKEY from customers with two WHERE-conditions for C_MKTSEGMENT and C_NATIONKEY. */
SELECT C_NAME, C_NATIONKEY FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'BUILDING' AND C_NATIONKEY = 21;
SELECT C_NAME, C_NATIONKEY FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'MACHINERY' AND C_NATIONKEY = 9;

Query to complete:

select …
from table(information_schema.query_history())
where query_tag = ‘ff_challenge’ and query_text NOT ILIKE ‘%information_schema%’
group by …
order by … ;

Comment the link to your solutions in GitHub below!

5 responses to “Week 70 – Intermediate”

  1. ChrisHastie avatar
    ChrisHastie

    I’m actually not sure if I’ve approached this challenge in the way that’s expected, or if I’ve overcomplicated the task and could have just used a few regexp conditions in a case statement, but I’m happy with my output. Thanks for another interesting challenge!

    • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
    1. Chris B avatar
      Chris B

      If you have a look at my solution below you can do this really simply

    2. Chris B avatar
      Chris B

      Nice new little bit of query meta data to learn in this challenge

      • Solution URL – https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_70.sql
    3. dsmdavid avatar
      dsmdavid

      These hashes are :fire:
      Thanks for the challenge!

      • Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_70.sql
    4. darko avatar
      darko

      I repeatedly got into an error were the quert_tag wasn’t recognized in the final query. As a workaround, I decided to change the FROM and WHERE clauses to include queries in the past hour.

      • Solution URL – https://github.com/darko-nimbus/FROSTYFRIDAYCHALLENGES/blob/main/70%20-%20Intermediate%20-%20Cost%20Optimization/70%20-%20Intermediate%20-%20Cost%20Optimization.sql

    Leave a Reply

    Reader Interactions

    Comments

    1. I’m actually not sure if I’ve approached this challenge in the way that’s expected, or if I’ve overcomplicated the task and could have just used a few regexp conditions in a case statement, but I’m happy with my output. Thanks for another interesting challenge!

      • Solution URL - https://github.com/ChrisHastieIW/Frosty-Friday

    2. Nice new little bit of query meta data to learn in this challenge

      • Solution URL - https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_70.sql

    3. These hashes are :fire:
      Thanks for the challenge!

      • Solution URL - https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_70.sql

    4. I repeatedly got into an error were the quert_tag wasn’t recognized in the final query. As a workaround, I decided to change the FROM and WHERE clauses to include queries in the past hour.

      • Solution URL - https://github.com/darko-nimbus/FROSTYFRIDAYCHALLENGES/blob/main/70%20-%20Intermediate%20-%20Cost%20Optimization/70%20-%20Intermediate%20-%20Cost%20Optimization.sql

    Leave a Reply