WITH submissions_by_date_and_hackers AS (
  SELECT s.submission_date, s.hacker_id, COUNT(s.hacker_id) AS cnt
  FROM submissions AS s
  GROUP BY s.submission_date, s.hacker_id
),
active_hacker_count_by_date AS (
  SELECT a.submission_date, COUNT(a.hacker_id) AS active_cnt
  FROM
  (
	SELECT s.submission_date, s.hacker_id
	FROM submissions_by_date_and_hackers AS s
	INNER JOIN submissions_by_date_and_hackers AS s2 ON s.hacker_id = s2.hacker_id AND s.submission_date >= s2.submission_date
	GROUP BY s.submission_date, s.hacker_id
	HAVING COUNT(s2.submission_date) = DATEDIFF(day, '2016-03-01', s.submission_date) + 1
  ) AS a
  GROUP BY a.submission_date
),
best_hackers_by_date AS (
  SELECT a.submission_date, a.hacker_id
  FROM 
  (
	SELECT s.submission_date, s.hacker_id,
	RANK() OVER(PARTITION BY s.submission_date ORDER BY s.cnt DESC, s.hacker_id ASC) AS rang
	FROM submissions_by_date_and_hackers AS s
  ) AS a
  WHERE rang = 1
)

SELECT a.submission_date, a.active_cnt, b.hacker_id, h.name
FROM hackers AS h
INNER JOIN best_hackers_by_date AS b ON h.hacker_id = b.hacker_id
INNER JOIN active_hacker_count_by_date AS a ON a.submission_date = b.submission_date;