DAA-C01 Question 149
Single answerApply analytic/window functionsA retail analytics team stores order events in a Snowflake table named ORDER_EVENTS with the columns CUSTOMER_ID, ORDER_ID, ORDER_TS, and ORDER_AMOUNT. Analysts need a query that returns only the first order placed by each customer, based on the earliest ORDER_TS. If two orders for the same customer have the exact same ORDER_TS, the order with the smaller ORDER_ID must be chosen to ensure deterministic results. Which query best meets this requirement?
- A
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_AMOUNT FROM ORDER_EVENTS QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS, ORDER_ID) = 1;
- B
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_AMOUNT FROM ORDER_EVENTS WHERE RANK() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS) = 1;
- C
SELECT CUSTOMER_ID, MIN(ORDER_ID), MIN(ORDER_TS), ORDER_AMOUNT FROM ORDER_EVENTS GROUP BY CUSTOMER_ID, ORDER_AMOUNT;
- D
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_AMOUNT FROM ORDER_EVENTS QUALIFY FIRST_VALUE(ORDER_ID) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS) = ORDER_ID;
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use ROW_NUMBER() with PARTITION BY CUSTOMER_ID and ORDER BY ORDER_TS, ORDER_ID, then filter with QUALIFY = 1. In Snowflake, QUALIFY is specifically designed to filter the results of window functions after they are computed, similar to how HAVING filters aggregated results. This makes it ideal for top-N-per-group and first-row-per-group problems. Using ROW_NUMBER() is preferable here because the business requirement demands exactly one row per customer, and adding ORDER_ID as a secondary sort key ensures deterministic tie-breaking. By contrast, RANK() and DENSE_RANK() can return multiple rows when ties occur. Snowflake documentation for window functions and the QUALIFY clause supports this pattern as a best practice for analytic filtering.
- A. Correct.
Correct. ROW_NUMBER() assigns a unique sequential number within each CUSTOMER_ID partition based on the specified ORDER BY clause. By ordering on ORDER_TS and then ORDER_ID, the query deterministically selects exactly one earliest order per customer, even when multiple orders share the same timestamp. QUALIFY is the appropriate Snowflake construct for filtering on window function results without requiring a subquery.
- B. Incorrect.
Incorrect. RANK() is a window function, but it cannot be used directly in the WHERE clause in Snowflake because WHERE is evaluated before window functions. Even if rewritten with QUALIFY, RANK() ordered only by ORDER_TS could return multiple rows for the same customer when timestamps tie, which violates the requirement to return exactly one row.
- C. Incorrect.
Incorrect. This aggregates columns independently and can produce mismatched values that did not occur together in a single source row. For example, MIN(ORDER_ID) and MIN(ORDER_TS) might come from different orders, and grouping by ORDER_AMOUNT can return multiple rows per customer. This does not reliably identify the first order event.
- D. Incorrect.
Incorrect. FIRST_VALUE() returns the first ORDER_ID within the window frame, but this approach is incomplete for two reasons. First, the ORDER BY does not include ORDER_ID as a tiebreaker, so tied timestamps are not resolved deterministically. Second, using FIRST_VALUE() to filter rows can work in some cases, but ROW_NUMBER() is the more direct and reliable pattern when exactly one row per partition is required.