DECLARE @st DATE

SELECT @st = CAST(MIN(submission_date) as DATE) FROM Submissions

SELECT DISTINCT CAST(submission_date as DATE) submission_date
INTO #ContestDates
FROM Submissions
ORDER BY submission_date

--select @st, submission_date from #ContestDates

;WITH HackerSubmissions AS (
    SELECT cd.submission_date by_date, s.submission_date, hacker_id, count(*) sub_count
    FROM #ContestDates cd 
    INNER JOIN Submissions s
    ON s.submission_date >= @st
    AND s.submission_date <= cd.submission_date
    GROUP BY cd.submission_date, s.submission_date, hacker_id
),
DailyHackers AS (
    SELECT by_date, hacker_id, count(sub_count) sub_count
    FROM HackerSubmissions
    GROUP BY by_date, hacker_id
    HAVING count(sub_count) >= DATEDIFF(dd, @st, by_date) + 1
    --ORDER BY by_date, hacker_id
),
SubmissionCounts AS(
    SELECT by_date, count(distinct hacker_id) hacker_count
    FROM DailyHackers dh
    GROUP BY by_date
), 
HackerSubmissionCounts AS (
    SELECT s.submission_date, hacker_id, count(*) sub_count
    FROM Submissions s
    GROUP BY s.submission_date, hacker_id
),
DailyMaxSubmissions AS (
    SELECT submission_date, max(sub_count) sub_count
    FROM HackerSubmissionCounts
    GROUP BY submission_date
),
DailyMaxHackers AS (
    SELECT hsc.submission_date, min(hacker_id) hacker_id
    FROM HackerSubmissionCounts hsc
    INNER JOIN DailyMaxSubmissions dms
    ON hsc.submission_date = dms.submission_date
    AND hsc.sub_count = dms.sub_count
    GROUP BY hsc.submission_date
)
SELECT cd.submission_date, sc.hacker_count, h.hacker_id, h.name
FROM #ContestDates cd
INNER JOIN SubmissionCounts sc
ON cd.submission_date = sc.by_date
INNER JOIN DailyMaxHackers dmh
ON dmh.submission_date = cd.submission_date
INNER JOIN Hackers h
ON dmh.hacker_id = h.hacker_id
ORDER BY cd.submission_date