Exam DSA-C03 Topic 1 Question 5 Discussion

Actual exam question for Snowflake's DSA-C03 exam
Question #: 5
Topic #: 1
You are analyzing sensor data collected from industrial machines, which includes temperature readings. You need to identify machines with unusually high temperature variance compared to their peers. You have a table named 'sensor _ readings' with columns 'machine_id', 'timestamp', and 'temperature'. Which of the following SQL queries will help you identify machines with a temperature variance that is significantly higher than the average temperature variance across all machines? Assume 'significantly higher' means more than two standard deviations above the mean variance.

Suggested Answer: A Vote an answer

The correct answer is A. This query first calculates the variance for each machine using a CTE (Common Table Expression). Then, it calculates the average variance and standard deviation of variances across all machines. Finally, it selects the machine IDs where the variance is more than two standard deviations above the average variance. Option B is incorrect because it tries to calculate aggregate functions within the HAVING clause without proper grouping. Option C uses a JOIN which is inappropriate in this scenario. Option D is incorrect because the window functions will not return the correct aggregate values. Option E is syntactically incorrect. QUALIFY clause should have partition BY statement.

by Lionel at Sep 20, 2025, 12:53 AM

Limited Time Offer

15%

Off

Get Premium DSA-C03 Questions as Interactive Self Test Engine or PDF

Comments

Chosen Answer:
This is a voting comment (?) , you can switch to a simple comment.
Switch to a voting comment New
Nick name: Submit Cancel
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
0
0
0
10