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.
Query to complete:
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!