SET NULL "NULL";
SET FEEDBACK OFF;
SET ECHO OFF;
SET HEADING OFF;
SET WRAP OFF;
SET LINESIZE 10000;
SET TAB OFF;
SET PAGES 0;
SET DEFINE OFF;

WITH champions AS (
SELECT
    s_r.submission_date,
    s_r.hacker_id
FROM
(
    SELECT
        s_c.submission_date,
        s_c.hacker_id,
        s_c.subs,
        ROW_NUMBER() OVER(PARTITION BY s_c.submission_date ORDER BY s_c.subs DESC, s_c.hacker_id ASC) rn
    FROM
    (
        SELECT
            s.submission_date,
            s.hacker_id,
            COUNT(*) subs
        FROM Submissions s
        GROUP BY s.submission_date, s.hacker_id
    ) s_c
) s_r
WHERE s_r.rn = 1
),
dated_subs AS (
    SELECT s.submission_date,
        COUNT(DISTINCT s.hacker_id) unique_hackers
    FROM Submissions s
    WHERE s.hacker_id IN (
        SELECT
            s2.hacker_id
        FROM Submissions s2
        WHERE s2.hacker_id = s.hacker_id
            AND s2.submission_date <= s.submission_date
        GROUP BY s2.hacker_id
        HAVING COUNT(DISTINCT s2.submission_date) = (
            SELECT COUNT(DISTINCT(submission_date))
            FROM Submissions
            WHERE Submissions.submission_date <= s.submission_date
        )
    )
    GROUP BY s.submission_date
)
SELECT
    ds.submission_date,
    ds.unique_hackers,
    c.hacker_id,
    h.name
FROM dated_subs ds
INNER JOIN champions c
    ON c.submission_date = ds.submission_date
INNER JOIN Hackers h
    ON h.hacker_id = c.hacker_id
ORDER BY ds.submission_date ASC;

exit;