DAA-C01 Question 93
Single answerWindow functionsA retail analytics team stores clickstream events in a Snowflake table EVENTS with the columns USER_ID, EVENT_TS, EVENT_TYPE, and REVENUE. They need a query that returns only the most recent purchase event for each user, and if a user has multiple purchase events with the same timestamp, the row with the highest REVENUE should be kept. The team wants a solution that is concise and avoids unnecessary subqueries. Which SQL pattern best meets this requirement?
- A
SELECT USER_ID, EVENT_TS, EVENT_TYPE, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' QUALIFY ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY EVENT_TS DESC, REVENUE DESC) = 1;
- B
SELECT USER_ID, EVENT_TS, EVENT_TYPE, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' GROUP BY USER_ID, EVENT_TS, EVENT_TYPE, REVENUE HAVING MAX(EVENT_TS) AND MAX(REVENUE);
- C
SELECT USER_ID, EVENT_TS, EVENT_TYPE, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' QUALIFY RANK() OVER (PARTITION BY USER_ID ORDER BY EVENT_TS DESC) = 1;
- D
SELECT USER_ID, EVENT_TS, EVENT_TYPE, REVENUE FROM EVENTS WHERE EVENT_TYPE = 'PURCHASE' QUALIFY LAST_VALUE(REVENUE) OVER (PARTITION BY USER_ID ORDER BY EVENT_TS) = REVENUE;
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use ROW_NUMBER() with PARTITION BY USER_ID and ORDER BY EVENT_TS DESC, REVENUE DESC, then filter with QUALIFY = 1. This is a common Snowflake pattern for deduplicating or selecting the top row per group without nesting subqueries. QUALIFY is particularly useful in Snowflake because it filters after window functions are computed, analogous to how HAVING filters after aggregation. RANK and DENSE_RANK are useful when ties should be preserved, but the scenario requires exactly one row per user, making ROW_NUMBER the appropriate choice. Snowflake documentation and best practices for analytic SQL emphasize combining window functions with QUALIFY for concise and readable top-per-group queries.
- A. Correct.
Correct. ROW_NUMBER() assigns a unique sequence within each USER_ID partition based on the specified sort order. Ordering by EVENT_TS DESC ensures the latest purchase is considered first, and REVENUE DESC breaks ties when multiple purchases share the same timestamp. QUALIFY filters on the result of the window function directly, which is the Snowflake-specific concise pattern that avoids wrapping the query in an extra subquery.
- B. Incorrect.
Incorrect. This option misuses GROUP BY and HAVING. MAX(EVENT_TS) and MAX(REVENUE) are aggregate expressions, but the HAVING clause shown is not a valid boolean condition for selecting the desired row. Even if rewritten, simple aggregation would not reliably return the full row associated with the latest timestamp and highest revenue tie-breaker without an additional join or windowing logic.
- C. Incorrect.
Incorrect. RANK() OVER (PARTITION BY USER_ID ORDER BY EVENT_TS DESC) = 1 returns all rows tied for the latest timestamp for a user. Because the requirement is to keep only one row when timestamps tie, and specifically the one with the highest REVENUE, this option is incomplete. A candidate might choose this because RANK is often used for top-N logic, but here ROW_NUMBER with a secondary sort key is required.
- D. Incorrect.
Incorrect. LAST_VALUE is not the right function for selecting the latest row in this scenario. In Snowflake, window frame behavior matters for value functions, and without explicitly defining a frame, LAST_VALUE can produce results that are often misunderstood. More importantly, comparing LAST_VALUE(REVENUE) to REVENUE does not guarantee selection of the latest purchase row per user, nor does it implement the required timestamp-first and revenue-second ordering correctly.