Shilpa
Published in : 2022-11-05
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?
There is no comments yet
Join our community and get the chance to solve your code issues & share your opinion with us
Sign up Now