SQL query for users who have logged in for at least n consecutive days

Take 3 consecutive days as an example, using the tool: MySQL.

1. Create SQL table:

 create table if not exists order(id varchar(10),date datetime,orders varchar(10));
insert into orde values('1' , '2019/1/1',10 );
insert into orde values('1' , '2019/1/2',109 );
insert into orde values('1' , '2019/1/3',150 );
insert into orde values('1' , '2019/1/4',99);
insert into orde values('1' , '2019/1/5',145);
insert into orde values('1' , '2019/1/6',1455);
insert into orde values('1' , '2019/1/7',199);
insert into orde values('1' , '2019/1/8',188 );
insert into orde values('4' , '2019/1/1',10 );
insert into orde values('2' , '2019/1/2',109 );
insert into orde values('3' , '2019/1/3',150 );
insert into orde values('4' , '2019/1/4',99);
insert into orde values('5' , '2019/1/5',145);
insert into orde values('6' , '2019/1/6',1455);
insert into orde values('7' , '2019/1/7',199);
insert into orde values('8' , '2019/1/8',188 );
insert into orde values('9' , '2019/1/1',10 );
insert into orde values('9' , '2019/1/2',109 );
insert into orde values('9' , '2019/1/3',150 );
insert into orde values('9' , '2019/1/4',99);
insert into orde values('9' , '2019/1/6',145);
insert into orde values('9' , '2019/1/9',1455);
insert into orde values('9' , '2019/1/10',199);
insert into orde values('9' , '2019/1/13',188 );

View the datasheet:

2. Use the row_number() over() sorting function to calculate the ranking of each id. The SQL is as follows:

 select *,row_number() over(partition by id order by date ) 'rank'
from orde
where orders is not NULL;

View datasheet:

3. Subtract the rank field from the date field. The SQL is as follows:

 select *,DATE_SUB(a.date,interval a.rank day) 'date_sub'
from(
select *,row_number() over(partition by id order by date ) 'rank'
from orde
where orders is not NULL
) a;

View the data:

4. Group by id and date and calculate the number of grouped items (count), and calculate the earliest and latest login times. The SQL is as follows:

 select b.id,min(date) 'start_time',max(date) 'end_time',count(*) 'date_count'
from(
select *,DATE_SUB(a.date,interval a.rank day) 'date_sub'
from(
select *,row_number() over(partition by id order by date ) 'rank'
from orde
where orders is not NULL
)
) b
group by b.date_sub,id
having count(*) >= 3
;

View the data:

References:

SQL query for users who have placed orders for at least seven consecutive days

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:

How to query the maximum number of consecutive login days between two dates in MySQL
MySQL calculates the number of days, months, and years between two dates
mysql generates continuous dates and variable assignments
How to calculate the number of consecutive login days in MySQL

<<: Summary of common tool functions necessary for front-end development

>>: Neon light effects implemented with pure CSS3