SnowPro Associate: Platform Question 300
Single answer○ Use of SELECT statementsA data analyst needs a list of unique customer IDs from the SALES table for orders placed in 2024. The analyst also wants the result sorted by the most recent order date for each customer, with customers who ordered most recently appearing first. Which SELECT statement will correctly return the required result in Snowflake?
- A
SELECT DISTINCT customer_id FROM sales WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' ORDER BY order_date DESC;
- B
SELECT customer_id, MAX(order_date) AS last_order_date FROM sales WHERE YEAR(order_date) = 2024 GROUP BY customer_id ORDER BY last_order_date DESC;
- C
SELECT DISTINCT customer_id, MAX(order_date) FROM sales WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' ORDER BY MAX(order_date) DESC;
- D
SELECT customer_id FROM sales WHERE order_date >= '2024-01-01' QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1 ORDER BY order_date DESC;
Show answer and explanation
Correct answer: B
Explanation
The correct approach is to aggregate at the customer level and compute the most recent 2024 order date for each customer, then sort by that value. In Snowflake, SELECT statements that combine aggregated and non-aggregated columns require a GROUP BY for the non-aggregated columns. Using MAX(order_date) with GROUP BY customer_id is a standard pattern for this requirement. Option 2 correctly applies filtering, aggregation, and ordering. Snowflake documentation for SELECT, GROUP BY, ORDER BY, and QUALIFY supports these behaviors. Although QUALIFY is often used in Snowflake to filter window function results such as ROW_NUMBER(), the query must still correctly constrain the date range and return the required result set.
- A. Incorrect.
Incorrect. Although DISTINCT returns unique customer_id values, this query attempts to ORDER BY order_date, which is not selected and is not valid in this context with DISTINCT for the required per-customer most recent date. Even if allowed in some SQL dialects, it would not correctly sort customers by their latest 2024 order because there may be multiple order_date values per customer.
- B. Correct.
Correct. This query filters rows to 2024, groups by customer_id, calculates the latest order date for each customer using MAX(order_date), and sorts by that derived value in descending order. This directly matches the requirement to return one row per customer and order the customers by their most recent 2024 order date.
- C. Incorrect.
Incorrect. This query mixes DISTINCT with an aggregate function MAX(order_date) without a GROUP BY clause. In Snowflake, when a non-aggregated column like customer_id appears with an aggregate, the query must use GROUP BY for the non-aggregated column. This is a common mistake when trying to get unique rows and aggregated values at the same time.
- D. Incorrect.
Incorrect. The QUALIFY clause with ROW_NUMBER() is a valid Snowflake pattern for returning the latest row per customer, but this query does not restrict the upper bound of the date range, so it includes orders after 2024. In addition, it orders by order_date even though that column is not projected, making the intent less clear than the grouped solution. The missing 2025 upper boundary is the key reason it does not satisfy the requirement.