SnowPro Associate: Platform Question 38
Single answer○ SQLA data engineer needs to produce a daily customer status snapshot in Snowflake. The source table CUSTOMER_EVENTS contains multiple records per CUSTOMER_ID as customers change status over time. Each row has CUSTOMER_ID, STATUS, and UPDATED_AT. The requirement is to return exactly one row per CUSTOMER_ID representing the most recent status based on UPDATED_AT, and to do so in a single SQL statement without using a subquery. Which SQL approach best meets this requirement?
- A
Use ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY UPDATED_AT DESC) in the SELECT list and filter with QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY UPDATED_AT DESC) = 1
- B
Use GROUP BY CUSTOMER_ID and select STATUS with MAX(UPDATED_AT) in the same query
- C
Use DISTINCT on CUSTOMER_ID, STATUS, UPDATED_AT and order by UPDATED_AT DESC
- D
Use LIMIT 1 after ordering the full table by UPDATED_AT DESC
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a window function with QUALIFY. In Snowflake, QUALIFY allows filtering based on window function results without wrapping the query in a subquery. For this scenario, ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY UPDATED_AT DESC) identifies the most recent row within each customer's event history, and QUALIFY ... = 1 returns exactly one latest row per customer. This pattern is widely used in Snowflake SQL for deduplication and latest-record selection. By contrast, GROUP BY with MAX(UPDATED_AT) does not safely return non-aggregated columns from the same row, DISTINCT does not solve per-group latest-row selection, and LIMIT applies to the full result set rather than each partition. This aligns with Snowflake SQL best practices for analytic functions and QUALIFY usage.
- A. Correct.
Correct. In Snowflake, QUALIFY filters the results of window functions after they are computed, which makes it ideal for returning the latest row per CUSTOMER_ID in a single statement. Using ROW_NUMBER() partitioned by CUSTOMER_ID and ordered by UPDATED_AT descending assigns rank 1 to the most recent event for each customer. QUALIFY ROW_NUMBER() ... = 1 then keeps only that latest row per customer. This is a common Snowflake SQL pattern and avoids the need for a subquery or self-join.
- B. Incorrect.
Incorrect. GROUP BY CUSTOMER_ID with MAX(UPDATED_AT) can identify the latest timestamp per customer, but STATUS is not functionally guaranteed to match that MAX(UPDATED_AT) unless an additional join or qualifying logic is used. Selecting STATUS directly alongside an aggregate without properly resolving which row it comes from is either invalid SQL or logically incorrect depending on how it is written. This option reflects a common misconception that aggregates automatically return values from the same row as the maximum timestamp.
- C. Incorrect.
Incorrect. DISTINCT removes duplicate combinations of the selected columns, but it does not choose the latest row per CUSTOMER_ID. If a customer has multiple different statuses or timestamps, DISTINCT will still return multiple rows for that customer. Ordering the result does not change the number of rows returned. This is a frequent misunderstanding of DISTINCT versus row-ranking logic.
- D. Incorrect.
Incorrect. LIMIT 1 returns only one row for the entire result set, not one row per CUSTOMER_ID. Even if the table is ordered by UPDATED_AT descending, LIMIT 1 would only return the single most recent event across all customers. This fails the requirement to return exactly one row for every customer.