-- 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