with submission_cnt as (
    select 
        subject_id,
        count(subject_id) as cnt
    from 
        submission
    where 
        create_time = current_date()
    group by 
        subject_id
)

select
    s.name,
    s_c.cnt
from 
    submission_cnt as s_c
left join 
    subject as s
on 
    s_c.subject_id = s.id
order by 
    s_c.cnt desc,
    s_c.subject_id