Challenge
Consider a table: Employees.
The Employees table has columns: id (int), name (varchar), salary (decimal), and manager_id (int).
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.
Explanation
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.