DAA-C01 Question 92
Single answerAggregate functionsA retail analytics team stores clickstream events in a Snowflake table named WEB_EVENTS with the columns SESSION_ID, EVENT_TS, EVENT_TYPE, and REVENUE. They need a daily KPI report that returns one row per calendar day with: (1) the number of distinct sessions, (2) the number of purchase events, and (3) the total revenue from purchase events only. The team wants a solution that is correct even when REVENUE is NULL for some non-purchase events. Which query best meets the requirement?
- A
SELECT DATE(EVENT_TS) AS EVENT_DATE, COUNT(DISTINCT SESSION_ID) AS DISTINCT_SESSIONS, COUNT_IF(EVENT_TYPE = 'PURCHASE') AS PURCHASE_EVENTS, SUM(IFF(EVENT_TYPE = 'PURCHASE', REVENUE, 0)) AS PURCHASE_REVENUE FROM WEB_EVENTS GROUP BY DATE(EVENT_TS);
- B
SELECT DATE(EVENT_TS) AS EVENT_DATE, COUNT(SESSION_ID) AS DISTINCT_SESSIONS, SUM(EVENT_TYPE = 'PURCHASE') AS PURCHASE_EVENTS, SUM(REVENUE) AS PURCHASE_REVENUE FROM WEB_EVENTS GROUP BY DATE(EVENT_TS);
- C
SELECT DATE(EVENT_TS) AS EVENT_DATE, APPROX_COUNT_DISTINCT(SESSION_ID) AS DISTINCT_SESSIONS, COUNT(REVENUE) AS PURCHASE_EVENTS, SUM(CASE WHEN EVENT_TYPE = 'PURCHASE' THEN REVENUE END) AS PURCHASE_REVENUE FROM WEB_EVENTS GROUP BY DATE(EVENT_TS);
- D
SELECT DATE(EVENT_TS) AS EVENT_DATE, COUNT(DISTINCT SESSION_ID) AS DISTINCT_SESSIONS, COUNT(EVENT_TYPE = 'PURCHASE') AS PURCHASE_EVENTS, SUM(COALESCE(REVENUE, 0)) AS PURCHASE_REVENUE FROM WEB_EVENTS GROUP BY DATE(EVENT_TS);
Show answer and explanation
Correct answer: A
Explanation
This question tests practical use of aggregate functions in Snowflake for KPI reporting. The key points are: COUNT(DISTINCT ...) is required for exact distinct-session counts; COUNT_IF(condition) is the correct aggregate for counting rows that meet a predicate; and conditional aggregation with IFF or CASE inside SUM is the standard way to include only qualifying rows in a total. In Snowflake, COUNT(expr) counts non-NULL values of the expression, which is why using COUNT(boolean_expression) is a common error. Also, APPROX_COUNT_DISTINCT is appropriate only when an approximate result is acceptable. These patterns align with Snowflake SQL function behavior documented for COUNT, COUNT_IF, SUM, conditional expressions such as IFF/CASE, and distinct-count functions.
- A. Correct.
Correct. COUNT(DISTINCT SESSION_ID) returns the daily distinct session count. COUNT_IF(EVENT_TYPE = 'PURCHASE') correctly counts only rows where the predicate is true. SUM(IFF(EVENT_TYPE = 'PURCHASE', REVENUE, 0)) sums revenue only for purchase rows and contributes 0 for all other event types, which prevents non-purchase rows from affecting the total even if their REVENUE is NULL. This produces one row per day because of the GROUP BY DATE(EVENT_TS).
- B. Incorrect.
Incorrect. COUNT(SESSION_ID) counts all non-NULL SESSION_ID values, not distinct sessions, so repeat events within the same session would be overcounted. SUM(EVENT_TYPE = 'PURCHASE') is also not the best Snowflake pattern for conditional counting in exam-style SQL because COUNT_IF is the supported aggregate specifically designed for this purpose. Most importantly, SUM(REVENUE) sums revenue across all event types, not just purchases, so it does not meet the business requirement.
- C. Incorrect.
Incorrect. APPROX_COUNT_DISTINCT(SESSION_ID) can be useful for large-scale estimation, but the scenario asks for a KPI report and does not indicate that approximation is acceptable. COUNT(REVENUE) counts non-NULL REVENUE values, not purchase events, so non-purchase rows with populated revenue would be counted and purchase rows with NULL revenue would be missed. The revenue expression itself is valid for purchase-only summation because SUM ignores NULLs, but the purchase event count is wrong.
- D. Incorrect.
Incorrect. COUNT(DISTINCT SESSION_ID) is correct for distinct sessions, but COUNT(EVENT_TYPE = 'PURCHASE') is a common mistake. COUNT(expr) counts non-NULL expression results, and the boolean expression evaluates to TRUE or FALSE rather than NULL for most rows, so it would count nearly every row, not just purchases. SUM(COALESCE(REVENUE, 0)) also sums revenue for all event types, not only purchases.