-- 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