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;

WITH
  cte1 AS   (   SELECT s.submission_date, s.submission_id, s.hacker_id, h.name
                    , DAY(s.submission_date) AS day_num
                    , DENSE_RANK() OVER(PARTITION BY s.hacker_id ORDER BY s.submission_date) AS rnk
                FROM submissions s
                JOIN hackers h ON h.hacker_id = s.hacker_id
                WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15' )
, cte2 AS   (   SELECT submission_date, COUNT(DISTINCT hacker_id) AS unique_h_count
                FROM cte1
                WHERE day_num = rnk
                GROUP BY submission_date )

, cte3 AS   (   SELECT submission_date, hacker_id, COUNT(submission_id) AS s_count
                FROM cte1
                GROUP BY submission_date, hacker_id )
, cte4 AS   (   SELECT *, ROW_NUMBER() OVER(PARTITION BY submission_date ORDER BY s_count DESC, hacker_id) AS rn
                FROM cte3 )

SELECT cte4.submission_date, cte2.unique_h_count, cte4.hacker_id, h.name
FROM cte4
JOIN hackers h ON h.hacker_id = cte4.hacker_id
JOIN cte2 ON cte2.submission_date = cte4.submission_date
WHERE rn = 1

WITH 
cte0 AS( --Join of tables (Creating Master Table).
    SELECT s.submission_date, s.submission_id, s.hacker_id, h.name
    FROM submissions s
    LEFT JOIN hackers h ON s.hacker_id = h.hacker_id
    WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15'),
cte1 AS( --Date & Sequence
    SELECT 
        submission_date,
        ROW_NUMBER() OVER(ORDER BY submission_date ASC) AS dt_seq
    FROM cte0
    GROUP BY submission_date),
cte2 AS( --All hackers with at least one submission.
    SELECT hacker_id, submission_date, COUNT(submission_id) AS sb_count
    FROM cte0
    GROUP BY hacker_id, submission_date
    HAVING COUNT(submission_id)>0),
cte3 AS( --All hackers with at least one submission each day.
    SELECT 
        hacker_id, submission_date,
        ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date) AS h_seq
    FROM cte2),
cte4 AS ( -- result of first query
    SELECT cte3.submission_date, COUNT(hacker_id) AS num_hackers
    FROM cte3
    JOIN cte1 ON cte1.submission_date = cte3.submission_date AND cte1.dt_seq = cte3.h_seq
    GROUP BY cte3.submission_date),
cte5 AS ( 
    SELECT 
        submission_date, hacker_id,
        RANK() OVER(PARTITION BY submission_date ORDER BY sb_count DESC, hacker_id ASC) AS rnk
    FROM cte2),
cte6 AS ( -- result of 2nd query
    SELECT submission_date, hacker_id 
    FROM cte5 
    WHERE rnk=1),
cte7 AS (
    SELECT hacker_id, name
    FROM cte0
    GROUP BY hacker_id, name)

SELECT cte4.submission_date, cte4.num_hackers, cte6.hacker_id, cte7.name
FROM cte4
JOIN cte6 ON cte6.submission_date = cte4.submission_date
JOIN cte7 ON cte7.hacker_id = cte6.hacker_id
ORDER BY cte4.submission_date;