-- Filter out submissions from the second time onwards 
WITH distinct_values AS (
    SELECT DISTINCT 
        submission_date, 
        hacker_id
    FROM submissions
), 
-- Filter hackers who submitted the required number of submissions for each corresponding day (rolling/counting up to each day).
filter_hackers AS(
    SELECT
        submission_date,
        hacker_id,
        DENSE_RANK() OVER(ORDER BY submission_date ASC) AS rank_date,
        COUNT(hacker_id) OVER(PARTITION BY hacker_id ORDER BY submission_date ASC) AS hacker_sub 
    FROM distinct_values
),
-- Count the number of submissions each hacker made per day
top_sub AS (
    SELECT 
        s.submission_date,
        s.hacker_id, 
        h.name,
        COUNT(s.hacker_id) AS count_sub,
        ROW_NUMBER() OVER(PARTITION BY s.submission_date ORDER BY submission_date ASC, COUNT(s.hacker_id) DESC ,s.hacker_id ASC) AS flag
    FROM submissions AS s 
    JOIN hackers AS h 
        ON h.hacker_id = s.hacker_id
    GROUP BY s.submission_date, s.hacker_id, h.name
) 
SELECT
    fh.submission_date,
    COUNT(fh.hacker_id),
    ts.hacker_id,
    ts.name
FROM filter_hackers AS fh
JOIN top_sub AS ts
    ON fh.submission_date = ts.submission_date 
WHERE fh.rank_date = fh.hacker_sub AND ts.flag = 1
GROUP BY fh.submission_date, ts.hacker_id, ts.name
ORDER BY fh.submission_date ASC

WITH TBL1 AS ( -- all hacker_id who made at least one submission each day
    SELECT DISTINCT
        submission_date
        ,hacker_id
    From Submissions Sub
    WHERE hacker_id IN (
        SELECT DISTINCT hacker_id 
        FROM Submissions 
        WHERE submission_date <= Sub.submission_date
        GROUP BY hacker_id
        HAVING COUNT(DISTINCT submission_date) = (
            SELECT COUNT(DISTINCT submission_date)
            FROM Submissions
            WHERE submission_date <= Sub.submission_date
        )
    )
)
,TBL2 AS ( --no of unique hacker_id who made at least one submission each day
    SELECT
        submission_date
        ,COUNT(hacker_id) AS hackerNr
    From TBL1
    GROUP BY submission_date
)
,TBL3 AS ( -- number of submissions made by hacker each day
    SELECT 
        hacker_id
        ,submission_date
        ,COUNT(submission_id) AS nrSub 
    FROM Submissions 
    GROUP BY hacker_id, submission_date
)
,TBL4 AS ( -- lowest hacker_id each day
    SELECT DISTINCT
        T1.submission_date
        ,T2.hackerNr
        ,MIN(T3.hacker_id) AS hackerID
    FROM TBL1 T1
    LEFT JOIN TBL2 T2 ON T1.submission_date = T2.submission_date
    LEFT JOIN TBL3 T3 ON T1.submission_date = T3.submission_date
    WHERE T3.nrSub = (SELECT MAX(nrSub) FROM TBL3 WHERE submission_date = T1.submission_date GROUP BY submission_date)
    GROUP BY T1.submission_date, T2.hackerNr
)
SELECT 
    TBL4.*
    ,H.name
FROM TBL4
LEFT JOIN Hackers H ON H.hacker_id = TBL4.hackerID
ORDER BY TBL4.submission_date

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;