HackerRank MySQL - Interviews




Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are 0



  1. SELECT CON.CONTEST_ID,
  2. CON.HACKER_ID,
  3. CON.NAME,
  4. SUM(TOTAL_SUBMISSIONS),
  5. SUM(TOTAL_ACCEPTED_SUBMISSIONS),
  6. SUM(TOTAL_VIEWS),
  7. SUM(TOTAL_UNIQUE_VIEWS)
  8. FROM CONTESTS CON
  9. JOIN COLLEGES COL ON CON.CONTEST_ID = COL.CONTEST_ID
  10. JOIN CHALLENGES CHA ON COL.COLLEGE_ID = CHA.COLLEGE_ID
  11. LEFT JOIN
  12. (SELECT CHALLENGE_ID,
  13. SUM(TOTAL_VIEWS) AS TOTAL_VIEWS,
  14. SUM(TOTAL_UNIQUE_VIEWS) AS TOTAL_UNIQUE_VIEWS
  15. FROM VIEW_STATS
  16. GROUP BY CHALLENGE_ID) VS ON CHA.CHALLENGE_ID = VS.CHALLENGE_ID
  17. LEFT JOIN
  18. (SELECT CHALLENGE_ID,
  19. SUM(TOTAL_SUBMISSIONS) AS TOTAL_SUBMISSIONS,
  20. SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSIONS
  21. FROM SUBMISSION_STATS
  22. GROUP BY CHALLENGE_ID) SS ON CHA.CHALLENGE_ID = SS.CHALLENGE_ID
  23. GROUP BY CON.CONTEST_ID,
  24. CON.HACKER_ID,
  25. CON.NAME
  26. HAVING SUM(TOTAL_SUBMISSIONS) != 0
  27. OR SUM(TOTAL_ACCEPTED_SUBMISSIONS) != 0
  28. OR SUM(TOTAL_VIEWS) != 0
  29. OR SUM(TOTAL_UNIQUE_VIEWS) != 0
  30. ORDER BY CONTEST_ID;




codesadda.com

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