Please consider the following table:
User | Date | amount |
---|---|---|
John | 1/1/2022 | 40 |
John | 1/2/2022 | -30 |
John | 1/6/2022 | 50 |
... | ... | ... |
Jack | 4/1/2022 | 80 |
Jack | 4/1/2022 | -60 |
We have three records for John and two records for Jack. In each of the dates, the user either added a value to the amount field (positive values) or subtracted from it (negative values).
The number of days that the sum of the amount has not changed is important.
For example, from 1/1/2022 to 1/2/2022, dear John had a value of 40. But on 1/2/2022, the amount was reduced by 30. Therefore, for 30 days (from 1/1 to 1/2), the amount was equal to 40, and again until 1/6, the total amount (40 - 30) remained unchanged for about 120 days. And finally, until today (today is 8/23), the sum of the amount will be equal to 40 - 30 + 50 = 60.
Question:
- How can I calculate these intervals to get the number of days?
- How can you get these intervals for all users?
I read about Window or CTE but to no avail.
Why did I ask this question?
The number of database records has increased to about one million, and for 21,000 users, we must obtain the length of each interval and multiply it by the total amount of the previous records.
Expected Output:
For example, for John, I expect the following output:
40 × 30(days) + (40 - 30) × 60 (days) + (40 - 30 + 50) × 53 (days)