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

  • jr_josias2
     + 0 comments

    I was pursuing the wrong solution, as the problem statement said to print the total of unique hackers that had submitted to every day, I thought the whole period of 15 days, not the up-to-day.

    After struggling a lot, to calculate the distinct hackers up-to-day, that had submitted work on previous days, I decided to sneak and peak in here and found the solution from @praval8765419559, although very resourceful, I thought it hard to comprehend, and I decided to reverse engineering his solution, and here is MySQL script that worked:

    SELECT
    min_hk_id.submission_date AS submission_date,
    hacker_counts.hacker_count,
    MIN(min_hk_id.hacker_id) AS hacker_id,
    (SELECT name FROM hackers WHERE hacker_id = MIN(min_hk_id.hacker_id)) AS name
FROM(
    SELECT
        submission_date,
        hacker_id,
        COUNT(hacker_id) AS sub_counts
    FROM submissions
    GROUP BY 1,2
) AS min_hk_id
JOIN(
    SELECT submission_date, MAX(sub_counts) AS sub_counts
    FROM(
        SELECT
            submission_date,
            hacker_id,
            COUNT(hacker_id) AS sub_counts
        FROM submissions
        GROUP BY 1,2
    ) as inner_counts
    GROUP BY 1
) AS max_counts
ON min_hk_id.submission_date = max_counts.submission_date AND min_hk_id.sub_counts = max_counts.sub_counts
JOIN(
    SELECT s.submission_date, COUNT(*) AS hacker_count
    FROM (
      SELECT d1.hacker_id, d1.submission_date
      FROM (SELECT DISTINCT hacker_id, submission_date FROM submissions) d1
      JOIN (SELECT DISTINCT hacker_id, submission_date FROM submissions) d2
        ON d2.hacker_id = d1.hacker_id
       AND d2.submission_date <= d1.submission_date
      GROUP BY d1.hacker_id, d1.submission_date
      HAVING COUNT(*) = DATEDIFF(d1.submission_date, DATE '2016-03-01') + 1
    ) AS s
    GROUP BY s.submission_date
) AS hacker_counts
ON hacker_counts.submission_date = min_hk_id.submission_date
GROUP BY min_hk_id.submission_date,hacker_counts.hacker_count

    Was a great challenge, even though I failed, I could refresh older concepts and grasp new ones.

    `