Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data analyst is querying a large Delta table named `product_sales` in Microsoft Fabric, which contains columns `product_id` (int), `sale_date` (date), and `revenue` (decimal). They need to calculate the running total of revenue for each product, ordered by `sale_date`. Which Spark SQL window function should be used to achieve this?
- ASUM(revenue) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
- BSUM(revenue) OVER (PARTITION BY product_id ORDER BY sale_date)
- CSUM(revenue) OVER (PARTITION BY product_id)
- DSUM(revenue) OVER (ORDER BY sale_date)
Show answer & explanationAnswer & explanation
Correct answer: A. SUM(revenue) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
To calculate a running total, you need to sum values from the beginning of the partition up to the current row. The `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` clause explicitly defines this window frame, ensuring the sum accumulates correctly for each product ordered by sale date.
Why the other options are wrong
- B. This syntax implies a default window frame, which often defaults to `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` for aggregates, but `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` is more explicit and safer for running totals.
- C. This calculates the total revenue for each product across all dates, not a running total, because it lacks the `ORDER BY` clause and an explicit window frame for accumulation.
- D. This calculates a running total across all products, not for each individual product, because it lacks the `PARTITION BY product_id` clause.
Spark SQL Running Total
A running total, also known as a cumulative sum, is an aggregate calculation that adds each new value to the sum of previous values within a defined group and order.
- Uses a window function with `SUM()`.
- Requires `PARTITION BY` to group data (e.g., per product).
- Requires `ORDER BY` to define the accumulation sequence (e.g., by date).
- Uses `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` to define the accumulating window frame.
Memory trick: Partition to group, order to sequence, frame to accumulate!