select name,cnt
from(
    select
        distinct subject_id,
        name,
        count(create_time) over(partition by name) cnt
    from submission a
    left join subject b on a.subject_id=b.id
    where create_time=curdate()-1
) t
order by cnt desc,subject_id