WITH date_span AS (
    SELECT MIN(submission_date) AS min_day,
           MAX(submission_date) AS max_day --(optional)
    FROM Submissions
),

-- number of submissions and total score per hacker per day
cte_number_of_submissions_per_day AS (
    SELECT submission_date, hacker_id,
           COUNT(submission_id) AS count_sub,
           SUM(score) AS total_score --(optional)
    FROM Submissions
    GROUP BY submission_date, hacker_id
),

-- compute how many days a hacker has submitted up to each day
cte_hacker_day_consistency AS (
    SELECT s.hacker_id,
           s.submission_date,
           COUNT(DISTINCT s2.submission_date) AS days_submitted,
           DATEDIFF(day, d.min_day, s.submission_date) + 1 AS days_elapsed
    FROM Submissions s
    JOIN date_span d ON 1=1
    JOIN Submissions s2
      ON s.hacker_id = s2.hacker_id
     AND s2.submission_date <= s.submission_date
    GROUP BY s.hacker_id, s.submission_date, d.min_day
),

-- flag if hacker is consistent up to this submission_date
cte_consistency_flag AS (
    SELECT hacker_id, submission_date,
           CASE WHEN days_submitted = days_elapsed THEN 1 ELSE 0 END AS is_consistent
    FROM cte_hacker_day_consistency
),

-- ranking hackers per day
cte_rank_by_submissions AS (
    SELECT n.*,
           c.is_consistent,
           ROW_NUMBER() OVER(
               PARTITION BY n.submission_date
               ORDER BY n.count_sub DESC, c.is_consistent DESC, n.hacker_id ASC
           ) AS rn
    FROM cte_number_of_submissions_per_day n
    JOIN cte_consistency_flag c
      ON n.hacker_id = c.hacker_id
     AND n.submission_date = c.submission_date
),
-- Count the second column ie unique hackers submitted each day
cte_count_unique_consistent_hackers AS (
    SELECT submission_date, SUM(is_consistent) AS number_unique_consistent_hacker
    FROM cte_rank_by_submissions
    GROUP BY submission_date
),
-- best hacker per day (with consistency preference)
cte_best_hacker_per_day AS (
    SELECT *
    FROM cte_rank_by_submissions
    WHERE rn = 1
)
SELECT bhpd.submission_date,  cuch.number_unique_consistent_hacker, bhpd.hacker_id, h.name
FROM cte_best_hacker_per_day bhpd JOIN cte_count_unique_consistent_hackers cuch ON bhpd.submission_date = cuch.submission_date JOIN Hackers h ON bhpd.hacker_id = h.hacker_id
ORDER BY submission_date;