Week 70 - Administration

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. 

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!