user

Shilpa

5 Nov 2022

(Mysql) Count User's signing in activity based on daily login logs

PHP & Mysql

I am trying to create one leaderboard based on users' activity, performance, and other activities done by each user.

here my data looks like as below,

ID | UserId | LoginDate

1 | 1      | 2022-11-01

2 | 1      | 2022-11-02

3 | 2      |  2022-11-02

4 | 2      |  2022-11-03

5 | 1      |  2022-11-03

6 | 2      |  2022-11-04

7 | 1      |  2022-11-04

7 | 2      |  2022-11-06

 

How do I create a query which will do the calculation of each user logged in to the above table and the longest continuation of the days and announce them as a first on the leaderboard?

 

From the above example,

User 1 logged in from 1st to 4th continuously, then His score is 4.

User 2 logged in from 2nd to 4th continuously, then His score is 2, but on 6th November, that user is logged in the system, but on 5th he missed the login, so its not in continuation.

 

With Individual calculation and using max function and group by syntax, I am able to achieve the scenario at some level, but not sure How to count the max continuous days?

 

 

Comments

No Comments to show

© 2024 Copyrights reserved for web-brackets.com