create table user_results (
user_id integer,
answer_date date
check ( answer_date = trunc ( answer_date ) )
)Table created.
begin
insert into user_results values ( 42, date'2023-09-11' );
insert into user_results values ( 42, date'2023-09-12' );
insert into user_results values ( 42, date'2023-09-18' );
insert into user_results values ( 42, date'2023-09-19' );
insert into user_results values ( 42, date'2023-09-20' );
insert into user_results values ( 42, date'2023-09-21' );
insert into user_results values ( 42, date'2023-09-22' );
insert into user_results values ( 42, date'2023-09-29' );
insert into user_results values ( 42, date'2023-10-08' );
insert into user_results values ( 42, date'2023-10-09' );
insert into user_results values ( 42, date'2023-10-10' );
insert into user_results values ( 42, date'2023-10-11' );
insert into user_results values ( 3141, date'2023-09-11' );
insert into user_results values ( 3141, date'2023-09-12' );
insert into user_results values ( 3141, date'2023-09-13' );
insert into user_results values ( 3141, date'2023-09-19' );
insert into user_results values ( 3141, date'2023-09-20' );
insert into user_results values ( 3141, date'2023-09-21' );
insert into user_results values ( 3141, date'2023-09-29' );
insert into user_results values ( 3141, date'2023-09-30' );
insert into user_results values ( 3141, date'2023-10-10' );
insert into user_results values ( 3141, date'2023-10-11' );
end; Statement processed.
Find the streaks per user
select *
from user_results match_recognize (
partition by user_id
order by answer_date
measures
count(*) streak,
min ( answer_date ) grp_start
pattern ( init consecutive* )
define
consecutive as
answer_date = prev ( answer_date ) + 1
) streaks| USER_ID | STREAK | GRP_START | 42 | 2 | 11-SEP-23 | 42 | 5 | 18-SEP-23 | 42 | 1 | 29-SEP-23 | 42 | 4 | 08-OCT-23 | 3141 | 3 | 11-SEP-23 | 3141 | 3 | 19-SEP-23 | 3141 | 2 | 29-SEP-23 | 3141 | 2 | 10-OCT-23 |
|---|
Find the longest and current streak
select user_id, max ( streak ) longest_streak,
min ( grp_start ) keep (
dense_rank last order by streak
) longest_start,
max ( streak ) keep (
dense_rank first order by grp_start desc
) current_streak
from user_results match_recognize (
partition by user_id
order by answer_date
measures
count(*) streak,
min ( answer_date ) grp_start
pattern ( init consecutive* )
define
consecutive as
answer_date = prev ( answer_date ) + 1
) streaks
group by user_id| LONGEST_STREAK | LONGEST_START | CURRENT_STREAK | 5 | 18-SEP-23 | 4 | 3 | 11-SEP-23 | 2 |
|---|