How to select rows where logged in last month and logged min 1 time in one of month preceding August-CodePudding

I have table in Oracle SQL presents ID of clients and date with time of their login to application:

ID | LOGGED
----------------
11 | 2021-07-10 12:55:13.278
11 | 2021-08-10 13:58:13.211
11 | 2021-02-11 12:22:13.364
22 | 2021-01-10 08:34:13.211
33 | 2021-04-02 14:21:13.272

I need to select only these clients (ID) who has logged minimum 1 time in last month (August) and minimum 1 time in one month preceding August (June or July)

Currently we have September, so...
I need clients who has logged min 1 time in August
and min 1 time in July or Jun,
if logged in June -> not logg in July
if logged in July -> not logged in June

As a result I need like below:

ID
----
11

How can do that in Oracle SQL ? be aware that column "LOGGED" has Timestamp like: 2021-01-10 08:34:13.211

CodePudding user response：

May be you consider this:

select id
                    
               
 from yourtable
 group by id 
 having count(case 
                  months_between(trunc(sysdate,'MM'), 
                                 trunc(logged,'MM')  
                                ) when 1 then 1 end
                   ) >= 1
        and count 
        (case when
                  months_between(trunc(sysdate,'MM') , 
                                 trunc(logged,'MM') 
                                )  in (2,3)  then 1 end 
                                 
        ) = 1

I don't understand one thing: You wrote :

minimum 1 time in one month preceding August (June or July)

and after then:

if logged in June -> not logg in July

if logged in July -> not logged in June

If you need EXACTLY one month- June or July just consider my query above.

If you need minimum one logon in June and July, then:

select id
                        
                   
     from yourtable
     group by id 
     having count(case 
                      months_between(trunc(sysdate,'MM'), 
                                     trunc(logged,'MM')  
                                    ) when 1 then 1 end
                       ) >= 1
            and count 
            (case when
                      months_between(trunc(sysdate,'MM') , 
                                     trunc(logged,'MM') 
                                    )  in (2,3)  then 1 end 
                                     
            ) >= 1

CodePudding user response：

Your question needs some clarification, but based on what you were describing I am seeing a couple of options.

The simplest one is probably using a combo of data densification (for generating a row for every month for each id) plus an analytical function (for enabling inter-row calculations. Here's a simple example of this:

rem create a dummy table with some more data (you do not seem to worry about the exact timestamp)
drop table logs purge;

create table logs (ID number, LOGGED timestamp);

insert into logs values (11, to_timestamp('2021-07-10 12:55:13.278','yyyy-mm-dd HH24:MI:SS.FF'));
insert into logs values (11, to_timestamp('2021-07-11 12:55:13.278','yyyy-mm-dd HH24:MI:SS.FF'));
insert into logs values (11, to_timestamp('2021-08-10 13:58:13.211','yyyy-mm-dd HH24:MI:SS.FF'));
insert into logs values (11, to_timestamp('2021-02-11 12:22:13.364','yyyy-mm-dd HH24:MI:SS.FF'));
insert into logs values (11, to_timestamp('2021-04-11 12:22:13.364','yyyy-mm-dd HH24:MI:SS.FF'));
insert into logs values (22, to_timestamp('2021-01-10 08:34:13.211','yyyy-mm-dd HH24:MI:SS.FF'));
insert into logs values (33, to_timestamp('2021-04-02 14:21:13.272','yyyy-mm-dd HH24:MI:SS.FF'));
commit;

The following SQL gets your data densified and lists the total count of logins for a month and the previous month on the same row so that you could do a comparative calculation. I have not done then, but I am hoping you get the idea.

with t as
(-- dummy artificial table just to create a time dimension for densification
 select distinct to_char(sysdate - rownum,'yyyy-mm') mon
 from dual connect by level < 300),
l_sparse as
(-- aggregating your login info per month
 select id, to_char(logged,'yyyy-mm') mon, count(*) cnt 
 from logs group by id, to_char(logged,'yyyy-mm')  ),
l_dense as
(-- densification with partition outer join
 select t.mon, l.id, cnt from l_sparse l partition by (id)
 right outer join t on (l.mon = t.mon)
)
-- final analytical function to list current and previous row info in same record
select mon, id
  , cnt
  , lag(cnt) over (partition by id order by mon asc) prev_cnt
from l_dense
order by id, mon;

parts of the result:

MON             ID        CNT   PREV_CNT
------- ---------- ---------- ----------
2020-12         11
2021-01         11
2021-02         11          2
2021-03         11                     2
2021-04         11          1
2021-05         11                     1
2021-06         11
2021-07         11          3
2021-08         11          2          3
2021-09         11                     2
2020-12         22
2021-01         22          2
2021-02         22                     2
2021-03         22
2021-04         22
...

You can see for ID 11 that for 2021-08 you have logins for the current and previous month, so you can math on it. (Would require another subselect/with branch).

Alternatives to this would be:

interrow calculation plus time math between two logged timestamps
pattern matching

Did not drill into those, not enough info about your real requirement.