COF-C03 Question 308
Single answerWindow functionsA retail analytics team stores order data in a Snowflake table named ORDERS with the columns CUSTOMER_ID, ORDER_ID, ORDER_TS, and ORDER_TOTAL. The team needs a query that returns every order along with the previous order total for the same customer, based on order timestamp. If a customer has no previous order, the query should return NULL for the previous order total. Which SQL statement will meet this requirement?
- A
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_TOTAL, LAG(ORDER_TOTAL) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS) AS PREV_ORDER_TOTAL FROM ORDERS;
- B
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_TOTAL, LEAD(ORDER_TOTAL) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS) AS PREV_ORDER_TOTAL FROM ORDERS;
- C
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_TOTAL, LAG(ORDER_TOTAL) OVER (ORDER BY ORDER_TS) AS PREV_ORDER_TOTAL FROM ORDERS;
- D
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, ORDER_TOTAL, ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_TS) AS PREV_ORDER_TOTAL FROM ORDERS;
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical use of Snowflake window functions. To retrieve a prior value from another row while keeping all rows in the result set, the appropriate function is LAG. The correct pattern is to use OVER with both PARTITION BY and ORDER BY: PARTITION BY CUSTOMER_ID isolates each customer's order history, and ORDER BY ORDER_TS defines the sequence within that history. In Snowflake, LAG(expr) returns the value from the previous row in the ordered window and returns NULL when no previous row exists unless a default is specified. This is a standard analytic SQL pattern documented in Snowflake's window function and LAG function references. The key applied concept is that window functions do not collapse rows like aggregate functions; instead, they compute values across related rows while preserving row-level detail.
- A. Correct.
Correct. LAG returns a value from a previous row within the window frame. By partitioning on CUSTOMER_ID, the calculation is reset for each customer, and ordering by ORDER_TS ensures the previous value is based on the order sequence for that customer. When there is no prior row in the partition, LAG returns NULL by default, which matches the requirement.
- B. Incorrect.
Incorrect. LEAD looks ahead to a subsequent row, not back to a previous row. A candidate might choose this if they confuse LEAD and LAG, but this option would return the next order total for the same customer rather than the previous one.
- C. Incorrect.
Incorrect. Although LAG is the correct function conceptually, this query does not partition by CUSTOMER_ID. As a result, the previous order total could come from a different customer if their order appears immediately before the current row in timestamp order. This is a common mistake when using window functions on grouped business entities.
- D. Incorrect.
Incorrect. ROW_NUMBER assigns a sequential number to each row within a partition; it does not return a prior row's column value. Someone might select this if they think numbering rows can be used directly to identify previous rows, but by itself it does not satisfy the requirement to output the previous ORDER_TOTAL.