The key aspect of the task was to calculate eligible hackers who made submissions every day up to and including each specific day (i.e., cumulative submissions). We should check eligible on each day..not for whole range ... so if we have a 2016-03-05 date -> we should check if that hackers submitted from 2016-03-01 to 2016-03-05....e.t.c

This exercise is extremely frustrating because of lack of compatibility. MS Sql does not allow CTEs and then the MS SQL sever has way too many requirements for a simple Rank(). This is definitely not compabitle with modern day scripting. The answer is a few lines of code with Postgre which they are not offering as a language to use. People here are running 4 CTEs, or 5 subqueries for a question which otherwise is simple.

how come this problem has to be run with this version 5.7.27-0ubuntu0.18.04.1 . there is even no CTE and row_number.

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