I don't understand this question. Are there several separate tables that need to be linked together? Why don't the tables have names?

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

SELECT ds.submission_date, (SELECT COUNT(DISTINCT hacker_id) FROM Submissions d WHERE d.submission_date = ds.submission_date AND d.hacker_id IN ( SELECT hacker_id FROM ( SELECT DISTINCT hacker_id, submission_date FROM Submissions WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY hacker_id, submission_date ) AS unique_hackers ) ) AS unique_hackers, msh.hacker_id, h.name FROM (SELECT DISTINCT submission_date FROM Submissions WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' ) ds LEFT JOIN ( SELECT submission_date, hacker_id, COUNT(submission_id) AS submission_count, RANK() OVER (PARTITION BY submission_date ORDER BY COUNT(submission_id) DESC, hacker_id ASC) AS rank FROM Submissions WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY submission_date, hacker_id ) AS msh ON ds.submission_date = msh.submission_date AND msh.rank = 1 LEFT JOIN Hackers h ON msh.hacker_id = h.hacker_id ORDER BY ds.submission_date;

WITH x AS (SELECT submission_date, hacker_id, COUNT(DISTINCT submission_id) AS subs FROM Submissions GROUP BY submission_date, hacker_id),

max_subs AS (SELECT submission_date, x.hacker_id, name, ROW_NUMBER() OVER(PARTITION BY submission_date ORDER BY subs DESC, x.hacker_id) AS rank FROM x LEFT JOIN Hackers h ON x.hacker_id = h.hacker_id),

streak_1 AS (SELECT hacker_id, submission_date, ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date) AS consecutive_days FROM x),

streak_2 AS (SELECT submission_date, COUNT(DISTINCT hacker_id) AS consistent_hacker_count FROM streak_1 WHERE consecutive_days = DATEDIFF(DAY, '2016-03-01', submission_date) + 1 GROUP BY submission_date)

SELECT ds.submission_date, consistent_hacker_count, ts.hacker_id, ts.name FROM (SELECT DISTINCT submission_date FROM Submissions) ds LEFT JOIN streak_2 ch ON ds.submission_date = ch.submission_date LEFT JOIN max_subs ts ON ds.submission_date = ts.submission_date AND ts.rank = 1 ORDER BY ds.submission_date;

WITH DailySubmissions AS ( -- Step 1: Calculate the number of unique hackers for each submission date SELECT s.submission_date, COUNT(DISTINCT s.hacker_id) AS unique_hackers FROM submissions s WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY s.submission_date ), MaxSubmissions AS ( -- Step 2: Calculate the hacker with the maximum number of submissions for each date SELECT s.submission_date, s.hacker_id, COUNT(s.submission_id) AS submission_count FROM submissions s WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY s.submission_date, s.hacker_id ), MaxHacker AS ( -- Step 3: For each date, find the hacker with the maximum submissions SELECT ms.submission_date, ms.hacker_id, ms.submission_count, ROW_NUMBER() OVER (PARTITION BY ms.submission_date ORDER BY ms.submission_count DESC, ms.hacker_id ASC) AS rn FROM MaxSubmissions ms ) -- Step 4: Combine results SELECT ds.submission_date, ds.unique_hackers, h.hacker_id, h.name FROM DailySubmissions ds JOIN MaxHacker mh ON ds.submission_date = mh.submission_date JOIN hackers h ON mh.hacker_id = h.hacker_id WHERE mh.rn = 1 ORDER BY ds.submission_date;