select s.name,a.cnt from 
(select subject_id,count(subject_id) cnt from submission where create_time='2022-04-09' group by subject_id) a
join subject s on a.subject_id= s.id order by a.cnt desc,a.subject_id

思路

• 1.求出当天提交任务数量，通过subject_id分组即可

select subject_id,count(subject_id) cnt from submission where create_time='2022-04-09' group by subject_id

• 2.根据subject_id找出对应的name即可，使用join或者where，最后在排个序

select s.name,a.cnt from 
(select subject_id,count(subject_id) cnt from submission where create_time='2022-04-09' group by subject_id) a
join subject s on a.subject_id= s.id order by a.cnt desc,a.subject_id