with t as (
select submission_date,
       h.hacker_id,
       name,
       count( submission_id) as br, 
       dense_rank() over (partition by h.hacker_id order by submission_date) as rbr,
       dense_rank() over (partition by submission_date order by count(*) desc,h.hacker_id ) as rk1   
from hackers h inner join submissions s on h.hacker_id=s.hacker_id
group by submission_date,h.hacker_id,name
    ),
tt as (
select t.*,
       dense_rank() over (partition by submission_date order by br desc,hacker_id ) as rk,
       count( hacker_id) over (partition by submission_date) as broj 
from t where rbr=datediff(day,'2016-03-01',submission_date)+1
        )
select t.submission_date,tt.broj,t.hacker_id,t.name from t inner join tt on t.submission_date=tt.submission_date
where tt.rk=1 and t.rk1=1
order by 1

SELECT 
    b.submission_date, COALESCE(a.contSub,0), b.id, c.name
FROM (
    SELECT o.submission_date, MIN(o.hacker_id) AS id
    FROM (
        SELECT n.submission_date, n.hacker_id,COUNT(n.submission_id)
        FROM Submissions n
        GROUP BY submission_date, hacker_id
        HAVING COUNT(submission_id)>= ALL(
              SELECT COUNT(m.submission_id)
              FROM Submissions m
              GROUP BY m.submission_date, m.hacker_id
              HAVING m.submission_date=n.submission_date
          )
    ) AS o
    GROUP BY submission_date
    ) AS b
LEFT JOIN (
    SELECT aa.submission_date, COUNT(DISTINCT aa.hacker_id) AS contSub
    FROM Submissions AS aa
    WHERE aa.submission_date='2016-03-01' 
    OR submission_date<ALL(
        SELECT bb.submission_date
        FROM (
            SELECT tdy.hacker_id, tdy.submission_date, ytd.submission_date AS SubmittedYtd
            FROM Submissions tdy LEFT JOIN Submissions ytd
            ON DATE_ADD(tdy.submission_date, INTERVAL -1 DAY)= ytd.submission_date 
            AND ytd.hacker_id=tdy.hacker_id    
        ) AS bb
        WHERE aa.hacker_id=bb.hacker_id AND bb.submission_date<> STR_TO_DATE('2016-03-01', '%Y-%m-%d') AND bb.SubmittedYtd IS NULL
    )
    GROUP BY aa.submission_date
) AS a 
ON a.submission_date=b.submission_date
JOIN Hackers c ON b.id=c.hacker_id
ORDER BY a.submission_date ASC

WITH
-- count each hacker's number of submissions
a AS
(
    SELECT DISTINCT s.hacker_id hid, 
                    s.submission_date sdt,
                    COUNT(s.submission_id) csid
    FROM Submissions s
    GROUP BY s.submission_date, s.hacker_id
),

-- find out hackers who submitted the most by date and select the one with smallest id
b AS
(
    SELECT sdt, MIN(hid) mhid
    FROM a x
    WHERE csid = (SELECT MAX(csid) FROM a y WHERE x.sdt = y.sdt)
    GROUP BY sdt
),

-- number each of the 15 days by order
c AS
(
    SELECT sdt, ROW_NUMBER()OVER(ORDER BY sdt) rn
    FROM b
),

-- filter out those who started submitting since the first day
e AS
(
    SELECT hid, sdt, rn FROM
    (SELECT hid, a.sdt, rn, MIN(a.sdt)OVER(PARTITION BY hid) mm
     FROM a JOIN c ON a.sdt = c.sdt
     ) AS d
    WHERE mm = "2016-03-01"
),

-- renumber each row by hacker id and date - for each person, when the row number equal to the day number, it is a consecutive submission.

g AS
(
SELECT * FROM
(SELECT *,
       CASE WHEN rn = nrn THEN "COOL"
            ELSE "TRASH"
       END AS tag
FROM(
    SELECT *,
           ROW_NUMBER()OVER(PARTITION BY hid ORDER BY hid, sdt) nrn
    FROM e) AS f) AS ff
WHERE tag = "COOL"
),

-- count number of hackers of each day after filtering out COOL rows
i AS
(
SELECT sdt, COUNT(hid) cc
FROM g
GROUP BY sdt
)

-- combine table b and table i
SELECT i.sdt, i.cc, b.mhid, h.name
FROM i JOIN b ON b.sdt = i.sdt
       JOIN Hackers h ON h.hacker_id = b.mhid
ORDER BY i.sdt