How to wirte an extensible SQL to find the users who continuously login for n days


Problem :

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.

For example:

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.

Thank you!



Solution :

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

Mysql Tutorials

Mysql Howto..

how to handle MySQL NULL in C++ using mysql.h?

How do I count the number of rows in a MySQL Table?

How to manage dynamic entities on mysql / hibernate / java combination

How do I get a column from mySQL to show up in a select box via PHP? [closed]

Show messages according to the MySQL database data? C#

How to get the data I want in MySQL in one query or in PHP?

MySQL Closure Table hierarchical database - How to pull information out in the correct order

How to search string in MySQL [closed]

How to optimize MySQL queries that has calculations

How to select value number of ENUM types in MySql?

how would I run this?

Voting system - How to store all votes in MySQL and how to properly tally the results

Rails / PHP : How to serialize an sql data row?

How to dump database from mysql with sensitive data removed or corrupted?

How to create the inner query wtih 5 different tables mysql

How to find related pages without querying inside a loop?

How to query the count result in this scenario in MYSQL/SQL?

How to get result from 2 mysql tables? [closed]

How do I use a web form to enter data to mysql db using an array?

How to create temp table in view in MySQL

MySQL: How to select a column with a value from another column, depending on which columns are empty?

How to populate dropdown list from mysql db and based on selection populate another dropdown list in php form?

How to execute the code inside javascript confirm box

How to add together the values in an array from MySQL query

MYSQL - How to sort column with ORDER BY and LIMIT and then sort those results by another column

How can i optimize this mysql query ? i use IN() operator

How to update existing record if the one being saved has the same key?

How to query wp_usermeta using WordPress inbuilt functions efficiently?

How to make a mysql connection inside a function

How to select some rows from MySQL table using theirs numbers?