HackerRank MySQL - 15 Days of Learning SQL

Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.

Write a query to print total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

  1. SELECT t1.submission_date, hkr_cnt, t2.hacker_id, name
  2. FROM (SELECT p1.submission_date,
  3. COUNT(DISTINCT p1.hacker_id) AS hkr_cnt
  4. FROM (SELECT submission_date, hacker_id,
  5. @h_rnk := CASE WHEN @h_grp != hacker_id THEN 1 ELSE @h_rnk+1 END AS hacker_rank,
  6. @h_grp := hacker_id AS hacker_group
  7. FROM (SELECT DISTINCT submission_date, hacker_id
  8. FROM submissions
  9. ORDER BY hacker_id, submission_date) AS a,
  10. (SELECT @h_rnk := 1, @h_grp := 0) AS r) AS p1
  11. JOIN (SELECT submission_date,
  12. @d_rnk := @d_rnk + 1 AS date_rank
  13. FROM (SELECT DISTINCT submission_date
  14. FROM submissions
  15. ORDER BY submission_date) AS b,
  16. (SELECT @d_rnk := 0) r) AS p2
  17. ON p1.submission_date = p2.submission_date
  18. AND hacker_rank = date_rank
  19. GROUP BY p1.submission_Date) AS t1
  20. JOIN (SELECT submission_date, hacker_id, sub_cnt,
  21. @s_rnk := CASE WHEN @d_grp != submission_date THEN 1 ELSE @s_rnk+1 END AS max_rnk,
  22. @d_grp := submission_date AS date_group
  23. FROM (SELECT submission_date, hacker_id, COUNT(*) AS sub_cnt
  24. FROM submissions AS s
  25. GROUP BY submission_date, hacker_id
  26. ORDER BY submission_date, sub_cnt DESC, hacker_id) AS c,
  27. (SELECT @s_rnk := 1, @d_grp := 0) AS r) AS t2
  28. ON t1.submission_date = t2.submission_date AND max_rnk = 1
  29. JOIN hackers AS h ON h.hacker_id = t2.hacker_id
  30. ORDER BY t1.submission_date
  31. ;
Please click on the like button if it worked

Solution not working or have any suggestions? Please send an email to [email protected]

donate a cup of tea :)

Join Our Facebook Group

Share this solution


Codesadda.com is your home of programming solutions, tutorials, video tutorials and much more. Sign Up for our weekly newsletter to get update about new content.

Like us on Facebook | Connect with us on LinkedIn | Subscribe our Channel on Youtube