0

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)
2
  • Welcome to the DBA.SE community. Could you possibly update (edit)your question and provide the community with an expected result set? This would greatly increase your possibility of receiving an answer. Thanks.
    – John K. N.
    Commented Aug 23, 2022 at 7:01
  • Thanks @JohnK.N. I updated the question
    – Saeed
    Commented Aug 23, 2022 at 7:08

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.