Question

A data engineer is building a Silver-layer report from a transactions table. Each row is an individual purchase with columns customer_id and amount. The business wants every original transaction row preserved, but also wants an additional column showing each customer's total spend across all their transactions. Which approach produces the required result?

Accepted Answer

Use SUM(amount) OVER (PARTITION BY customer_id) as a window aggregate alongside the existing columns

Answer

Use GROUP BY customer_id with SUM(amount) to compute the totals

Answer

Use SUM(amount) OVER (PARTITION BY customer_id ORDER BY amount) to get the total

Answer

Use collect_list(amount) grouped by customer_id, then explode the result