Calculate The Number Of Consecutive Daily Sessions A User Has In Mysql

How do I calculate the number of sessions a user has which are 1 day apart from each other? This is what I have so far. The answer should be 46, but this code returns just the last

Solution 1:

I believe the solution is to use a join on the records that are exactly one day before the date in question. Try this:

SELECT COUNT(*) FROM mobile_traffic m1
INNER JOIN mobile_traffic m2 ON m1.user_id = m2.user_id
AND DATE(m1.reg_utc_timestamp) =
(DATE(m2.reg_utc_timestamp) + INTERVAL 1 DAY)
WHERE m1.user_id = <some_user_id>