## /*
## Enter your query here.
## Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
## */
## WITH 
## data AS(
##     SELECT * 
##     FROM Submissions
##     WHERE submission_date BETWEEN
##     '2016-03-01' AND '2016-03-15'
## ),
## daily_subm_cnt AS(
##     SELECT hacker_id, submission_date,
##     COUNT(*) subm_cnt
##     FROM data
##     GROUP BY hacker_id, submission_date
## ),
## accum_subm_cnt AS(
##     SELECT submission_date,
##     hacker_id,
##     subm_cnt,
##     SUM(1) OVER
##     (PARTITION BY hacker_id 
##      ORDER BY submission_date) accum_cnt
##     FROM daily_subm_cnt
## ),
## atl_one AS(
##     SELECT submission_date, subm_cnt, hacker_id, accum_cnt
##     FROM accum_subm_cnt
##     WHERE accum_cnt > DATEDIFF(DAY, '2016-03-01', submission_date)
## ),
## ranked_cnt AS(
##     SELECT submission_date, subm_cnt, hacker_id, accum_cnt,
##     RANK() OVER(
##         PARTITION BY submission_date 
##         ORDER BY subm_cnt DESC) cnt_rnk
##     FROM accum_subm_cnt
## ),
## ranked_hacker AS(
##     SELECT submission_date, subm_cnt, hacker_id, cnt_rnk,
##     RANK() OVER(
##         PARTITION BY submission_date
##         ORDER BY hacker_id ASC) id_rnk
##     FROM ranked_cnt
##     WHERE cnt_rnk = 1
## ),
## daily_hacker_cnt AS(
##     SELECT submission_date, COUNT(DISTINCT(hacker_id)) hacker_cnt
##     FROM atl_one
##     GROUP BY submission_date
## )
## 
## SELECT dhc.submission_date, dhc.hacker_cnt,
## rh.hacker_id, h.name
## FROM daily_hacker_cnt dhc
## JOIN ranked_hacker rh
## ON rh.submission_date = dhc.submission_date
## AND rh.id_rnk = 1
## JOIN Hackers h
## ON h.hacker_id = rh.hacker_id;**