user image

Shilpa
Published in : 2022-03-01

Mysql query: How do I count the day streak of particular user?

PHP & Mysql

When any user logged in to my web application, I want to count the streak for how many days continuously that user is logged in.

Here is my data: Live sample link

Table schema:

CREATE TABLE mytable
    (`Id` int, `UserId` int, `Timestamp` date, `IsLoggedIn` tinyint)
;

Table data:

INSERT INTO mytable
    (`Id`, `UserId`, `Timestamp`, `IsLoggedIn`)
VALUES
    (1, 1, '2022-02-28',1),
    (2, 1, '2022-02-27',1),
    (3, 2, '2022-02-28',1),
    (4, 1, '2022-03-01',1),
    (5, 1, '2022-02-26',1),
    (6, 2, '2022-02-26',1),
    (7, 3, '2022-02-25',1),
    (8, 2, '2022-03-01',1),
    (9, 3, '2022-03-01',1)
;

and select query,

select * from mytable;

I want to count the streak of logged-in user.

For example UserId = 1, total day streak should be 4, as that user logged in on Feb 26 to Mar 01 (02/26, 02/27, 02/28, 03/01).

How do I calculate among the entire table?

 

Comments

Eslam Zedan Date : 2022-03-02

Best answers

8

Best answers

8

One way to do this is to use MySQL user variables. This isn't necessarily the most efficient approach for large sets, since it materializes two inline views.

SELECT s.UserId
     , MAX(s.streak) AS longest_streak
  FROM ( SELECT IF(@prev_user = o.user AND o.Timestamp = @prev_date + INTERVAL 1 DAY
                  , @streak := @streak + 1
                  , @streak := 1
                ) AS streak
              , @prev_user := o.user AS user
              , @prev_date := o.Timestamp AS `date`
           FROM ( SELECT t.user
                       , DATE(t.Timestamp) AS `date`
                    FROM mytable t
                   CROSS
                    JOIN (SELECT @prev_user := NULL, @prev_date := NULL, @streak := 1) i
                   GROUP BY t.UserId, DATE(t.Timestamp)
                   ORDER BY t.UserId, DATE(t.Timestamp)
                ) o
       ) s
 GROUP BY s.UserId

The inline view aliased as i just initializes some user variables; we don't really care what it returns, except that we need it to return exactly 1 row because of the JOIN operation; we just really care about the side effect of initializing user variables early in the statement execution.

The inline view aliased as o gets a list of users and dates; the specification was for an entry "on each date", so we can truncate off the time portion, and get just the DATE, and make that into a distinct set, using the GROUP BY clause.

The inline view aliased as s processes each row, and saves the values of the current row into the @prev_ user variables. Before it overwrites the values, it compares the values on the current row to the values (saved) from the previous row. If the user matches, and the date on the current row is exactly 1 day later than the previous date, we are continuing a "streak", so we increment the current value of the @streak variable by 1. Otherwise, the previous streak was broken, and we start a new "streak", resetting @streak to 1.

Finally, we process the rows from s to extract the maximum streak for each user.

(This statement is desk checked only, there could be a typo or two.) 

as I found here

Shilpa Date : 2022-03-02

The query you provided is not working. 

I updated the same query as below, but still it returns error.

SELECT s.UserId
     , MAX(s.streak) AS longest_streak
  FROM ( SELECT IF(@prev_user = o.UserId AND o.Timestamp = @prev_date + INTERVAL 1 DAY
                  , @streak := @streak + 1
                  , @streak := 1
                ) AS streak
              , @prev_user := o.UserId AS user
              , @prev_date := o.Timestamp AS `date`
           FROM ( SELECT t.UserId
                       , DATE(t.Timestamp) AS `date`
                    FROM mytable t
                   CROSS
                    JOIN (SELECT @prev_user := NULL, @prev_date := NULL, @streak := 1) i
                   GROUP BY t.UserId, DATE(t.Timestamp)
                   ORDER BY t.UserId, DATE(t.Timestamp)
                ) o
       ) s
 GROUP BY s.UserId

Thank you for responding, but can you please check?

Rakshit Date : 2022-03-02

Best answers

34

Best answers

34

Here I created a MySQL query for your question.  Using CROSS JOIN to same table you can achieve your goals. I am calculating time from today's date to a continuous streak.

-- Query to get a streak of days for userId = 1; (visit: http://sqlfiddle.com/#!9/9e8371/1/0)
SELECT MAX(streak) AS streak
FROM (
  SELECT Id, UserId, `Timestamp`,
         DATEDIFF(NOW(), `Timestamp`),
         @streak := IF( DATEDIFF(NOW(), `Timestamp`) - @days_diff > 1, @streak, 
                       IF(@days_diff := DATEDIFF(NOW(), `Timestamp`), @streak+1, @streak+1))  AS streak                                        
  FROM mytable
  CROSS JOIN (SELECT @streak := 0, @days_diff := -1) AS vars
  WHERE `Timestamp` <= NOW() AND UserId = 1
  ORDER BY `Timestamp` DESC) AS t;

Reference: SQLFiddle

Shilpa Date : 2022-03-02

Thank you, it worked. I need to show the users usage on my dashboard & leaderboard.

Leave a comment

Join us

Join our community and get the chance to solve your code issues & share your opinion with us

Sign up Now

Related posts

mysql service start not exist
Publish date: 2022-02-23 | Comments: 4

Tag: PHP & Mysql

CodeIgniter route does not work
Publish date: 2022-02-22 | Comments: 4

Tag: PHP & Mysql

How the image file have .php extension?
Publish date: 2022-03-01 | Comments: 2

Tag: PHP & Mysql

php difference between two times in hours
Publish date: 2022-02-25 | Comments: 2

Tag: PHP & Mysql

ERROR 1410 (42000): You are not allowed to create a user with GRANT
Publish date: 2022-04-01 | Comments: 2

Tag: PHP & Mysql

SQL full languages table
Publish date: 2022-02-24 | Comments: 2

Tag: PHP & Mysql

[solved] #1273 - Unknown collation: 'utf8mb4_0900_ai_ci'
Publish date: 2022-01-09 | Comments: 1

Tag: PHP & Mysql