SnowPro Associate: Platform Question 303
Single answer○ Use of SELECT statementsA data analyst needs to produce a report showing the 10 most recent orders for each customer from the SALES_ORDERS table. The table contains CUSTOMER_ID, ORDER_ID, ORDER_TS, and ORDER_TOTAL. The analyst wants a single query that returns only the latest 10 orders per customer, sorted by CUSTOMER_ID and newest ORDER_TS first. Which query should be used?
- A
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_TOTAL FROM SALES_ORDERS QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS DESC) <= 10 ORDER BY CUSTOMER_ID, ORDER_TS DESC;
- B
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_TOTAL FROM SALES_ORDERS WHERE ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS DESC) <= 10 ORDER BY CUSTOMER_ID, ORDER_TS DESC;
- C
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_TOTAL FROM SALES_ORDERS GROUP BY CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_TOTAL HAVING ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS DESC) <= 10 ORDER BY CUSTOMER_ID, ORDER_TS DESC;
- D
SELECT TOP 10 CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_TOTAL FROM SALES_ORDERS ORDER BY CUSTOMER_ID, ORDER_TS DESC;
Show answer and explanation
Correct answer: A
Explanation
This question tests practical use of SELECT statements with window functions in Snowflake. To return the top N rows within each group, a common pattern is to use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) and then filter with QUALIFY. In Snowflake, QUALIFY is evaluated after window functions, making it the correct clause for this scenario. WHERE cannot reference window function results, and TOP/LIMIT applies to the entire result set rather than each partition. This aligns with Snowflake SQL best practices and documented behavior for SELECT, window functions, and QUALIFY.
- A. Correct.
Correct. In Snowflake, the QUALIFY clause is used to filter the results of window functions such as ROW_NUMBER(). PARTITION BY CUSTOMER_ID resets the row numbering for each customer, and ORDER BY ORDER_TS DESC ranks the most recent order as 1 within each customer. Using QUALIFY ... <= 10 correctly returns the latest 10 orders per customer in a single SELECT statement.
- B. Incorrect.
Incorrect. Window functions cannot be filtered directly in the WHERE clause because WHERE is evaluated before window functions are computed. This is a common SQL mistake. In Snowflake, QUALIFY is specifically designed for filtering rows after window functions are evaluated.
- C. Incorrect.
Incorrect. Although HAVING filters after grouping, this query is not appropriate because ROW_NUMBER() is a window function, not an aggregate used with GROUP BY/HAVING in this way. Adding GROUP BY here is unnecessary and changes the query semantics without solving the per-customer top-10 requirement.
- D. Incorrect.
Incorrect. TOP 10 limits the overall result set to 10 rows for the entire query, not 10 rows per customer. Even though Snowflake supports TOP, this query would return only 10 total orders across all customers, which does not meet the reporting requirement.