DAA-C01 Question 185
Single answerDomain 3.0: Data Analysis (32%)A retail analytics team stores clickstream events in a Snowflake table with the columns USER_ID, EVENT_TS, SESSION_ID, EVENT_TYPE, and REVENUE. Analysts need a query that returns, for each user, the revenue from only the most recent session based on the latest EVENT_TS in that session. The team wants the solution to be concise, easy to maintain, and to avoid unnecessary subqueries where possible. Which approach best meets these requirements?
- A
Use ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY EVENT_TS DESC) in the SELECT list and filter for ROW_NUMBER = 1 in a WHERE clause.
- B
Aggregate by USER_ID and SESSION_ID to compute MAX(EVENT_TS) and SUM(REVENUE), then use QUALIFY ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY MAX(EVENT_TS) DESC) = 1.
- C
Use FIRST_VALUE(REVENUE) OVER (PARTITION BY USER_ID ORDER BY EVENT_TS DESC) to return the latest session revenue for each user.
- D
Use HAVING MAX(EVENT_TS) = EVENT_TS after grouping by USER_ID to keep only the latest session per user.
Show answer and explanation
Correct answer: B
Explanation
The key requirement is to identify the latest session for each user and then return the total revenue for that session. In Snowflake, the cleanest pattern is often a two-step logical query: first aggregate to the business grain you need (here, USER_ID and SESSION_ID), then rank within each USER_ID by the session's MAX(EVENT_TS), and finally filter with QUALIFY. QUALIFY is a Snowflake feature specifically designed to filter the results of window functions, similar to how HAVING filters aggregate results. This avoids wrapping the ranking logic in an additional subquery or CTE solely for filtering. A representative pattern is: SELECT USER_ID, SESSION_ID, SUM(REVENUE) AS SESSION_REVENUE, MAX(EVENT_TS) AS SESSION_LAST_TS FROM clickstream GROUP BY USER_ID, SESSION_ID QUALIFY ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY MAX(EVENT_TS) DESC) = 1. This aligns with Snowflake SQL best practices for analytic queries and proper use of window functions and QUALIFY.
- A. Incorrect.
Incorrect. In Snowflake, window functions such as ROW_NUMBER() cannot be filtered directly in the WHERE clause because WHERE is evaluated before window functions. To filter on a window function result, Snowflake supports QUALIFY. Also, this option ranks individual event rows, not sessions, so it would return the latest event rather than the total revenue for the latest session.
- B. Correct.
Correct. This approach first aggregates at the session level, which is necessary because the requirement is to return revenue for the most recent session, not the most recent event. By grouping on USER_ID and SESSION_ID, the query can calculate both SUM(REVENUE) for the session and MAX(EVENT_TS) to identify session recency. QUALIFY then filters the ranked session rows per user without requiring an extra subquery, which aligns with Snowflake best practice for filtering window function results concisely.
- C. Incorrect.
Incorrect. FIRST_VALUE(REVENUE) over event rows would return a revenue value from a single row, not the total revenue for the session. Even if adjusted, this option does not solve the need to aggregate all revenue within the latest session before selecting one session per user. It reflects a common misconception that analytic functions can replace needed aggregation logic.
- D. Incorrect.
Incorrect. HAVING filters grouped results, but this expression is not valid for selecting the latest session per user after grouping only by USER_ID. It also does not account for SESSION_ID-level aggregation needed to identify and sum the latest session. This confuses aggregate filtering with ranking grouped rows within each user.