I still cannot find the solution for cumulative problem. Anyone can help or suggest any solution. Many thanks.

I have 2 tables: transactions (date, user_id, price, service group) and point (service group, point). The point user get is price * point.

I left join transactions and point table then I calculated the point of each user and add it in a new column.

I use window function sum(new_point) over(partition by user_id order by date) to calculate cummulative point but I'm stucked about expired point after 30 days.

The required: calculate cumulative point of each user on daily basis. (After 30 days point will be expired since the day that transaction was made)

E.g: Transaction of user A:

1/1/2020: 20, 80
2/1/2020: 10
3/1/2020: 9
1/2/2020: 10
2/2/2020: 20
3/2/2020: 30

=> result like this

 1/1/2020: 20   80 = 100
2/1/2020: 100   10 = 110
3/1/2020: 110   9 = 119
1/2/2020:  119   10 = 129
2/2/2020:  29   20 = 49 (point of 1/1/2020 expired)
3/2/2020: 39   30 = 69 (point of 2/1/2020 expired)

CodePudding user response：

1. Tested on dbfiddle
2. Please let me know if I have misunderstood your question.

WITH new_sum AS (
SELECT 
  t.user_id, t.date, t.price, p.point,
  t.price COALESCE(p.point,0) AS new_point, 
  SUM(t.price COALESCE(p.point,0)) OVER(PARTITION BY user_id ORDER BY date) AS running_sum
FROM transactions t
LEFT JOIN point p ON t.service_group = p.service_group
),
expired AS (
SELECT 
  user_id,
  DATE_ADD(DATE_ADD(date, INTERVAL 1 MONTH),INTERVAL 1 DAY) AS date, 
  running_sum AS expired_points
FROM new_sum
)

SELECT 
  s.user_id, s.date, 
  s.running_sum - COALESCE(e.expired_points,0) AS final
FROM new_sum s
LEFT JOIN expired e
ON s.user_id = e.user_id
AND s.date = e.date;

CodePudding user response：

You can use a range in the window function for this, by interpreting the date as a number:

select date, sum(point) over (order by datediff(date,'1970-01-01') asc range between 32 preceding and 0 following) point
from (
    select date, sum(price*point) point
    from transactions
    join point using (service_group)
    where user_id=1
    group by date
) user_points

Note that you seem to be using a 32 day expiration, not 30 days, in your example.

fiddle