DAA-C01 Question 91
Single answerAggregate functionsA retail analytics team stores web events in a Snowflake table WEB_EVENTS with the columns SESSION_ID, EVENT_TS, EVENT_TYPE, and REVENUE. They need a daily KPI query that returns one row per calendar day with: (1) the total number of purchase events, (2) the number of distinct sessions that had at least one purchase, and (3) the average revenue per purchase event. Some purchase rows have REVENUE = NULL because the tracking pixel failed, and these rows should still count as purchase events but should not contribute to the revenue average. Which query correctly produces the required metrics?
- A
SELECT DATE_TRUNC('DAY', EVENT_TS) AS EVENT_DAY, COUNT_IF(EVENT_TYPE = 'PURCHASE') AS PURCHASE_EVENTS, COUNT(DISTINCT IFF(EVENT_TYPE = 'PURCHASE', SESSION_ID, NULL)) AS PURCHASE_SESSIONS, AVG(IFF(EVENT_TYPE = 'PURCHASE', REVENUE, NULL)) AS AVG_PURCHASE_REVENUE FROM WEB_EVENTS GROUP BY 1;
- B
SELECT DATE_TRUNC('DAY', EVENT_TS) AS EVENT_DAY, COUNT(EVENT_TYPE = 'PURCHASE') AS PURCHASE_EVENTS, COUNT(DISTINCT SESSION_ID) AS PURCHASE_SESSIONS, AVG(REVENUE) AS AVG_PURCHASE_REVENUE FROM WEB_EVENTS WHERE EVENT_TYPE = 'PURCHASE' AND REVENUE IS NOT NULL GROUP BY 1;
- C
SELECT DATE(EVENT_TS) AS EVENT_DAY, SUM(IFF(EVENT_TYPE = 'PURCHASE', 1, 0)) AS PURCHASE_EVENTS, APPROX_COUNT_DISTINCT(IFF(EVENT_TYPE = 'PURCHASE', SESSION_ID, NULL)) AS PURCHASE_SESSIONS, AVG(COALESCE(REVENUE, 0)) AS AVG_PURCHASE_REVENUE FROM WEB_EVENTS GROUP BY 1;
- D
SELECT DATE_TRUNC('DAY', EVENT_TS) AS EVENT_DAY, COUNT(*) FILTER (WHERE EVENT_TYPE = 'PURCHASE') AS PURCHASE_EVENTS, COUNT(DISTINCT SESSION_ID) FILTER (WHERE EVENT_TYPE = 'PURCHASE') AS PURCHASE_SESSIONS, AVG(REVENUE) FILTER (WHERE EVENT_TYPE = 'PURCHASE') AS AVG_PURCHASE_REVENUE FROM WEB_EVENTS GROUP BY 1;
Show answer and explanation
Correct answer: A
Explanation
This question tests conditional aggregation in Snowflake. For applied KPI queries, candidates must know how aggregate functions interact with NULL values and how to conditionally include rows without accidentally changing the aggregation grain. In Snowflake, COUNT_IF is the standard function for counting rows that satisfy a predicate. COUNT(expr) counts non-NULL expressions, not only rows where a boolean expression is TRUE, which is a common mistake. AVG ignores NULLs, so using IFF or CASE to return REVENUE only for purchase rows and NULL otherwise is the correct way to compute average purchase revenue while excluding missing values from the average. For distinct counts, wrapping SESSION_ID in IFF/CASE ensures only purchase-session IDs participate in the COUNT DISTINCT. Snowflake documentation on aggregate functions, COUNT_IF, COUNT, AVG, and conditional expressions supports these behaviors and the recommended pattern for conditional aggregation.
- A. Correct.
Correct. COUNT_IF(EVENT_TYPE = 'PURCHASE') counts only rows where the predicate is true, so purchase rows with REVENUE = NULL are still included in the event count. COUNT(DISTINCT IFF(EVENT_TYPE = 'PURCHASE', SESSION_ID, NULL)) counts distinct SESSION_ID values only for purchase rows because non-purchase rows become NULL and are ignored by COUNT DISTINCT. AVG(IFF(EVENT_TYPE = 'PURCHASE', REVENUE, NULL)) averages revenue only for purchase rows, and because AVG ignores NULLs, purchase rows with missing REVENUE do not affect the average denominator or numerator. This matches all stated requirements.
- B. Incorrect.
Incorrect. There are two issues. First, COUNT(EVENT_TYPE = 'PURCHASE') does not count only true values in Snowflake; it counts non-NULL evaluated expressions, so both TRUE and FALSE boolean results are counted when not NULL, which would overcount. Second, the WHERE clause filters out purchase rows with REVENUE IS NULL, but the requirement says those rows must still count as purchase events and sessions. Filtering them out changes both the event count and the distinct-session count.
- C. Incorrect.
Incorrect. SUM(IFF(EVENT_TYPE = 'PURCHASE', 1, 0)) can correctly count purchase events, but the rest does not meet the requirements. APPROX_COUNT_DISTINCT returns an estimate rather than the exact number of distinct sessions, which is usually inappropriate for a KPI that requires an exact count unless approximation is explicitly acceptable. Also, AVG(COALESCE(REVENUE, 0)) incorrectly treats NULL revenue as 0 for all rows in the group, including non-purchase rows unless separately filtered, which biases the average downward and violates the requirement that NULL revenue should not contribute to the average.
- D. Incorrect.
Incorrect. Although this syntax is valid in some SQL dialects, Snowflake does not support the aggregate FILTER (WHERE ...) clause in this form. In Snowflake, the idiomatic approach is to use COUNT_IF for conditional counts and expressions such as IFF/CASE inside aggregate functions for conditional aggregation. The logic of the option is conceptually sound, but the syntax is not appropriate for Snowflake.