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. ;


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