57.sql

-- https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem

SELECT SUBMISSION_DATE,
(SELECT COUNT(DISTINCT HACKER_ID) FROM SUBMISSIONS SUB2  
 WHERE SUB2.SUBMISSION_DATE = SUB1.SUBMISSION_DATE AND
    (SELECT COUNT(DISTINCT SUB3.SUBMISSION_DATE) FROM SUBMISSIONS SUB3
     WHERE SUB3.HACKER_ID=SUB2.HACKER_ID AND 
     SUB3.SUBMISSION_DATE<SUB1.SUBMISSION_DATE)=dateDIFF(SUB1.SUBMISSION_DATE,'2016-03-01')),
(SELECT HACKER_ID FROM SUBMISSIONS SUB2 WHERE SUB2.SUBMISSION_DATE=SUB1.SUBMISSION_DATE
 GROUP BY HACKER_ID ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS AL,
(SELECT NAME FROM HACKERS WHERE HACKER_ID=AL) FROM
(SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) SUB1
GROUP BY SUBMISSION_DATE