Calculate the number of days that the field value has not changed

Question

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)

Answer 1

In order to answer your question, I did the following (all of the code below is available on the fiddle here):

CREATE TABLE test
(
  t_user TEXT    NOT NULL,
  t_date DATE    NOT NULL,
  amount INTEGER NOT NULL
);

populate:

INSERT INTO test VALUES
('John', '2022-01-01',  40),
('John', '2022-02-01', -30),
('John', '2022-06-01',  50);

First pass:

I've left in a couple of extra fields so that you can follow the logic - I've removed them for the final pass.

SELECT
  t_user, 
  t_date, 

  CASE
    WHEN LEAD(t_date) OVER w IS NULL
      THEN current_date
    ELSE LEAD(t_date) OVER w
  END AS ld,
  
  CASE
    WHEN LEAD(t_date) OVER w is null
      THEN current_date - t_date
    ELSE LEAD(t_date) OVER w - t_date
  END AS d_cnt,
  amount,
  
  SUM(amount) OVER (PARTITION BY t_user ORDER BY t_date) AS rr_tot

FROM
  test
WINDOW w AS (PARTITION BY t_user ORDER BY t_date)
ORDER BY t_user DESC, t_date;

Result:

t_user      t_date          ld  d_cnt  amount  s_tot
John    2022-01-01  2022-02-01     31      40     40
John    2022-02-01  2022-06-01    120     -30     10
John    2022-06-01  2022-08-23     83      50     60

• r_tot (running total) keeps track of the total at each date range, necessary for the multiplication step (r_tot * d_cnt) in the final pass

• the WINDOW w clause is just a little syntactic sugar that (really) helps with legibility

Final pass:

SELECT
  t_user, 
  SUM(r_tot * d_cnt)
FROM
(
  SELECT
    t_user, 
    CASE
      WHEN LEAD(t_date) OVER w is null
        THEN current_date - t_date
      ELSE LEAD(t_date) OVER w - t_date
    END AS d_cnt,
    amount,
    SUM(amount) OVER (PARTITION BY t_user ORDER BY t_date) AS r_tot
  FROM
    test
  WINDOW w AS (PARTITION BY t_user ORDER BY t_date)
  
) AS sub1
GROUP BY t_user
ORDER BY t_user;

Result:

t_user   sum
John    7420

A couple of points to note:

• please use ISO 8601 standard dates - that format is YYYY-MM-DD - it also makes your code more portable, universally understood and unambiguous. Standards are there for a reason and should be used unless you have a very good reason not to!

• in future, could you please provide a fiddle with your tables and data? This gives a single point of truth (SPOT) for the question and saves on duplication of effort on behalf of those trying to help you

• don't use keywords (and/or reserved words) like Date and User either as column or table names - it makes your SQL less portable and less readable and more error-prone