Question is EXTREMELY poorly worded

What it ACTUALLY wants is # of unique hackers who submitted on each day AND all previous days to this date...

The question is written it such a way that makes it sound like it wants either unique hackers who submitted on All days or # of unique hackers per day regardless of previous submissions, this is why most of the below suggested answers are incorrect or increadibly poorly written sql code, almost impossibe to follow logic.

Then, it wants the max number of submissions made by a hacker for each day, regardless of whether or not they submitted on all previous days (this part is particularly confusing tbh).

I could simplify the below code, however it'll take to long

WITH DailySubmissions AS ( -- Step 1: List of unique hackers for each submission date

SELECT distinct s.submission_date, s.hacker_id
FROM submissions s

) , submission_dates as ( Select submission_date From submissions s group by submission_date )

, unique_hackers_list as ( Select sd.submission_date, hacker_id, count(*) as hackers , datediff(day, '2016-03-01', sd.submission_date) + 1 as days_past from submission_dates sd left join DailySubmissions as ds on sd.submission_date >= ds.submission_date group by sd.submission_date, hacker_id )

, ag as ( select submission_date, s.hacker_id, h.name, count(distinct submission_id) as submissions, row_number() over(partition by submission_date order by submission_date asc, count(distinct submission_id) desc, s.hacker_id asc) as subs_rank from submissions s left join hackers h on s.hacker_id = h.hacker_id group by submission_date, s.hacker_id, h.name )

, unique_hackers as (
select submission_date, count(hacker_id) as Unique_Hackers
from unique_hackers_list
where hackers = days_past
group by submission_date

)

select uh.submission_date, Unique_Hackers, subs.hacker_id, subs.name
from unique_hackers as uh
left join (
        select submission_date, hacker_id, name
        from ag
        where subs_rank = 1
    ) as subs
    on uh.submission_date = subs.submission_date
order by uh.submission_date asc