Hard: Question 2

SELECT a1.submission_date,
       hcnt,
       hid,
       name
FROM
  (SELECT submission_date,
          COUNT(hacker_id) AS hcnt
   FROM
     (SELECT m.submission_date,
             m.hacker_id,
             count(DISTINCT n.submission_date) AS ns
      FROM Submissions m
      JOIN Submissions n ON m.hacker_id = n.hacker_id
      AND m.submission_date >= n.submission_date
      GROUP BY 1,
               2
      HAVING datediff(submission_date, CONVERT('2016-03-01',date)) + 1 = ns) o
   GROUP BY submission_date) a1
JOIN
  (SELECT a.submission_date,
          min(a.hacker_id) AS hid,
          acnt
   FROM
     (SELECT submission_date,
             hacker_id,
             COUNT(submission_id) AS acnt
      FROM Submissions
      GROUP BY 1,
               2) a
   JOIN
     (SELECT submission_date,
             max(cnt) AS ycnt
      FROM
        (SELECT submission_date,
                hacker_id,
                COUNT(submission_id) AS cnt
         FROM Submissions
         GROUP BY 1,
                  2) x
      GROUP BY submission_date) y ON a.submission_date = y.submission_date
   AND acnt = ycnt
   GROUP BY 1,
            3) y1 ON a1.submission_date = y1.submission_date
JOIN Hackers b1 ON y1.hid = b1.hacker_id
ORDER BY 1;