15 Days of Learning SQL (Submission)

with dates as (
    select '2016-03-01' challenge_date union
    select '2016-03-02' challenge_date union
    select '2016-03-03' challenge_date union
    select '2016-03-04' challenge_date union
    select '2016-03-05' challenge_date union
    select '2016-03-06' challenge_date union
    select '2016-03-07' challenge_date union
    select '2016-03-08' challenge_date union
    select '2016-03-09' challenge_date union
    select '2016-03-10' challenge_date union
    select '2016-03-11' challenge_date union
    select '2016-03-12' challenge_date union
    select '2016-03-13' challenge_date union
    select '2016-03-14' challenge_date union
    select '2016-03-15' challenge_date
)
select d.challenge_date, s.hacker_count, h1.hacker_id, h.name from dates d
left join (
    select
        s_in.submission_date,
        count(distinct s_in.hacker_id) hacker_count
    from Submissions s_in
    join (
        select hacker_id,submission_date from(
            select
                submission_date,
                hacker_id,
                row_number() over(partition by hacker_id order by submission_date) row_num
            from (select distinct hacker_id, submission_date from Submissions) T) T
        where row_num=cast(right(cast(submission_date as varchar),2) as int)
    ) uniq_h on s_in.hacker_id=uniq_h.hacker_id and s_in.submission_date=uniq_h.submission_date
    group by s_in.submission_date
) s on d.challenge_date=s.submission_date
left join (
    select * from (
        select
            submission_date,
            hacker_id,
            row_number() over(partition by submission_date order by submission_count desc, hacker_id) row_num
        from(
            select submission_date,
                    hacker_id,
                    count(*) submission_count
            from Submissions
            group by submission_date, hacker_id
        ) T
    ) T
)h1 on d.challenge_date=h1.submission_date and h1.row_num=1
join Hackers h on h1.hacker_id=h.hacker_id
order by d.challenge_date