Longest Consecutive Login Streak for Each User
Consider a table:
Employees table has columns:
salary (decimal), and
Write an SQL query to find the employees who earn more than their managers.
DB Fiddle Instructions
Click ‘Edit on DB Fiddle’ to be taken to the in browser SQL playground where you can run your queries.
This problem is solved by finding the differences between the login dates and a sequence of integers for each user (
login_date - ROW_NUMBER()). If the users log in on consecutive dates, the differences would remain the same, indicating a streak. We use the first CTE
Streaks to calculate these differences. Then, we use the second CTE
MaxStreaks to calculate the length of each streak. Finally, we select the longest streak for each user. Note that we add 1 to the difference between the maximum and minimum login dates in each streak to get the correct streak length.