How to wirte an extensible SQL to find the users who continuously login for n days
If I have a table(Oracle or MySQL), which stores the date user logins.
So how can I write a SQL(or something else) to find the users who have continuously login for n days.
userID | logindate 1000 2014-01-10 1000 2014-01-11 1000 2014-02-01 1000 2014-02-02 1001 2014-02-01 1001 2014-02-02 1001 2014-02-03 1001 2014-02-04 1001 2014-02-05 1002 2014-02-01 1002 2014-02-03 1002 2014-02-05 .....
We can see that user 1000 has continually logined for two days in 2014, and user 1001 has continually logined for 5 days. and user 1002 never continuously logins.
The SQL should be extensible , which means I can pick every number of n, and modify a little or pass a new parameter, and the results is as expected.
As we don't know what dbms you are using (you named both MySQL and Oracle), here are are two solutions, both doing the same: Order the rows and subtract rownumber days from the login date (so if the 6th record is 2014-02-12 and the 7th is 2014-02-13 they both result in 2014-02-06). So we group by user and that groupday and count the days. Then we group by user to find the longest series.
Here is a solution for a dbms with analytic window functions (e.g. Oracle):
select userid, max(days) from ( select userid, groupday, count(*) as days from ( select userid, logindate - row_number() over (partition by userid order by logindate) as groupday from mytable ) group by userid, groupday ) group by userid --having max(days) >= 3
And here is a MySQL query (untested, because I don't have MySQL available):
select userid, max(days) from ( select userid, date_add(logindate, interval -row_number day) as groupday, count(*) as days from ( select userid, logindate, @row_num := @row_num + 1 as row_number from mytable cross join (select @row_num := 0) r order by userid, logindate ) group by userid, groupday ) group by userid -- having max(days) >= 3
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]