DAA-C01 Question 186
Single answerDomain 3.0: Data Analysis (32%)A retail analytics team stores clickstream events in a Snowflake table with columns EVENT_TS, USER_ID, SESSION_ID, PAGE_URL, and EVENT_TYPE. Analysts need a report that shows, for each user, the amount of time between one event and the next event in the same session so they can identify unusually long pauses before checkout. The team wants a solution that is accurate even when multiple sessions exist for the same user and that avoids complex self-joins for performance and maintainability reasons. Which approach should the analyst use?
- A
Use the LAG window function partitioned by USER_ID and SESSION_ID, ordered by EVENT_TS, and compute the timestamp difference between EVENT_TS and the previous row's EVENT_TS.
- B
Use ROW_NUMBER to assign sequence numbers by USER_ID only, then self-join the table on ROW_NUMBER = ROW_NUMBER + 1 to compare consecutive events.
- C
Use a GROUP BY on USER_ID and SESSION_ID with MIN(EVENT_TS) and MAX(EVENT_TS) to calculate the time between consecutive events in each session.
- D
Use the LEAD window function partitioned by USER_ID only, ordered by PAGE_URL, and compute the timestamp difference to the next row.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical use of analytic window functions for sequential analysis, which is a core skill in Data Analysis. In Snowflake, LAG and LEAD are commonly used to compare the current row with a previous or next row within a defined partition and order. For clickstream or session-based analysis, the partition must reflect the business boundary of the sequence, which here is USER_ID and SESSION_ID. The order must be based on EVENT_TS so that the sequence matches actual event chronology. Using GROUP BY loses row-level sequence information, and self-joins are generally less maintainable and often less efficient than native window functions for this pattern. Snowflake documentation for window functions and ranking/analytic functions supports using LAG/LEAD with PARTITION BY and ORDER BY for adjacent-row comparisons, and best practices for event-stream analysis emphasize choosing the correct partitioning and ordering keys to avoid incorrect cross-group comparisons.
- A. Correct.
Correct. LAG is designed to access a prior row in the same result set without a self-join. Partitioning by both USER_ID and SESSION_ID ensures that event comparisons do not cross session boundaries for the same user. Ordering by EVENT_TS ensures the prior event is the chronologically previous event. The analyst can then use DATEDIFF or TIMESTAMPDIFF-style logic to calculate elapsed time between events. This is the most appropriate and maintainable solution for sequential event analysis in Snowflake.
- B. Incorrect.
Incorrect. Although ROW_NUMBER plus a self-join can sometimes be used to compare adjacent rows, this option partitions only by USER_ID and ignores SESSION_ID, which can incorrectly compare events across different sessions for the same user. It also introduces unnecessary join complexity when Snowflake window functions like LAG or LEAD solve the problem more directly and efficiently.
- C. Incorrect.
Incorrect. MIN(EVENT_TS) and MAX(EVENT_TS) at the session level only provide the total span of the session, not the time between each consecutive event. This is a common misunderstanding when working with event-sequence analysis: aggregation summarizes groups, but it does not preserve row-to-row relationships needed for dwell-time or pause analysis.
- D. Incorrect.
Incorrect. LEAD can also be used for next-event analysis, but this option is wrong because it partitions only by USER_ID and orders by PAGE_URL rather than EVENT_TS. Ordering by PAGE_URL does not represent event sequence, and omitting SESSION_ID can cause event comparisons to cross session boundaries. The issue is not LEAD itself, but the incorrect partitioning and ordering criteria.