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

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

Recommend

Vue image cropping component example code

Example: tip: This component is based on vue-crop...

A complete guide to the Docker command line (18 things you have to know)

Preface A Docker image consists of a Dockerfile a...

Causes and solutions for front-end exception 502 bad gateway

Table of contents 502 bad gateway error formation...

How to uninstall MySQL cleanly (tested and effective)

How to uninstall Mysql perfectly? Follow the step...

Six weird and useful things about JavaScript

Table of contents 1. Deconstruction Tips 2. Digit...

How to solve the problem of blurry small icons on mobile devices

Preface Previously, I talked about the problem of...

Web Design Tips: Simple Rules for Page Layout

Repetition: Repeat certain page design styles thr...

How to use async await elegantly in JS

Table of contents jQuery's $.ajax The beginni...

How to add custom system services to CentOS7 systemd

systemd: The service systemctl script of CentOS 7...

Detailed explanation of JavaScript error capture

Table of contents 1. Basic usage and logic 2. Fea...

Difference between MySQL update set and and

Table of contents Problem Description Cause Analy...