select a.name,a.cnt
from
(
    SELECT s.subject_id,ss.name,
    count(*) as cnt
    from submission s 
    join subject ss on s.subject_id = ss.id
    where s.create_time = current_date
    group by s.subject_id,ss.name
)a
order by a.cnt desc,a.subject_id

没啥难的