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.

Practice

1. What is the main purpose of the QUERY_HISTORY view in Snowflake?

easy

A. To see details of past queries executed in the system

B. To create new tables and schemas

C. To manage user permissions and roles

D. To monitor network traffic between Snowflake and clients

Solution

1. Step 1: Understand the role of QUERY_HISTORY

   The QUERY_HISTORY view stores information about queries that have already run, including their text, execution time, and status. Its main purpose is to see details of past queries executed in the system.

2. Final Answer:

   To see details of past queries executed in the system -> Option A

3. Quick Check:

   QUERY_HISTORY = past query details [OK]

Hint: QUERY_HISTORY shows past queries and their info [OK]

Common Mistakes:

• Confusing QUERY_HISTORY with user management
• Thinking it manages network or security settings
• Assuming it creates or modifies database objects

2. Which SQL clause correctly filters queries executed by a specific user in the QUERY_HISTORY view?

easy

A. FILTER BY USER = 'john_doe'

B. WHERE USER_NAME = 'john_doe'

C. SELECT USER_NAME FROM QUERY_HISTORY WHERE 'john_doe'

D. HAVING USER_NAME = 'john_doe'

Solution

1. Step 1: Recall SQL filtering syntax

   To filter rows in SQL, the WHERE clause is used with a condition like USER_NAME = 'value'. WHERE USER_NAME = 'john_doe' is valid and standard SQL syntax.

2. Final Answer:

   WHERE USER_NAME = 'john_doe' -> Option B

3. Quick Check:

   Filter with WHERE clause = WHERE USER_NAME = 'john_doe' [OK]

Hint: Use WHERE to filter rows by user name [OK]

Common Mistakes:

• Using FILTER BY instead of WHERE
• Misplacing HAVING without GROUP BY
• Incorrect SELECT syntax without WHERE

3. Given the query:

SELECT query_text, total_elapsed_time FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE execution_status = 'FAILED' ORDER BY start_time DESC LIMIT 1;

What does this query return?

medium

A. The most recent failed query's text and its total elapsed time

B. All successful queries ordered by start time

C. The oldest failed query's text and elapsed time

D. An error because QUERY_HISTORY is not a table

Solution

1. Step 1: Analyze the query clauses

   The query uses TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) to access query history, filters for execution_status = 'FAILED', orders by start_time DESC (most recent first), and limits to 1 row, returning the most recent failed query's text and total elapsed time.

2. Final Answer:

   The most recent failed query's text and its total elapsed time -> Option A

3. Quick Check:

   Filter failed + order desc + limit 1 = most recent failed query [OK]

Hint: ORDER BY DESC + LIMIT 1 gets latest record [OK]

Common Mistakes:

• Thinking QUERY_HISTORY is a normal table
• Confusing oldest vs most recent due to ORDER BY
• Ignoring the WHERE filter on execution_status

4. You wrote this query to find slow queries but it returns no results:

SELECT query_text FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE total_elapsed_time > 1000;

What is the likely issue?

medium

A. The TABLE() function cannot be used with QUERY_HISTORY

B. QUERY_HISTORY does not have total_elapsed_time column

C. The query_text column cannot be selected from QUERY_HISTORY

D. total_elapsed_time is in microseconds, so 1000 is too small a threshold

Solution

1. Step 1: Check the unit of total_elapsed_time

   In Snowflake, total_elapsed_time is measured in microseconds, so 1000 microseconds (1 millisecond) is too small a threshold, and few or no queries exceed it, resulting in no results.

2. Final Answer:

   total_elapsed_time is in microseconds, so 1000 is too small a threshold -> Option D

3. Quick Check:

   Elapsed time unit = microseconds, threshold too low [OK]

Hint: Check units: elapsed time is microseconds, not milliseconds [OK]

Common Mistakes:

• Assuming elapsed time is in seconds or milliseconds
• Thinking QUERY_HISTORY lacks columns
• Misusing TABLE() function syntax

5. You want to profile query performance by grouping queries by user and calculating average execution time. Which query correctly achieves this?

hard

A. SELECT user_name, SUM(total_elapsed_time) FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE execution_status = 'SUCCESS';

B. SELECT user_name, total_elapsed_time FROM QUERY_HISTORY GROUP BY user_name;

C. SELECT user_name, AVG(total_elapsed_time) AS avg_time FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) GROUP BY user_name ORDER BY avg_time DESC;

D. SELECT user_name, AVG(total_elapsed_time) FROM QUERY_HISTORY WHERE total_elapsed_time > 1000 ORDER BY user_name;

Solution

1. Step 1: Identify correct aggregation and grouping

   To get average execution time per user, use AVG(total_elapsed_time) with GROUP BY user_name from TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()), as in SELECT user_name, AVG(total_elapsed_time) AS avg_time ... GROUP BY user_name ORDER BY avg_time DESC.

2. Final Answer:

   SELECT user_name, AVG(total_elapsed_time) AS avg_time FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) GROUP BY user_name ORDER BY avg_time DESC; -> Option C

3. Quick Check:

   Group by user + AVG + ORDER BY avg_time = SELECT user_name, AVG(total_elapsed_time) AS avg_time FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) GROUP BY user_name ORDER BY avg_time DESC; [OK]

Hint: Use GROUP BY user_name with AVG for profiling [OK]

Common Mistakes:

• Missing GROUP BY when using aggregation
• Selecting columns without aggregation
• Not using TABLE() function for QUERY_HISTORY