DAA-C01 Question 146
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 EVENTS with the following columns: EVENT_ID, USER_ID, SESSION_ID, EVENT_TS, EVENT_TYPE, PAGE_URL, and PURCHASE_AMOUNT. Analysts need a query that returns one row per SESSION_ID showing: (1) the first page viewed in the session, (2) the last page viewed in the session, and (3) the total PURCHASE_AMOUNT for the session. Sessions can contain many non-page events, and some rows have PURCHASE_AMOUNT as NULL. The team wants the most efficient SQL approach using Snowflake analytic capabilities without self-joining the EVENTS table multiple times. Which query best meets the requirement?
- A
SELECT DISTINCT SESSION_ID, FIRST_VALUE(PAGE_URL) OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS) AS FIRST_PAGE, LAST_VALUE(PAGE_URL) OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS) AS LAST_PAGE, SUM(PURCHASE_AMOUNT) OVER (PARTITION BY SESSION_ID) AS TOTAL_PURCHASE FROM EVENTS WHERE EVENT_TYPE = 'PAGE_VIEW';
- B
SELECT SESSION_ID, MIN(PAGE_URL) AS FIRST_PAGE, MAX(PAGE_URL) AS LAST_PAGE, SUM(COALESCE(PURCHASE_AMOUNT,0)) AS TOTAL_PURCHASE FROM EVENTS GROUP BY SESSION_ID;
- C
SELECT SESSION_ID, FIRST_VALUE(IFF(EVENT_TYPE = 'PAGE_VIEW', PAGE_URL, NULL)) IGNORE NULLS OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FIRST_PAGE, LAST_VALUE(IFF(EVENT_TYPE = 'PAGE_VIEW', PAGE_URL, NULL)) IGNORE NULLS OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_PAGE, SUM(COALESCE(PURCHASE_AMOUNT,0)) OVER (PARTITION BY SESSION_ID) AS TOTAL_PURCHASE FROM EVENTS QUALIFY ROW_NUMBER() OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS DESC, EVENT_ID DESC) = 1;
- D
SELECT SESSION_ID, NTH_VALUE(PAGE_URL, 1) OVER (PARTITION BY SESSION_ID) AS FIRST_PAGE, NTH_VALUE(PAGE_URL, -1) OVER (PARTITION BY SESSION_ID) AS LAST_PAGE, SUM(PURCHASE_AMOUNT) OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS) AS TOTAL_PURCHASE FROM EVENTS QUALIFY RANK() OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS) = 1;
Show answer and explanation
Correct answer: C
Explanation
The best solution uses window functions to calculate session-level attributes in a single pass over the table, then QUALIFY to reduce results to one row per session. In Snowflake, FIRST_VALUE and LAST_VALUE are appropriate for deriving ordered values within a partition, but LAST_VALUE often requires an explicit frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to evaluate across the entire partition instead of the default frame. IGNORE NULLS is useful when only certain event types should contribute to first/last page logic while preserving all rows for other calculations. SUM(COALESCE(PURCHASE_AMOUNT,0)) handles NULL purchase amounts consistently. QUALIFY is a Snowflake best practice for filtering on window-function results without nesting subqueries. This pattern aligns with Snowflake SQL documentation for window functions, window frames, and QUALIFY.
- A. Incorrect.
This is incorrect for two reasons. First, filtering with WHERE EVENT_TYPE = 'PAGE_VIEW' removes non-page events before calculating SUM(PURCHASE_AMOUNT), so any purchase amounts recorded on other event types would be excluded. Second, LAST_VALUE without an explicit window frame in Snowflake commonly returns the value from the current row's frame rather than the last row in the full partition, which can produce incorrect session-level last-page results. Using DISTINCT does not fix the semantic issue.
- B. Incorrect.
This is incorrect because MIN(PAGE_URL) and MAX(PAGE_URL) are lexical comparisons, not time-based first/last values. They return alphabetically smallest and largest URLs, not the first and last page viewed in the session. While SUM(COALESCE(PURCHASE_AMOUNT,0)) is reasonable for handling NULL purchase amounts, the first/last page logic does not satisfy the requirement.
- C. Correct.
This is correct. FIRST_VALUE and LAST_VALUE are used as window functions partitioned by SESSION_ID and ordered by EVENT_TS to derive page sequence within each session. Wrapping PAGE_URL in IFF(EVENT_TYPE = 'PAGE_VIEW', PAGE_URL, NULL) and using IGNORE NULLS ensures only page-view rows contribute to first/last page selection, while still allowing all session rows to remain available for purchase aggregation. The explicit frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is especially important for LAST_VALUE so the full partition is considered. SUM(COALESCE(PURCHASE_AMOUNT,0)) OVER (PARTITION BY SESSION_ID) correctly computes session totals with NULL treated as zero. QUALIFY with ROW_NUMBER returns one output row per session without additional self-joins or subqueries.
- D. Incorrect.
This is incorrect because NTH_VALUE with a negative position is not the right way to get the last value in Snowflake, and the query also omits the required ORDER BY and full-frame semantics needed for meaningful first/last page evaluation. In addition, SUM(PURCHASE_AMOUNT) OVER (PARTITION BY SESSION_ID ORDER BY EVENT_TS) creates a running total, not the final total for the entire session on every row. QUALIFY RANK() = 1 would return the first row in each session, which does not address the last-page requirement.