select
    a.name,
    t.cnt
from
    subject a
    inner join (
        select
            create_time,
            subject_id,
            count(*) cnt
        from
            submission
        group by
            create_time,
            subject_id
        having
            create_time = '2024-09-02'
    ) t on t.subject_id = a.id
order by
    t.cnt desc,
    t.subject_id asc;