15 Days of Learning SQL

  • + 0 comments
    Check this Out Guys  
    
    SELECT A.SUBMISSION_DATE , A.CNT , B.CL2 , B.NAME FROM 
    (
        SELECT TB8.SUBMISSION_DATE , COUNT(TB8.HACKER_ID) AS CNT FROM 
    (
        SELECT TB6.SUBMISSION_DATE , TB6.HACKER_ID FROM 
        (
            SELECT SUBMISSION_DATE , HACKER_ID 
            FROM SUBMISSIONS GROUP BY SUBMISSION_DATE , HACKER_ID
        )AS TB6
        WHERE TB6.HACKER_ID IN 
        (
            SELECT TB7.HACKER_ID FROM 
            (
                SELECT SUBMISSION_DATE , HACKER_ID
                FROM SUBMISSIONS GROUP BY SUBMISSION_DATE , HACKER_ID
            ) AS TB7
            WHERE TB7.SUBMISSION_DATE <= TB6.SUBMISSION_DATE
            GROUP BY TB7.HACKER_ID 
            HAVING COUNT(TB7.HACKER_ID) = DATEDIFF(TB6.SUBMISSION_DATE,'2016-03-01')+1  
        )
    ) AS TB8
    GROUP BY TB8.SUBMISSION_DATE
    ) AS A 
    JOIN 
    (
    SELECT A.SUBMISSION_DATE , A.CL2 , B.NAME FROM 
    (
        SELECT TB2.SUBMISSION_DATE ,
        (
            SELECT HACKER_ID
            FROM SUBMISSIONS 
            WHERE SUBMISSION_DATE = TB2.SUBMISSION_DATE
            GROUP BY SUBMISSION_DATE, HACKER_ID
            ORDER BY COUNT(HACKER_ID) DESC , HACKER_ID ASC LIMIT 1 
        ) AS CL2
        FROM 
        (SELECT DISTINCT (SUBMISSION_DATE) AS SUBMISSION_DATE FROM SUBMISSIONS)
        AS TB2
    ) AS A
    LEFT JOIN 
    HACKERS AS B 
    ON A.CL2 = B.HACKER_ID
    ) AS B 
    ON A.SUBMISSION_DATE = B.SUBMISSION_DATE 
    ORDER BY A.SUBMISSION_DATE