MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE test (
  create_time datetime NOT NULL,
  id int unsigned NOT NULL,
  users smallint unsigned DEFAULT NULL,
    PRIMARY KEY (create_time, id)
);
insert into `test` (`create_time`, `id`, `users`) values
  ('2023-05-20 08:00:03','4','2567'),  
  ('2023-05-20 08:00:03','8','3674'),  
  ('2023-05-20 08:00:03','10','1169'),  
  ('2023-05-20 08:05:03','4','2564'),  
  ('2023-05-20 08:05:03','8','3671'),  
  ('2023-05-20 08:05:03','10','1169'),  
  ('2023-05-20 08:10:03','4','2560'),  
  ('2023-05-20 08:10:03','8','3667'),  
  ('2023-05-20 08:10:03','10','1163'),  
  ('2023-05-20 08:15:03','4','2555'),  
  ('2023-05-20 08:15:03','8','3665'),  
  ('2023-05-20 08:15:03','10','1157'),  
  ('2023-05-20 08:20:02','4','2550'),  
  ('2023-05-20 08:20:02','8','3659'),  
  ('2023-05-20 08:20:02','10','1148'),  
  ('2023-05-20 08:25:03','4','2549'),  
  ('2023-05-20 08:25:03','8','3647'),  
  ('2023-05-20 08:25:03','10','1140'),  
  ('2023-05-20 08:30:03','4','2539'),  
  ('2023-05-20 08:30:03','8','3641'),  
  ('2023-05-20 08:30:03','10','1137'),  
  ('2023-05-20 08:35:02','4','2537'),  
  ('2023-05-20 08:35:02','8','3634'),  
  ('2023-05-20 08:35:02','10','1133'),  
  ('2023-05-20 08:40:02','4','2534'),  
  ('2023-05-20 08:40:02','8','3631'),  
  ('2023-05-20 08:40:02','10','1133'),  
  ('2023-05-20 08:45:02','4','2528'),  
  ('2023-05-20 08:45:02','8','3625'),  
  ('2023-05-20 08:45:02','10','1136'),  
  ('2023-05-20 08:50:03','4','2526'),  
  ('2023-05-20 08:50:03','8','3621'),  
  ('2023-05-20 08:50:03','10','1131'),  
  ('2023-05-20 08:55:03','4','2522'),  
  ('2023-05-20 08:55:03','8','3616'),  
  ('2023-05-20 08:55:03','10','1127'),  
  ('2023-05-21 08:00:04','4','1706'),  
  ('2023-05-21 08:00:04','8','2438'),  
  ('2023-05-21 08:00:04','10','741'),  
  ('2023-05-21 08:05:03','4','1702'),  
  ('2023-05-21 08:05:03','8','2432'),  
  ('2023-05-21 08:05:03','10','736'),  
  ('2023-05-21 08:10:04','4','1699'),  
  ('2023-05-21 08:10:04','8','2425'),  
  ('2023-05-21 08:10:04','10','733'),  
  ('2023-05-21 08:15:03','4','1700'),  
  ('2023-05-21 08:15:03','8','2421'),  
  ('2023-05-21 08:15:03','10','732'),  
  ('2023-05-21 08:20:03','4','1692'),  
  ('2023-05-21 08:20:03','8','2421'),  
  ('2023-05-21 08:20:03','10','722'),  
  ('2023-05-21 08:25:03','4','1688'),  
  ('2023-05-21 08:25:03','8','2418'),  
  ('2023-05-21 08:25:03','10','723'),  
  ('2023-05-21 08:30:04','4','1688'),  
  ('2023-05-21 08:30:04','8','2420'),  
  ('2023-05-21 08:30:04','10','721'),  
  ('2023-05-21 08:35:02','4','1681'),  
  ('2023-05-21 08:35:02','8','2419'),  
  ('2023-05-21 08:35:02','10','720'),  
  ('2023-05-21 08:40:02','4','1674'),  
  ('2023-05-21 08:40:02','8','2414'),  
  ('2023-05-21 08:40:02','10','720'),  
  ('2023-05-21 08:45:03','4','1671'),  
  ('2023-05-21 08:45:03','8','2409'),  
  ('2023-05-21 08:45:03','10','722'),  
  ('2023-05-21 08:50:03','4','1666'),  
  ('2023-05-21 08:50:03','8','2408'),  
  ('2023-05-21 08:50:03','10','721'),  
  ('2023-05-21 08:55:03','4','1663'),  
  ('2023-05-21 08:55:03','8','2401'),  
  ('2023-05-21 08:55:03','10','718'),  
  ('2023-05-22 08:00:04','4','1752'),  
  ('2023-05-22 08:00:04','8','2528'),  
  ('2023-05-22 08:00:04','10','792'),  
  ('2023-05-22 08:05:03','4','1752'),  
  ('2023-05-22 08:05:03','8','2525'),  
  ('2023-05-22 08:05:03','10','790'),  
  ('2023-05-22 08:10:03','4','1744'),  
  ('2023-05-22 08:10:03','8','2523'),  
  ('2023-05-22 08:10:03','10','787'),  
  ('2023-05-22 08:15:03','4','1741'),  
  ('2023-05-22 08:15:03','8','2533'),  
  ('2023-05-22 08:15:03','10','783'),  
  ('2023-05-22 08:20:02','4','1730'),  
  ('2023-05-22 08:20:02','8','2540'),  
  ('2023-05-22 08:20:02','10','772'),  
  ('2023-05-22 08:25:03','4','1725'),  
  ('2023-05-22 08:25:03','8','2534'),  
  ('2023-05-22 08:25:03','10','770'),  
  ('2023-05-22 08:30:02','4','1720'),  
  ('2023-05-22 08:30:02','8','2529'),  
  ('2023-05-22 08:30:02','10','762'),  
  ('2023-05-22 08:35:02','4','1722'),  
  ('2023-05-22 08:35:02','8','2527'),  
  ('2023-05-22 08:35:02','10','763'),  
  ('2023-05-22 08:40:02','4','1723'),  
  ('2023-05-22 08:40:02','8','2529'),  
  ('2023-05-22 08:40:02','10','761'),  
  ('2023-05-22 08:45:02','4','1723'),  
  ('2023-05-22 08:45:02','8','2537'),  
  ('2023-05-22 08:45:02','10','763'),  
  ('2023-05-22 08:50:03','4','1721'),  
  ('2023-05-22 08:50:03','8','2527'),  
  ('2023-05-22 08:50:03','10','761'),  
  ('2023-05-22 08:55:03','4','1718'),  
  ('2023-05-22 08:55:03','8','2528'),  
  ('2023-05-22 08:55:03','10','760'),  
  ('2023-05-23 08:00:03','4','2384'),  
  ('2023-05-23 08:00:03','8','3665'),  
  ('2023-05-23 08:00:03','10','1273'),  
  ('2023-05-23 08:05:02','4','2376'),  
  ('2023-05-23 08:05:02','8','3657'),  
  ('2023-05-23 08:05:02','10','1260'),  
  ('2023-05-23 08:10:03','4','2373'),  
  ('2023-05-23 08:10:03','8','3650'),  
  ('2023-05-23 08:10:03','10','1250'),  
  ('2023-05-23 08:15:03','4','2367'),  
  ('2023-05-23 08:15:03','8','3647'),  
  ('2023-05-23 08:15:03','10','1236'),  
  ('2023-05-23 08:20:03','4','2368'),  
  ('2023-05-23 08:20:03','8','3637'),  
  ('2023-05-23 08:20:03','10','1216'),  
  ('2023-05-23 08:25:03','4','2353'),  
  ('2023-05-23 08:25:03','8','3632'),  
  ('2023-05-23 08:25:03','10','1206'),  
  ('2023-05-23 08:30:04','4','2346'),  
  ('2023-05-23 08:30:04','8','3628'),  
  ('2023-05-23 08:30:04','10','1195'),  
  ('2023-05-23 08:35:03','4','2341'),  
  ('2023-05-23 08:35:03','8','3616'),  
  ('2023-05-23 08:35:03','10','1184'),  
  ('2023-05-23 08:40:03','4','2339'),  
  ('2023-05-23 08:40:03','8','3613'),  
  ('2023-05-23 08:40:03','10','1179'),  
  ('2023-05-23 08:45:04','4','2337'),  
  ('2023-05-23 08:45:04','8','3608'),  
  ('2023-05-23 08:45:04','10','1168'),  
  ('2023-05-23 08:50:02','4','2329'),  
  ('2023-05-23 08:50:02','8','3604'),  
  ('2023-05-23 08:50:02','10','1162'),  
  ('2023-05-23 08:55:03','4','2322'),  
  ('2023-05-23 08:55:03','8','3602'),  
  ('2023-05-23 08:55:03','10','1156'),  
  ('2023-05-24 08:00:03','4','2646'),  
  ('2023-05-24 08:00:03','8','4169'),  
  ('2023-05-24 08:00:03','10','1402'),  
  ('2023-05-24 08:05:02','4','2634'),  
  ('2023-05-24 08:05:02','8','4158'),  
  ('2023-05-24 08:05:02','10','1400'),  
  ('2023-05-24 08:10:02','4','2615'),  
  ('2023-05-24 08:10:02','8','4147'),  
  ('2023-05-24 08:10:02','10','1390'),  
  ('2023-05-24 08:15:03','4','2607'),  
  ('2023-05-24 08:15:03','8','4147'),  
  ('2023-05-24 08:15:03','10','1376'),  
  ('2023-05-24 08:20:02','4','2608'),  
  ('2023-05-24 08:20:02','8','4147'),  
  ('2023-05-24 08:20:02','10','1386'),  
  ('2023-05-24 08:25:03','4','2605'),  
  ('2023-05-24 08:25:03','8','4138'),  
  ('2023-05-24 08:25:03','10','1379'),  
  ('2023-05-24 08:30:04','4','2598'),  
  ('2023-05-24 08:30:04','8','4126'),  
  ('2023-05-24 08:30:04','10','1372'),  
  ('2023-05-24 08:35:02','4','2597'),  
  ('2023-05-24 08:35:02','8','4116'),  
  ('2023-05-24 08:35:02','10','1365'),  
  ('2023-05-24 08:40:02','4','2593'),  
  ('2023-05-24 08:40:02','8','4110'),  
  ('2023-05-24 08:40:02','10','1356'),  
  ('2023-05-24 08:45:03','4','2586'),  
  ('2023-05-24 08:45:03','8','4099'),  
  ('2023-05-24 08:45:03','10','1351'),  
  ('2023-05-24 08:50:02','4','2586'),  
  ('2023-05-24 08:50:02','8','4088'),  
  ('2023-05-24 08:50:02','10','1341'),  
  ('2023-05-24 08:55:03','4','2584'),  
  ('2023-05-24 08:55:03','8','4085'),  
  ('2023-05-24 08:55:03','10','1333');

