DAA-C01 Question 235
Single answerSort and filter dataA retail analytics team stores clickstream events in a Snowflake table named EVENTS with the columns USER_ID, EVENT_TS, EVENT_TYPE, and ATTRIBUTES. An analyst needs a query that returns only purchase events from the last 7 days and lists the most recent purchases first. The analyst also wants to avoid logic that could break if the current session time zone changes. Which query best meets these requirements?
- A
SELECT USER_ID, EVENT_TS, EVENT_TYPE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' AND EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP()) ORDER BY EVENT_TS DESC;
- B
SELECT USER_ID, EVENT_TS, EVENT_TYPE FROM EVENTS WHERE EVENT_TYPE ILIKE '%PURCHASE%' AND DATEDIFF(day, EVENT_TS, CURRENT_TIMESTAMP()) <= 7 ORDER BY EVENT_TS;
- C
SELECT USER_ID, EVENT_TS, EVENT_TYPE FROM EVENTS HAVING EVENT_TYPE = 'PURCHASE' AND EVENT_TS >= DATEADD(day, -7, CURRENT_DATE()) ORDER BY EVENT_TS DESC;
- D
SELECT USER_ID, EVENT_TS, EVENT_TYPE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' AND TO_DATE(EVENT_TS) >= CURRENT_DATE() - 7 ORDER BY 2 DESC;
Show answer and explanation
Correct answer: A
Explanation
The best solution is to filter with a direct timestamp comparison and sort explicitly in descending order. In Snowflake, ORDER BY ... DESC is the standard way to return the newest records first. For time-based filtering, comparing EVENT_TS directly to DATEADD(day, -7, CURRENT_TIMESTAMP()) preserves timestamp precision and aligns with a rolling 7-day requirement. By contrast, converting timestamps to dates with TO_DATE or comparing against CURRENT_DATE truncates time information and can shift boundaries unexpectedly. Similarly, HAVING is intended for filtering aggregated result sets, not ungrouped base rows. Snowflake documentation and SQL best practices support using WHERE for row filtering, ORDER BY for result sorting, and DATEADD/CURRENT_TIMESTAMP for precise relative time filtering.
- A. Correct.
Correct. This query uses a precise timestamp filter with DATEADD(day, -7, CURRENT_TIMESTAMP()) and orders results by EVENT_TS DESC so the newest purchase events appear first. Using CURRENT_TIMESTAMP() keeps the comparison in timestamp form rather than truncating to a date, which is appropriate when the requirement is the last 7 days as a rolling 7 x 24-hour window. This is the best match for both filtering and sorting requirements.
- B. Incorrect.
Incorrect. Although it filters based on recency, there are two issues. First, EVENT_TYPE ILIKE '%PURCHASE%' is broader than required and could match unintended values such as 'REPURCHASE' or 'PURCHASE_COMPLETE' if those exist. Second, ORDER BY EVENT_TS sorts ascending by default, so the oldest qualifying rows appear first, which violates the requirement to show the most recent purchases first. Using DATEDIFF in the predicate can also be less precise around boundary conditions than directly comparing timestamps.
- C. Incorrect.
Incorrect. HAVING is used to filter grouped results after aggregation, not ordinary row-level filtering in a query with no GROUP BY. This statement is not the correct pattern for filtering base rows. In addition, CURRENT_DATE() returns a date, not a timestamp, so it would represent midnight of the current date in the session context rather than an exact rolling 7-day timestamp boundary.
- D. Incorrect.
Incorrect. This query may appear reasonable, but converting EVENT_TS to a date with TO_DATE(EVENT_TS) removes the time component. That changes the meaning from last 7 days to a date-based filter and can include rows older than 7 x 24 hours or exclude rows depending on date boundaries. It also relies on date conversion behavior tied to session settings and timestamp semantics, making it less robust than a direct timestamp comparison.