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

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

Create a table

 create table 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 )

Ideas

Group the table by id and rank by time, subtract the rank rnk from the time. If the time is continuous, the subtraction result is equal:

 select *,date-rankrnk from (select *,row_number() over(partition by id order by date) rankfrom orde) a;

Then group by id, rnk, and count

 select id,rnk,count(*) ok from 
(select *,date-rankrnk from (select *,row_number() over(partition by id order by date) rankfrom orde) a) b group by id,rnk;

The following table is obtained:

Finally, filter the count items ok>=7.

You can also filter directly:

 select id,rnk from 
(select *,date-rankrnk from (select *,row_number() over(partition by id order by date) rankfrom orde) a) b group by id,rnk having count(*)>=7;

The above is the SQL query that the editor introduced to you for users who have placed orders for at least seven consecutive days. I hope it will be helpful to you. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!

You may also be interested in:
  • SQL query for users who log in continuously
  • SQL query method to implement users who have logged in for more than 7 days
  • SQL query statement to find the number of consecutive login days of a user
  • How to query the code of continuous date records in sqlserver

<<:  5 basic skills of topic page design (Alibaba UED Shanmu)

>>:  What are the new CSS :where and :is pseudo-class functions?

Recommend

MySQL multi-instance configuration application scenario

Table of contents MySQL multiple instances Multi-...

Common rule priority issues of Nginx location

Table of contents 1. Location / Matching 2. Locat...

MySQL data archiving tool mysql_archiver detailed explanation

Table of contents I. Overview 2. pt-archiver main...

MySQL transaction, isolation level and lock usage example analysis

This article uses examples to describe MySQL tran...

Review of the best web design works in 2012 [Part 1]

At the beginning of the new year, I would like to...

Detailed explanation of the relationship between Linux and GNU systems

Table of contents What is the Linux system that w...

Two examples of using icons in Vue3

Table of contents 1. Use SVG 2. Use fontAwesome 3...

Move MySQL database to another disk under Windows

Preface Today I installed MySQL and found that th...

Detailed explanation of putting common nginx commands into shell scripts

1. Create a folder to store nginx shell scripts /...

MySQL Database Basics: A Summary of Basic Commands

Table of contents 1. Use help information 2. Crea...

Example code for implementing background blur effect with CSS

Is it the effect below? If so, please continue re...

vue+springboot realizes login verification code

This article example shares the specific code of ...

Detailed explanation of several practical solutions for quickly deleting large amounts of data (tens of millions) in MySQL

The author recently encountered a performance bot...

MySQL whole table encryption solution keyring_file detailed explanation

illustrate MySql Community Edition supports table...

Practical way to build selenium grid distributed environment with docker

Recently, I needed to test the zoom video confere...