Untitled

 avatar
user_9914919852
mysql
2 years ago
1.1 kB
1
Indexable
Never
SELECT sd, cnt, h.hacker_id, h.name
FROM(

SELECT sd, 
    (
        SELECT COUNT(DISTINCT h.hacker_id) FROM Hackers h JOIN Submissions s ON s.hacker_id = h.hacker_id WHERE s.submission_date <= sd AND
            (
                SELECT COUNT(DISTINCT submission_date) FROM Hackers hh JOIN Submissions ss ON ss.hacker_id = hh.hacker_id WHERE hh.hacker_id = h.hacker_id
            AND ss.submission_date <= sd
            )
            = 1+DATEDIFF(sd,'2016-03-01')
    ) AS cnt,
(
    SELECT h.hacker_id FROM Hackers h JOIN Submissions s ON s.hacker_id = h.hacker_id WHERE s.submission_date = sd GROUP BY h.hacker_id ORDER BY COUNT(s.submission_id) DESC, h.hacker_id LIMIT 1
) AS hacker_id,
    (
        SELECT COUNT(s.submission_id) FROM Hackers h JOIN Submissions s ON s.hacker_id = h.hacker_id WHERE s.submission_date = sd GROUP BY h.hacker_id ORDER BY COUNT(s.submission_id) DESC, h.hacker_id LIMIT 1
    ) AS hacker_cnt

FROM
    (
        SELECT submission_date sd
        FROM Submissions
        GROUP BY submission_date
    ) AS date_tbl

) AS TBL2
JOIN Hackers h ON h.hacker_id = TBL2.hacker_id
ORDER BY sd