COF-C03 Question 88
Single answerComplex queriesA retail company stores order events in a table named ORDER_EVENTS with the columns ORDER_ID, EVENT_TS, STATUS, and CUSTOMER_ID. Analysts need a query that returns only the most recent event for each ORDER_ID, and then keeps only rows where the most recent STATUS is 'SHIPPED'. A developer proposes several SQL patterns. Which query will return the correct result in Snowflake with the simplest and most direct approach?
- A
SELECT ORDER_ID, EVENT_TS, STATUS, CUSTOMER_ID FROM ORDER_EVENTS WHERE STATUS = 'SHIPPED' QUALIFY ROW_NUMBER() OVER (PARTITION BY ORDER_ID ORDER BY EVENT_TS DESC) = 1;
- B
SELECT ORDER_ID, EVENT_TS, STATUS, CUSTOMER_ID FROM ORDER_EVENTS QUALIFY ROW_NUMBER() OVER (PARTITION BY ORDER_ID ORDER BY EVENT_TS DESC) = 1 AND STATUS = 'SHIPPED';
- C
SELECT ORDER_ID, MAX(EVENT_TS) AS EVENT_TS, STATUS, CUSTOMER_ID FROM ORDER_EVENTS WHERE STATUS = 'SHIPPED' GROUP BY ORDER_ID, STATUS, CUSTOMER_ID;
- D
SELECT ORDER_ID, EVENT_TS, STATUS, CUSTOMER_ID FROM ORDER_EVENTS WHERE ROW_NUMBER() OVER (PARTITION BY ORDER_ID ORDER BY EVENT_TS DESC) = 1 AND STATUS = 'SHIPPED';
Show answer and explanation
Correct answer: B
Explanation
This question tests a common complex-query pattern in Snowflake: selecting the latest row per business key using a window function and filtering the result correctly. The key concept is execution logic. If STATUS = 'SHIPPED' is applied in WHERE before computing ROW_NUMBER(), the query ranks only shipped rows, which can produce the wrong answer. Snowflake provides the QUALIFY clause specifically to filter rows based on window function output without requiring a subquery or CTE. A correct pattern is to compute ROW_NUMBER() OVER (PARTITION BY ORDER_ID ORDER BY EVENT_TS DESC), then QUALIFY row_number = 1, and finally ensure the retained row has STATUS = 'SHIPPED'. This aligns with Snowflake SQL best practices for simplifying complex queries involving window functions.
- A. Incorrect.
Incorrect. This filters to STATUS = 'SHIPPED' before the window function is evaluated. As a result, ROW_NUMBER() is computed only across rows that are already shipped, which can incorrectly return an older shipped event even if the true latest event for that ORDER_ID has a different status such as 'CANCELLED' or 'RETURNED'. The requirement is to first identify the latest event per order, then check whether that latest event is shipped.
- B. Correct.
Correct. In Snowflake, QUALIFY filters the results of window functions after they are computed, similar to how HAVING filters aggregate results. This query calculates ROW_NUMBER() over each ORDER_ID ordered by EVENT_TS descending, keeps only the latest row per order, and then applies STATUS = 'SHIPPED' to that latest row. This is the most direct and readable Snowflake-specific solution for this scenario.
- C. Incorrect.
Incorrect. This approach mixes aggregation with non-key columns in a way that does not guarantee the selected STATUS and CUSTOMER_ID come from the row with the maximum EVENT_TS for each ORDER_ID. Grouping by ORDER_ID, STATUS, and CUSTOMER_ID can produce multiple rows per order. Also, filtering to shipped rows before aggregation has the same logical flaw as Option 1: it ignores the possibility that the actual latest event is not shipped.
- D. Incorrect.
Incorrect. Snowflake does not allow window functions such as ROW_NUMBER() directly in the WHERE clause. Window functions are evaluated after WHERE, so this syntax is not valid. In Snowflake, QUALIFY is the correct clause to filter on window function results.