Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Easy
A data analyst is working with a large Spark Delta table named `customer_transactions` in Microsoft Fabric. The table contains columns like `transaction_id`, `customer_id`, `transaction_date`, and `amount`. The analyst needs to calculate the cumulative sum of `amount` for each `customer_id`, ordered by `transaction_date`. Which Spark SQL window function should the analyst use to achieve this?
- AROW_NUMBER()
- BNTILE()
- CSUM() OVER (PARTITION BY ... ORDER BY ...)
- DLAG()
Show answer & explanationAnswer & explanation
Correct answer: C. SUM() OVER (PARTITION BY ... ORDER BY ...)
The `SUM() OVER (PARTITION BY ... ORDER BY ...)` window function is specifically designed to calculate cumulative sums within partitions. `PARTITION BY customer_id` ensures the sum restarts for each customer, and `ORDER BY transaction_date` ensures the sum accumulates chronologically.
Why the other options are wrong
- A. `ROW_NUMBER()` assigns a unique sequential integer to each row within its partition, not a cumulative sum.
- B. `NTILE()` divides rows into a specified number of groups, not for calculating sums.
- D. `LAG()` retrieves a value from a previous row in the same partition, not a cumulative sum.
Spark SQL Cumulative Sum
A calculation that computes the running total of a numeric column within a defined window (partition and order) in Spark SQL.
- Uses the `SUM()` aggregate function with an `OVER()` clause.
- `PARTITION BY` defines the groups for which the sum restarts.
- `ORDER BY` defines the sequence in which the sum accumulates within each group.
Memory trick: Windows frame how you view the data.