Query history and profiling in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to query the last 5 executed queries from the history.

Snowflake

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) ORDER BY START_TIME DESC LIMIT [1];

Drag options to blanks, or click blank then click option'

A10

B5

C1

D20

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIMIT without ordering may not show the most recent queries.

Using a number too large or too small for the task.

The LIMIT clause controls how many recent queries are shown. Using 5 shows the last 5 queries.

2fill in blank

medium

Complete the code to filter query history for queries executed by user 'ANALYST'.

Snowflake

SELECT QUERY_ID, USER_NAME, QUERY_TEXT FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE USER_NAME = '[1]';

Drag options to blanks, or click blank then click option'

ASYSADMIN

BADMIN

CANALYST

DGUEST

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around the username.

Using the wrong username.

Filtering USER_NAME by 'ANALYST' returns queries run by that user.

3fill in blank

hard

Fix the error in the code to show queries that took longer than 10 seconds.

Snowflake

SELECT QUERY_ID, EXECUTION_TIME FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE EXECUTION_TIME [1] 10000;

Drag options to blanks, or click blank then click option'

A>

B<

C=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > reverses the filter.

Confusing seconds with milliseconds.

EXECUTION_TIME is in milliseconds, so > 10000 filters queries longer than 10 seconds.

4fill in blank

hard

Fill both blanks to calculate average execution time and count of queries per user.

Snowflake

SELECT USER_NAME, AVG(EXECUTION_TIME) AS AVG_TIME, COUNT(*) AS QUERY_COUNT FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) GROUP BY [1] ORDER BY [2] DESC;

Drag options to blanks, or click blank then click option'

AUSER_NAME

BEXECUTION_TIME

CQUERY_ID

DSTART_TIME

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by USER_NAME instead of AVG_TIME changes the result order.

Grouping by wrong column causes errors.

Grouping by USER_NAME calculates average execution time and count per user. Ordering by AVG_TIME DESC sorts users by average execution time descending.

5fill in blank

hard

Fill all three blanks to select query id, execution time, and filter queries run in the last hour.

Snowflake

SELECT QUERY_ID, EXECUTION_TIME, START_TIME FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE START_TIME > DATEADD([1], -[2], CURRENT_TIMESTAMP()) AND EXECUTION_TIME [3] 0;

Drag options to blanks, or click blank then click option'

Ahour

B1

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'day' instead of 'hour' changes the time window.

Using < instead of > reverses the filter.

DATEADD with 'hour' and -1 subtracts one hour from current time. Filtering EXECUTION_TIME > 0 shows only completed queries.