DAA-C01 Question 230
Single answerCreate and run SQL queriesA retail analytics team stores clickstream events in a Snowflake table named EVENTS with the columns USER_ID, EVENT_TS, EVENT_TYPE, and REVENUE. An analyst needs a query that returns, for each user, only the most recent purchase event and its revenue. The analyst also wants to avoid unnecessary subqueries when possible to keep the SQL easy to maintain. Which query best meets this requirement?
- A
SELECT USER_ID, EVENT_TS, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' QUALIFY ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY EVENT_TS DESC) = 1;
- B
SELECT USER_ID, MAX(EVENT_TS) AS EVENT_TS, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' GROUP BY USER_ID, REVENUE;
- C
SELECT USER_ID, EVENT_TS, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' AND ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY EVENT_TS DESC) = 1;
- D
SELECT USER_ID, EVENT_TS, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' GROUP BY USER_ID, EVENT_TS, REVENUE HAVING MAX(EVENT_TS);
Show answer and explanation
Correct answer: A
Explanation
The best answer is the query that uses ROW_NUMBER() with QUALIFY. In Snowflake SQL, QUALIFY is designed to filter the result of window functions, similar to how HAVING filters aggregate results. This pattern is a best practice for problems such as 'latest row per group' because it avoids extra nesting and keeps the query readable. By filtering EVENT_TYPE = 'PURCHASE' first, then applying ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY EVENT_TS DESC), the query correctly identifies the most recent purchase row for each user. Options using GROUP BY with MAX(EVENT_TS) but also selecting REVENUE are flawed because aggregates do not automatically preserve values from the same source row. This is a common SQL anti-pattern. Snowflake documentation on QUALIFY and window functions supports using QUALIFY for post-window filtering in exactly this kind of scenario.
- A. Correct.
Correct. In Snowflake, QUALIFY filters the results of window functions after they are computed, making it ideal for returning the top-ranked row per partition without wrapping the logic in a subquery. ROW_NUMBER() partitions by USER_ID and orders purchase events by EVENT_TS descending, so rank 1 is the most recent purchase for each user. This is both syntactically valid and aligns with the requirement to avoid unnecessary subqueries.
- B. Incorrect.
Incorrect. This query groups by USER_ID and REVENUE, which can return multiple rows per user if the user has different revenue values across purchases. It also does not reliably associate the MAX(EVENT_TS) with the correct REVENUE from the same row unless additional logic is used. This is a common mistake when trying to fetch non-aggregated columns alongside an aggregate.
- C. Incorrect.
Incorrect. Window functions such as ROW_NUMBER() cannot be used directly in the WHERE clause in Snowflake because WHERE is evaluated before window functions are calculated. Snowflake provides QUALIFY specifically for filtering on window function results.
- D. Incorrect.
Incorrect. This query is not valid for the stated purpose. GROUP BY USER_ID, EVENT_TS, REVENUE preserves all distinct purchase rows, so it does not reduce the data to the latest row per user. In addition, HAVING MAX(EVENT_TS) is not a meaningful filter here because HAVING expects a Boolean condition, not just an aggregate expression.