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

This article mainly shows you "how to use SQL to query users who have logged in for at least n consecutive days". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn how to use SQL to query users who have logged in for at least n consecutive days.

1. Create the SQL table: create table if not exists orde (id varchar (10), date datetime,orders varchar (10)); insert into orde values ('1hammer,' 20119); insert into orde values ('1gram,' 20119) Insert into orde values ('1hammer,' 20119); insert into orde values ('1, '20119); insert into orde values (' 4', '20119); insert into orde values (' 2mm, '20119) Insert into orde values ('3hammer,' 20119); insert into orde values ('4yen,' 20119); insert into orde values ('5yen,' 20119); insert into orde values ('6here,' 20119); insert into orde values ('7march,' 20119); insert into orde values ('858,' 20119) Insert into orde values ('999,' 20119); insert into orde values ('9, '20119); insert into orde values (' 9, '20119) Insert into orde values ('9', '2019); insert into orde values (' 9', '2019)

View the data sheet:

two。 Use the row_number () over () sort 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 ordewhere orders is not NULL

View the data sheet:

3. Subtract the date date field from the rank ranking field, and the SQL is as follows: select *, DATE_SUB (a.dateje interval a.rank day) 'date_sub'from (select *, row_number () over (partition by id order by date)' rank'from ordewhere orders is not NULL) a

View the data:

4. Group and calculate the number of groups according to id and date (count), calculate the earliest login and the latest login time The SQL is as follows: select b.idjimmin (date) 'start_time',max (date)' end_time',count (*) 'date_count'from (select *, DATE_SUB (a.datememery interval a.rank day)' date_sub'from (select *, row_number () over (partition by id order by date) 'rank'from ordewhere orders is not NULL) a) bgroup by b.dateworthy subdivision count (*) > = 3

View the data:

The above is all the contents of the article "how to use SQL to query users who have logged in for at least n consecutive days". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!