Records: 180  Duplicates: 0  Warnings: 0


WITH cte AS (
  SELECT DISTINCT FIRST_VALUE(create_time) OVER (PARTITION BY DATE(create_time) ORDER BY SUM(users) DESC) create_time 
  FROM test 
  GROUP BY create_time
)
SELECT id, create_time AS stat_time, users
FROM test
WHERE create_time IN (SELECT create_time FROM cte);


id
stat_time
users


4
2023-05-20 08:00:03
2567

8
2023-05-20 08:00:03
3674

10
2023-05-20 08:00:03
1169

4
2023-05-21 08:00:04
1706

8
2023-05-21 08:00:04
2438

10
2023-05-21 08:00:04
741

4
2023-05-22 08:00:04
1752

8
2023-05-22 08:00:04
2528

10
2023-05-22 08:00:04
792

4
2023-05-23 08:00:03
2384

8
2023-05-23 08:00:03
3665

10
2023-05-23 08:00:03
1273

4
2023-05-24 08:00:03
2646

8
2023-05-24 08:00:03
4169

10
2023-05-24 08:00:03
1402
id	stat_time	users
4	2023-05-20 08:00:03	2567
8	2023-05-20 08:00:03	3674
10	2023-05-20 08:00:03	1169
4	2023-05-21 08:00:04	1706
8	2023-05-21 08:00:04	2438
10	2023-05-21 08:00:04	741
4	2023-05-22 08:00:04	1752
8	2023-05-22 08:00:04	2528
10	2023-05-22 08:00:04	792
4	2023-05-23 08:00:03	2384
8	2023-05-23 08:00:03	3665
10	2023-05-23 08:00:03	1273
4	2023-05-24 08:00:03	2646
8	2023-05-24 08:00:03	4169
10	2023-05-24 08:00:03	1402