How to calculate payments by a certain period.

Specifically, I wonder if the user registered on the x date, how much payment he has in 30 days from that date

There are two tables:

create table user (user_id, contact, registration_date) as
    select 1,   111 111, 1/18/2022 3:57:32 PM from dual union all
    select 2, 222 222,    8/12/2021 12:00:12 AM from dual union all
    select 3, 333 333,  12/11/2015 5:08:35 PM  from dual union all
    select 4, 444 444,  5/25/2020 10:59:10 AM  from dual;



create table transaction (day, user_id, payment) as
 select   1/20/2022,   1, 5 from dual union all
 select    1/30/2022, 1,  8  from dual union all
 select   2/20/2022, 1,  6  from dual union all
 select  8/12/2021 , 2,  10  from dual union all
 select  8/15/2021 , 2,  5  from dual union all
 select  9/25/2021 , 2,  12  from dual union all
 select  12/11/2015 , 3,  18  from dual union all
 select  12/20/2015 , 3,  10  from dual union all
 select  1/1/2016 , 3,  10  from dual union all
 select  5/26/2020 , 4,  7  from dual union all
select  6/1/2020 , 4,  2  from dual;

I wonder something like this, but this query does not work,

select t.user_id,u.registration_date,sum(t.payment) from transaction t
left join user u on t.user_id = u.user_id
where t.day >= u.registration_date and t.day <= u.registration_date   interval '30' days
group by t.user_id

My expected table:

CodePudding user response：

First of all, you probably want to outer join the transactions to the users not vice versa. Thus you also show users with no trasactions in the 30 days after registration.

Then, Oracle requires you to either put u.registration_date in GROUP BY or pseudo aggregate it (e.g. MIN(u.registration_date)). This doesn't comply with standard SQL, but it seems that Oracle hasn't mananged yet to properly detect functional dependencies, so they simply don't offer this feature.

At last, the registration date, despite its name, is not a date but a datetime. In order to compare it with the transaction date, truncate it. Then decide whether you want to include the 30th after the registration date or not (i.e. either add 30 or 31 days and use < anyway).

select u.user_id, u.registration_date, sum(t.payment) 
from users u
left join transactions t
       on t.user_id = u.user_id
       and t.day >= trunc(u.registration_date) 
       and t.day < trunc(u.registration_date)   interval '31' day
group by u.user_id, u.registration_date
order by u.user_id;

Demo: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=a6d8d13c994eff6a1c0f8afa6fcb176f

CodePudding user response：

Looks like

SQL>   SELECT u.user_id, u.registration_date, SUM (t.payment) payment
  2      FROM tuser u JOIN transaction t ON t.user_id = t.user_id
  3     WHERE t.day BETWEEN u.registration_date
  4                     AND u.registration_date   INTERVAL '30' DAY
  5  GROUP BY u.user_id, u.registration_date
  6  ORDER BY u.user_id;

   USER_ID REGISTRATI    PAYMENT
---------- ---------- ----------
         1 01/18/2022         13
         2 08/12/2021         15
         3 12/11/2015         38
         4 05/25/2020          9

SQL>

CodePudding user response：

Your query doesn't work because you forgot to add the u.registration_date in your GROUP BY clause. When you do a GROUP BY, all fields mentioned in the SELECT need to be either added to the GROUP BY or be used in a aggregate function like SUM, MIN, MAX, ...