Check for x consecutive days - given timestamps in database

You can accomplish this using a shifted self-outer-join in conjunction with a variable. See this solution:

SELECT IF(COUNT(1) > 0, 1, 0) AS has_consecFROM(    SELECT *    FROM    (        SELECT IF(b.login_date IS NULL, @val:=@val+1, @val) AS consec_set        FROM tbl a        CROSS JOIN (SELECT @val:=0) var_init        LEFT JOIN tbl b ON             a.user_id = b.user_id AND            a.login_date = b.login_date + INTERVAL 1 DAY        WHERE a.user_id = 1    ) a    GROUP BY a.consec_set    HAVING COUNT(1) >= 30) a

This will return either a 1 or a 0 based on if a user has logged in for 30 consecutive days or more at ANYTIME in the past.

The brunt of this query is really in the first subselect. Let's take a closer look so we can better understand how this works:

With the following example data set:

CREATE TABLE tbl (  user_id INT,  login_date DATE);INSERT INTO tbl VALUES(1, '2012-04-01'),  (2, '2012-04-02'),(1, '2012-04-25'),  (2, '2012-04-03'),(1, '2012-05-03'),  (2, '2012-04-04'),(1, '2012-05-04'),  (2, '2012-05-04'),(1, '2012-05-05'),  (2, '2012-05-06'),(1, '2012-05-06'),  (2, '2012-05-08'),(1, '2012-05-07'),  (2, '2012-05-09'),(1, '2012-05-09'),  (2, '2012-05-11'),(1, '2012-05-10'),  (2, '2012-05-17'),(1, '2012-05-11'),  (2, '2012-05-18'),(1, '2012-05-12'),  (2, '2012-05-19'),(1, '2012-05-16'),  (2, '2012-05-20'),(1, '2012-05-19'),  (2, '2012-05-21'),(1, '2012-05-20'),  (2, '2012-05-22'),(1, '2012-05-21'),  (2, '2012-05-25'),(1, '2012-05-22'),  (2, '2012-05-26'),(1, '2012-05-25'),  (2, '2012-05-27'),                    (2, '2012-05-28'),                    (2, '2012-05-29'),                    (2, '2012-05-30'),                    (2, '2012-05-31'),                    (2, '2012-06-01'),                    (2, '2012-06-02');

This query:

SELECT a.*, b.*, IF(b.login_date IS NULL, @val:=@val+1, @val) AS consec_setFROM tbl aCROSS JOIN (SELECT @val:=0) var_initLEFT JOIN tbl b ON     a.user_id = b.user_id AND    a.login_date = b.login_date + INTERVAL 1 DAYWHERE a.user_id = 1

Will produce:

As you can see, what we are doing is shifting the joined table by +1 day. For each day that is not consecutive with the prior day, a NULL value is generated by the LEFT JOIN.

Now that we know where the non-consecutive days are, we can use a variable to differentiate each set of consecutive days by detecting whether or not the shifted table's rows are NULL. If they are NULL, the days are not consecutive, so just increment the variable. If they are NOT NULL, then don't increment the variable:

After we've differentiated each set of consecutive days with the incrementing variable, it's then just a simple matter of grouping by each "set" (as defined in the consec_set column) and using HAVING to filter out any set that has less than the specified consecutive days (30 in your example):

Then finally, we wrap THAT query and simply count the number of sets that had 30 or more consecutive days. If there was one or more of these sets, then return 1, otherwise return 0.

See a SQLFiddle step-by-step demo

You can add X to timestamp date and chech if distinct( dates ) in this date range is == X:

At least once every day of those 30 days:

SELECT distinct 1 FROM    login_dates l1 inner join   login_dates l2      on l1.user = l2.user and          l2.timestamp between l1.timestamp and                                date_add( l1.timestamp, Interval X day )where l1.user = some_usergroup by    DATE(l1.timestamp)having    count( distinct DATE(l1.timestamp) ) = X

(You don't speack about performance requirements ... ;) )

* Edited * The query for only last X days: east once every day of those 30 days

SELECT distinct 1 FROM    login_dates l1 where l1.user = some_user      and l1.timestamp >  date_add( CURDATE() , Interval -X day )group by    l1.userhaving    count( distinct DATE(l1.timestamp) ) = X

That's a hard problem to solve with SQL alone.

The core of the problem is that you need to compare dynamic results sets to each other in one query. For example, you need to get all the logins/session IDs for one DATE, then JOIN or UNION them with a list to a grouping of logins from the DATE() (which you could use DATE_ADD to determine). You could do this for N number of consecutive dates. If you have any rows left, then those sessions have been logged in over that period.

Assume the following table:

sessionid int, created date

This query returns all the sessionids that have have rows for the last two days:

select t1.sessionid from logins t1   join logins t2 on t1.sessionid=t2.sessionid   where t1.created = DATE(date_sub(now(), interval 2 day))     AND t2.created = DATE(date_sub(now(), interval 1 day));

As you can see, the SQL will get gnarly for 30 days. Have a script generate it. :-D

This further assumes that every day, the login table is updated with the session.

I don't know if this actually solves your problem, but I hope I have helped frame the problem.

Good luck.