SnowPro Associate: Platform Question 302
Single answer○ Use of SELECT statementsA data analyst needs to return one row per customer from a Snowflake table named ORDERS. For each customer, the analyst wants the most recent order based on ORDER_TS, and the result should include CUSTOMER_ID, ORDER_ID, ORDER_TS, and TOTAL_AMOUNT. If two orders for the same customer have the exact same ORDER_TS, returning either of those tied rows is acceptable. Which SELECT statement best meets this requirement?
- A
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, TOTAL_AMOUNT FROM ORDERS QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS DESC) = 1;
- B
SELECT CUSTOMER_ID, MAX(ORDER_TS) AS ORDER_TS, ORDER_ID, TOTAL_AMOUNT FROM ORDERS GROUP BY CUSTOMER_ID;
- C
SELECT DISTINCT CUSTOMER_ID, ORDER_ID, ORDER_TS, TOTAL_AMOUNT FROM ORDERS ORDER BY ORDER_TS DESC;
- D
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, TOTAL_AMOUNT FROM ORDERS WHERE MAX(ORDER_TS) OVER (PARTITION BY CUSTOMER_ID) = ORDER_TS;
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a window function together with QUALIFY. In Snowflake, QUALIFY filters the results of window functions after those functions are computed, making it ideal for scenarios such as returning the latest row per customer. The pattern ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) = 1 is widely used for this requirement. GROUP BY with MAX() is suitable for returning the maximum value itself, but not the other non-aggregated columns from the same row unless additional logic is used. DISTINCT does not solve a greatest-per-group problem. Also, Snowflake does not allow window functions directly in the WHERE clause; QUALIFY is the intended feature for that use case. This aligns with Snowflake documentation on SELECT, window functions, and the QUALIFY clause.
- A. Correct.
Correct. ROW_NUMBER() assigns a sequence within each CUSTOMER_ID partition ordered by ORDER_TS descending, so the most recent row gets 1. QUALIFY filters on the result of the window function in the same SELECT statement, which is a common Snowflake pattern for top-N per group queries. Because ties are acceptable, ROW_NUMBER() is appropriate even though it picks one of the tied rows arbitrarily unless a secondary sort key is added.
- B. Incorrect.
Incorrect. This query is not valid as written because ORDER_ID and TOTAL_AMOUNT are selected without being aggregated or included in the GROUP BY clause. More importantly, even if it were rewritten, a simple GROUP BY with MAX(ORDER_TS) does not automatically return the full row associated with that maximum timestamp.
- C. Incorrect.
Incorrect. DISTINCT removes duplicate full rows, not duplicate customers. Ordering the result by ORDER_TS DESC does not ensure only the latest row per CUSTOMER_ID is returned. This is a common misconception when trying to get one row per business key.
- D. Incorrect.
Incorrect. This uses a window function in the WHERE clause, which is not allowed in Snowflake. Window function results must be filtered using QUALIFY, or by nesting the SELECT in a subquery/CTE and then applying a WHERE filter in the outer query.