We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. 15 Days of Learning SQL
  5. Discussions

15 Days of Learning SQL

  • praval8765419559
     + 0 comments

    Explanation: Now, the code below might seem daunting that's why it took me more than a day to figure out with a little help from various advance chat bots; more often than giving wrong response. The real challenge was to figure out the number of coders who have submitted at leat one submission from the start date to the current date, which is not mentioned clearly in the question itself, without the use of CTEs and Window Functions since my MySQL version was not allowing it Being from Statistics background and having has studied Probability theory, I can see that the question is ambiguous in its statement about the requirement for the first column.

    MySQL Code:

    SELECT
    dates.submission_date,
    IFNULL(consistent_counts.hacker_count, 0) AS distinct_hacker_count,
    top_hacker.hacker_id,
    top_hacker.name
FROM
    (SELECT DISTINCT submission_date FROM submissions) AS dates
LEFT JOIN
    (
        SELECT
            s1.submission_date,
            COUNT(s1.hacker_id) AS hacker_count
        FROM
            (SELECT DISTINCT submission_date, hacker_id FROM submissions) AS s1
        WHERE
            (SELECT COUNT(DISTINCT s2.submission_date)
             FROM submissions s2
             WHERE s2.hacker_id = s1.hacker_id AND s2.submission_date <= s1.submission_date
            ) = DATEDIFF(s1.submission_date, '2016-03-01') + 1
        GROUP BY
            s1.submission_date
    ) AS consistent_counts ON dates.submission_date = consistent_counts.submission_date
LEFT JOIN
    (
        SELECT
            daily_counts.submission_date,
            MIN(daily_counts.hacker_id) AS hacker_id,
            (SELECT name FROM hackers WHERE hacker_id = MIN(daily_counts.hacker_id)) AS name
        FROM
            (
                SELECT submission_date, hacker_id, COUNT(*) AS subs_count
                FROM submissions
                GROUP BY submission_date, hacker_id
            ) AS daily_counts
        JOIN
            (
                SELECT submission_date, MAX(subs_count) AS max_subs
                FROM (
                    SELECT submission_date, hacker_id, COUNT(*) AS subs_count
                    FROM submissions
                    GROUP BY submission_date, hacker_id
                ) AS inner_counts
                GROUP BY submission_date
            ) AS max_counts ON daily_counts.submission_date = max_counts.submission_date
                            AND daily_counts.subs_count = max_counts.max_subs
        GROUP BY
            daily_counts.submission_date
    ) AS top_hacker ON dates.submission_date = top_hacker.submission_date
ORDER BY
    dates.submission_date;