WITH HackerDaily AS (
    -- Extract distinct submission_date and hacker_id
    SELECT DISTINCT submission_date, hacker_id
    FROM Submissions
),
RecursiveActiveHackers AS (
    -- Base case - Include all hackers on the first day
    SELECT 
        h1.submission_date,
        h1.hacker_id
    FROM HackerDaily h1
    WHERE h1.submission_date = (SELECT MIN(submission_date) FROM HackerDaily)

    UNION ALL

    -- Recursive case - Retain hackers who are active on all previous days
    SELECT 
        h1.submission_date,
        h1.hacker_id
    FROM HackerDaily h1
    INNER JOIN RecursiveActiveHackers r
        ON h1.hacker_id = r.hacker_id -- Hacker must be active on all previous days
       AND h1.submission_date = DATEADD(DAY, 1, r.submission_date) -- Move to the next day
),
DailyMaxSubmissions AS (
    -- Calculate the hacker with the maximum submissions for each day
    SELECT 
        Submissions.submission_date,
        Submissions.hacker_id,
        Hackers.name,
        COUNT(*) AS submissions,
        RANK() OVER (
            PARTITION BY Submissions.submission_date 
            ORDER BY COUNT(*) DESC, Submissions.hacker_id ASC
        ) AS rnk
    FROM Submissions
    JOIN Hackers ON Submissions.hacker_id = Hackers.hacker_id
    GROUP BY Submissions.submission_date, Submissions.hacker_id, Hackers.name
)
-- Combine results
SELECT 
    r.submission_date,
    COUNT(DISTINCT r.hacker_id) AS active_hackers,
    d.hacker_id AS max_submission_hacker_id,
    d.name AS max_submission_hacker_name
FROM RecursiveActiveHackers r
LEFT JOIN DailyMaxSubmissions d
    ON r.submission_date = d.submission_date AND d.rnk = 1
GROUP BY r.submission_date, d.hacker_id, d.name
ORDER BY r.submission_date;