user image

Shilpa
Published in : 2022-11-05

(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

There is no comments yet

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

phpmyadmin manual installation gives following error
Publish date: 2022-07-11 | Comments: 6

Tag: PHP & Mysql

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

Tag: PHP & Mysql

[solved] Storage engine 'InnoDB' does not support system tables
Publish date: 2022-01-09 | Comments: 0

Tag: PHP & Mysql

MySQL - Lost connection to MySQL server during query - Required a solution.
Publish date: 2022-03-03 | Comments: 1

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

SQL full languages table
Publish date: 2022-02-24 | 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