with t1 as (
    select b.name, a.subject_id, create_time
    from submission as a
    left join subject as b
    on a.subject_id = b.id
)
select name, cnt
from (
    select name, subject_id, count(create_time) as cnt
    from t1
    where create_time = CURDATE()
    group by name, subject_id
    order by cnt desc, subject_id
) as e;