DAA-C01 Question 150
Single answerApply analytic/window functionsA retail analytics team stores order events in a Snowflake table named ORDER_EVENTS with the columns CUSTOMER_ID, ORDER_ID, ORDER_TS, and ORDER_AMOUNT. Analysts need a query that returns every order along with the amount from the customer's immediately previous order, based on ORDER_TS. If a customer has no prior order, the previous amount should be NULL. Which SQL statement best meets this requirement?
- A
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_AMOUNT, LAG(ORDER_AMOUNT) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS) AS PREV_ORDER_AMOUNT FROM ORDER_EVENTS;
- B
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_AMOUNT, LEAD(ORDER_AMOUNT) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS) AS PREV_ORDER_AMOUNT FROM ORDER_EVENTS;
- C
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_AMOUNT, FIRST_VALUE(ORDER_AMOUNT) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS PREV_ORDER_AMOUNT FROM ORDER_EVENTS;
- D
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_AMOUNT, LAG(ORDER_AMOUNT) OVER (ORDER BY ORDER_TS) AS PREV_ORDER_AMOUNT FROM ORDER_EVENTS;
Show answer and explanation
Correct answer: A
Explanation
This scenario is a classic use case for Snowflake analytic/window functions. To access the immediately preceding row's value within a logical group, use LAG with an OVER clause that includes both PARTITION BY and ORDER BY. PARTITION BY CUSTOMER_ID isolates each customer's history, and ORDER BY ORDER_TS establishes row sequence within that customer. Snowflake's LAG function returns NULL when no prior row exists unless a default value is supplied, which aligns with the requirement. Best practice is to choose the window function that directly expresses the business need rather than approximating it with functions like FIRST_VALUE. Refer to Snowflake documentation for window functions, particularly LAG, LEAD, and window partitioning/ordering behavior.
- A. Correct.
Correct. LAG returns a value from a prior row within the specified window. Partitioning by CUSTOMER_ID ensures each customer's order history is evaluated independently, and ordering by ORDER_TS defines the sequence of orders. For the first order in each customer partition, LAG returns NULL by default, which matches the requirement.
- B. Incorrect.
Incorrect. LEAD looks forward to a subsequent row, not backward to a prior row. This would return the next order amount for the customer rather than the previous one. It is a common mistake when analysts confuse directional analytic functions.
- C. Incorrect.
Incorrect. FIRST_VALUE returns the first value in the defined window frame, not necessarily the immediately previous row. With a frame of 1 PRECEDING to CURRENT ROW, FIRST_VALUE would return the earlier of the two rows in the frame, but for many rows that still does not reliably represent the prior row in the intended way and is semantically the wrong function for this use case.
- D. Incorrect.
Incorrect. Although LAG is the right function, omitting PARTITION BY CUSTOMER_ID causes the previous order amount to be taken from the prior row across all customers, not the prior order for the same customer. This would produce incorrect results whenever different customers' rows are interleaved in timestamp order.