DAA-C01 Question 145
Single answer2.3 Given a dataset or scenario, work with and query the data.A retail analytics team stores clickstream events in a Snowflake table named EVENT_LOG with the following simplified structure: SESSION_ID STRING, EVENT_TS TIMESTAMP_NTZ, PAGE_NAME STRING, EVENT_TYPE STRING, and ATTRIBUTES VARIANT. Analysts need a query that returns one row per SESSION_ID with: (1) the first page viewed in the session based on EVENT_TS, (2) the timestamp of the most recent event in the session, and (3) the total number of events in the session. The solution should be efficient and avoid unnecessary self-joins. Which SQL statement best meets these requirements?
- A
SELECT SESSION_ID, MIN(PAGE_NAME) AS FIRST_PAGE, MAX(EVENT_TS) AS LAST_EVENT_TS, COUNT(*) AS EVENT_COUNT FROM EVENT_LOG GROUP BY SESSION_ID;
- B
SELECT SESSION_ID, FIRST_VALUE(PAGE_NAME) OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS) AS FIRST_PAGE, MAX(EVENT_TS) OVER (PARTITION BY SESSION_ID) AS LAST_EVENT_TS, COUNT(*) OVER (PARTITION BY SESSION_ID) AS EVENT_COUNT FROM EVENT_LOG QUALIFY ROW_NUMBER() OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS DESC) = 1;
- C
SELECT SESSION_ID, PAGE_NAME AS FIRST_PAGE, EVENT_TS AS LAST_EVENT_TS, COUNT(*) AS EVENT_COUNT FROM EVENT_LOG GROUP BY SESSION_ID, PAGE_NAME, EVENT_TS QUALIFY ROW_NUMBER() OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS) = 1;
- D
SELECT DISTINCT SESSION_ID, FIRST_VALUE(PAGE_NAME) OVER (ORDER BY EVENT_TS) AS FIRST_PAGE, MAX(EVENT_TS) OVER (PARTITION BY SESSION_ID) AS LAST_EVENT_TS, COUNT(*) OVER () AS EVENT_COUNT FROM EVENT_LOG;
Show answer and explanation
Correct answer: B
Explanation
The best solution is to use window functions with PARTITION BY SESSION_ID so that calculations are scoped to each session while avoiding self-joins. In Snowflake, FIRST_VALUE can return the first ordered value within a partition, and MAX and COUNT can also be used as window functions to compute per-session metrics on every row. QUALIFY is especially useful in Snowflake because it filters rows after window functions are evaluated, allowing the query to keep exactly one representative row per SESSION_ID without wrapping the logic in a subquery. This pattern is a Snowflake best practice for row-level filtering based on window function results. Relevant Snowflake documentation includes window functions, FIRST_VALUE, ROW_NUMBER, and the QUALIFY clause.
- A. Incorrect.
Incorrect. MIN(PAGE_NAME) returns the lexicographically smallest page name, not the page associated with the earliest EVENT_TS. This is a common mistake when trying to derive a value from the first row in a time sequence using an aggregate that is unrelated to ordering. While MAX(EVENT_TS) and COUNT(*) per SESSION_ID are valid aggregates, FIRST_PAGE would be wrong unless page names happened to sort the same way as event timestamps, which cannot be assumed.
- B. Correct.
Correct. This query uses window functions partitioned by SESSION_ID to derive all required session-level metrics without self-joins. FIRST_VALUE(PAGE_NAME) OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS) returns the first page in each session by timestamp. MAX(EVENT_TS) OVER (PARTITION BY SESSION_ID) returns the most recent event timestamp, and COUNT(*) OVER (PARTITION BY SESSION_ID) returns the total events in the session. QUALIFY filters the result set to one row per session by keeping only the latest event row in each partition, while preserving the window-calculated values for the entire session.
- C. Incorrect.
Incorrect. This query groups by SESSION_ID, PAGE_NAME, and EVENT_TS, which produces multiple rows per session before QUALIFY is evaluated. In addition, COUNT(*) in this grouped query counts rows per unique grouped combination rather than all events in the session. It also selects PAGE_NAME and EVENT_TS from the row that QUALIFY keeps, which would correspond to the earliest event because of ORDER BY EVENT_TS, but that would make LAST_EVENT_TS incorrect. The structure mixes grouping and windowing in a way that does not satisfy the requirement.
- D. Incorrect.
Incorrect. FIRST_VALUE(PAGE_NAME) OVER (ORDER BY EVENT_TS) is not partitioned by SESSION_ID, so it would return the first page across the entire table, not per session. COUNT(*) OVER () similarly returns the total number of rows in the entire table rather than the number of events per session. DISTINCT does not fix the logical issue; it only removes duplicate output rows after the incorrect window calculations have already been made.