DAA-C01 Question 234
Single answerSort and filter dataA retail analytics team stores clickstream events in a Snowflake table EVENTS with columns EVENT_TS (TIMESTAMP_NTZ), CUSTOMER_ID, EVENT_TYPE, and REVENUE. An analyst needs a query for a dashboard tile that shows the 10 most recent purchase events with non-zero revenue from the last 7 days. The result must be sorted from newest to oldest event. Which query best meets the requirement?
- A
SELECT CUSTOMER_ID, EVENT_TS, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' AND REVENUE > 0 AND EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP()) ORDER BY EVENT_TS DESC LIMIT 10;
- B
SELECT CUSTOMER_ID, EVENT_TS, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' AND REVENUE > 0 ORDER BY EVENT_TS ASC LIMIT 10;
- C
SELECT CUSTOMER_ID, EVENT_TS, REVENUE FROM EVENTS HAVING EVENT_TYPE = 'PURCHASE' AND REVENUE > 0 AND EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP()) ORDER BY EVENT_TS DESC LIMIT 10;
- D
SELECT CUSTOMER_ID, EVENT_TS, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' AND REVENUE >= 0 AND EVENT_TS >= DATEADD(day, 7, CURRENT_TIMESTAMP()) ORDER BY EVENT_TS DESC LIMIT 10;
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because it correctly applies row-level filtering before sorting and limiting. In Snowflake SQL, WHERE is used to filter rows prior to ORDER BY and LIMIT, making it the appropriate clause for restricting to purchase events, positive revenue, and a recent time window. ORDER BY EVENT_TS DESC ensures the newest events appear first, and LIMIT 10 returns only the top 10 rows after sorting. This pattern is a common best practice for dashboard queries that need recent, filtered results. Snowflake documentation on SELECT syntax, WHERE, ORDER BY, and LIMIT supports this execution pattern, and DATEADD with a negative interval is the standard approach for defining a rolling lookback window such as the last 7 days.
- A. Correct.
Correct. This query filters rows in the WHERE clause to include only purchase events, excludes zero-revenue rows with REVENUE > 0, limits results to the last 7 days using DATEADD(day, -7, CURRENT_TIMESTAMP()), sorts from newest to oldest with ORDER BY EVENT_TS DESC, and returns only 10 rows with LIMIT 10. This directly matches all stated requirements.
- B. Incorrect.
Incorrect. Although it filters for purchase events with non-zero revenue, it does not restrict results to the last 7 days. It also sorts ascending, which would return the oldest matching events first rather than the most recent events required for the dashboard.
- C. Incorrect.
Incorrect. HAVING is used to filter grouped results after aggregation, typically with GROUP BY. In this query there is no aggregation or grouping, so using HAVING for these row-level filters is not appropriate in Snowflake for this scenario. The correct place for these predicates is the WHERE clause.
- D. Incorrect.
Incorrect. This query has two logic errors. First, REVENUE >= 0 includes zero-revenue rows, which violates the non-zero revenue requirement. Second, DATEADD(day, 7, CURRENT_TIMESTAMP()) filters for events 7 days in the future rather than the last 7 days. While the sort direction is correct, the filtering logic is not.