COF-C03 Question 89
Single answerComplex queriesA retail analytics team stores customer purchases in a Snowflake table SALES with columns CUSTOMER_ID, ORDER_ID, ORDER_TS, and AMOUNT. An analyst needs a query that returns only the most recent order for each customer. If two orders for the same customer have the exact same ORDER_TS, both rows should be returned. The analyst also wants the solution to be concise and avoid unnecessary nested subqueries. Which query best meets these requirements?
- A
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, AMOUNT FROM SALES QUALIFY RANK() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS DESC) = 1;
- B
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, AMOUNT FROM SALES WHERE RANK() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS DESC) = 1;
- C
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, AMOUNT FROM SALES QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS DESC) = 1;
- D
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, AMOUNT FROM SALES GROUP BY CUSTOMER_ID, ORDER_ID, ORDER_TS, AMOUNT HAVING MAX(ORDER_TS) = ORDER_TS;
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical use of complex queries in Snowflake, especially window functions and the QUALIFY clause. In Snowflake, QUALIFY is used to filter rows after window functions are evaluated, similar to how HAVING filters after aggregation. For returning the latest row per group while preserving ties, RANK() or DENSE_RANK() is appropriate; ROW_NUMBER() is only appropriate when exactly one row should be returned from each partition. The Snowflake documentation for QUALIFY and window functions highlights this pattern as a standard way to simplify queries that would otherwise require nested subqueries. Therefore, the best solution is to use QUALIFY with RANK() partitioned by CUSTOMER_ID and ordered by ORDER_TS descending.
- A. Correct.
Correct. QUALIFY filters the results of window functions in Snowflake without requiring an extra subquery. Using RANK() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS DESC) assigns rank 1 to the most recent order timestamp for each customer, and if multiple rows tie for the latest ORDER_TS, they all receive rank 1 and are returned. This matches the business requirement exactly and is the most concise Snowflake-specific solution.
- B. Incorrect.
Incorrect. In Snowflake, window functions cannot be filtered directly in the WHERE clause because WHERE is evaluated before window functions are computed. This is a common mistake for users familiar with filtering regular expressions or aggregates. Snowflake provides QUALIFY specifically for filtering on window function results.
- C. Incorrect.
Incorrect. QUALIFY is the right clause to filter window function output, but ROW_NUMBER() would return only one row per customer, even when multiple orders share the same latest ORDER_TS. Because the requirement states that all tied latest orders must be returned, ROW_NUMBER() does not satisfy the scenario.
- D. Incorrect.
Incorrect. Although this query attempts to compare each row to the maximum timestamp, the GROUP BY includes all selected columns, so MAX(ORDER_TS) is evaluated per grouped row rather than per customer. As written, it does not identify the most recent order per customer. A correct aggregate-based approach would require a separate subquery or join on CUSTOMER_ID and MAX(ORDER_TS), which is less concise than using QUALIFY with a ranking function.