DAA-C01 Question 94
Single answerWindow functionsA retail analytics team stores point-of-sale transactions in a Snowflake table named SALES_TXN with the columns STORE_ID, TXN_TS, TXN_ID, CUSTOMER_ID, and AMOUNT. Analysts need a query that returns, for each store and each transaction, the transaction amount and the cumulative sales amount for that store for the current calendar day up to and including the current transaction. If two transactions in the same store have the exact same TXN_TS, the cumulative total must still be deterministic and include both rows in a consistent order. Which SQL expression best meets this requirement?
- A
SUM(AMOUNT) OVER (PARTITION BY STORE_ID, CAST(TXN_TS AS DATE) ORDER BY TXN_TS, TXN_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
- B
SUM(AMOUNT) OVER (PARTITION BY STORE_ID ORDER BY CAST(TXN_TS AS DATE) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
- C
SUM(AMOUNT) OVER (PARTITION BY STORE_ID, CAST(TXN_TS AS DATE) ORDER BY TXN_TS RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
- D
SUM(AMOUNT) OVER (ORDER BY STORE_ID, TXN_TS, TXN_ID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Show answer and explanation
Correct answer: A
Explanation
For a running total by store and calendar day, the window should partition by both STORE_ID and the date derived from TXN_TS so that totals reset correctly each day. To make the calculation deterministic when timestamps tie, the ORDER BY clause should include a unique tie-breaker such as TXN_ID. In Snowflake, ROWS frames are typically the best choice for transaction-by-transaction cumulative totals because they advance by physical row position in the specified order. By contrast, RANGE frames operate on peer groups with the same ORDER BY value and can cause multiple rows with identical timestamps to share the same cumulative total, which is often not the intended behavior. This aligns with Snowflake window function best practices: choose PARTITION BY to define reset boundaries, ORDER BY to define processing sequence, and ROWS when you need a precise running total at the row level.
- A. Correct.
Correct. This expression partitions by STORE_ID and the transaction date, so the running total resets for each store and each calendar day. It orders by TXN_TS and TXN_ID, which provides deterministic ordering when multiple transactions share the same timestamp. Using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW produces a true row-by-row cumulative sum through the current transaction.
- B. Incorrect.
Incorrect. This partitions only by STORE_ID, so totals would continue across multiple days instead of resetting each calendar day. In addition, ordering by CAST(TXN_TS AS DATE) groups all transactions on the same date together without preserving transaction-level order within the day, which does not satisfy the requirement for cumulative totals up to each transaction.
- C. Incorrect.
Incorrect. Partitioning by STORE_ID and date is appropriate, but RANGE is the wrong frame for this requirement. With RANGE and ORDER BY TXN_TS, all rows with the same TXN_TS are treated as peers and included together in the same frame, so tied timestamps would receive the same cumulative result rather than a deterministic row-by-row progression. This is a common mistake when computing running totals.
- D. Incorrect.
Incorrect. This computes a reverse-looking total from the current row to the end of the partition because the frame is CURRENT ROW AND UNBOUNDED FOLLOWING. It also does not partition by store-day, so results would span all stores and dates based on the global ORDER BY. Although TXN_ID helps make ordering deterministic, the aggregation logic does not match the business requirement.