-- submissions_per_day
with hacker_submissions as
(select hacker_id, submission_date,count(submission_id) submission_cnt
    from submissions group by hacker_id, submission_date),

-- rank hackers on submission_cnt
ranked_hackers as (select hs.submission_date,hs.hacker_id, submission_cnt,
    row_number() over (partition by hs.submission_date order by submission_cnt desc, hs.hacker_id asc) rnk
    from hacker_submissions hs),

-- eligible hackers
cte as (select hacker_id, submission_date,
count(submission_cnt) over (partition by hacker_id order by submission_date) date_before_submissions
from hacker_submissions),
eligible_hackers as (select hacker_id,submission_date from cte where
                    day(submission_date) = date_before_submissions),

-- unique eligible hackers count
unique_hackers as (select submission_date, count(hacker_id) unique_cnt
                   from eligible_hackers group by submission_date)

--combine tables
select rh.submission_date,uh.unique_cnt,rh.hacker_id,h.name from
ranked_hackers rh
inner join unique_hackers uh on uh.submission_date=rh.submission_date
inner join hackers h on h.hacker_id = rh.hacker_id
where rh.rnk=1
order by rh.submission_date