Search code examples
sqlpartition-by

SQL Cumulative sum in a partition by


Consider a table with customers, sorted dates and amounts as follows

User    Date    Purchase
Joe  '2020-01-01' 10
Joe  '2020-02-01' 20
Joe  '2020-02-20' 20
Joe  '2020-03-15' 15
Lucy '2020-01-12' 5
Lucy '2020-02-15' 30
Lucy '2020-02-21' 20
Lucy '2020-03-05' 30

I would like to obtain a new column with the cumulative spent with the previous purchases, i.e.,

User    Date    Purchase Cum
Joe  '2020-01-01' 10      10
Joe  '2020-02-01' 20      30
Joe  '2020-02-20' 20      50
Joe  '2020-03-15' 15      65
Lucy '2020-01-12' 5        5
Lucy '2020-02-15' 30      35
Lucy '2020-02-21' 20      55
Lucy '2020-03-05' 30      85

Solution

  • This would be:

    select t.*,
           sum(purchase) over (partition by user order by date) as running_sum
    from t;