Leaderboard

Example... DATA mysql> select * from worked_hours; +----+---------+---------------------+---------------------+ | id | user_id | start_time | finish_time | +----+---------+---------------------+---------------------+ | 1 | 1 | 2024-06-17 09:00:00 | 2024-06-17 17:26:00 | | 2 | 1 | 2024-06-18 09:00:00 | 2024-06-18 17:31:00 | | 3 | 1 | 2024-06-19 09:00:00 | 2024-06-19 17:12:00 | | 4 | 1 | 2024-06-20 09:00:00 | 2024-06-20 17:40:00 | | 5 | 1 | 2024-06-21 09:00:00 | 2024-06-21 17:01:00 | | 6 | 1 | 2024-06-22 09:00:00 | 2024-06-22 17:36:00 | | 7 | 1 | 2024-06-23 09:00:00 | 2024-06-23 17:47:00 | | 8 | 1 | 2024-06-24 09:00:00 | 2024-06-24 17:19:00 | | 9 | 2 | 2024-06-17 09:00:00 | 2024-06-17 17:20:00 | | 10 | 2 | 2024-06-18 09:00:00 | 2024-06-18 17:43:00 | | 11 | 2 | 2024-06-19 09:00:00 | 2024-06-19 17:45:00 | | 12 | 2 | 2024-06-20 09:00:00 | 2024-06-20 17:35:00 | | 13 | 2 | 2024-06-21 09:00:00 | 2024-06-21 17:26:00 | | 14 | 2 | 2024-06-22 09:00:00 | 2024-06-22 17:14:00 | | 15 | 2 | 2024-06-23 09:00:00 | 2024-06-23 17:55:00 | | 16 | 2 | 2024-06-24 09:00:00 | 2024-06-24 17:15:00 | | 17 | 3 | 2024-06-17 09:00:00 | 2024-06-17 17:40:00 | | 18 | 3 | 2024-06-18 09:00:00 | 2024-06-18 17:15:00 | | 19 | 3 | 2024-06-19 09:00:00 | 2024-06-19 17:35:00 | | 20 | 3 | 2024-06-20 09:00:00 | 2024-06-20 17:26:00 | | 21 | 3 | 2024-06-21 09:00:00 | 2024-06-21 17:38:00 | | 22 | 3 | 2024-06-22 09:00:00 | 2024-06-22 17:41:00 | | 23 | 3 | 2024-06-23 09:00:00 | 2024-06-23 17:00:00 | | 24 | 3 | 2024-06-24 09:00:00 | 2024-06-24 17:04:00 | +----+---------+---------------------+---------------------+ QUERY WITH hrs as ( SELECT user_id , DAYNAME(start_time) as day , TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 as mins , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 as cum , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 - TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 <= 1500 as include FROM worked_hours WINDOW w1 as (PARTITION BY user_id ORDER BY start_time) ) SELECT user_id , day , mins , cum FROM hrs WHERE include; RESULTS