[HackerRank SQL] 15 Days of Learning SQL

www.hackerrank.com/challenges/15-days-of-learning-sql/problem

15 Days of Learning SQL | HackerRank

Julia conducted a  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  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.

Input Format

The following tables hold contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

  

• Submissions: The submission_date is the date of the submission, submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, and score is the score of the submission. 

  

---

Answer

select
    submission_date,
    (select count(distinct hacker_id)
     from submissions as s2
     where s2.submission_date = s1.submission_date and
     (select count(distinct s3.submission_date)
      from submissions as s3
      where s3.hacker_id = s2.hacker_id and s3.submission_date < s1.submission_date) = datediff(s1.submission_date, '2016-03-01')),
    (select hacker_id
     from submissions as s2
     where s2.submission_date = s1.submission_date
     group by hacker_id
     order by count(submission_id) desc, hacker_id limit 1) as id,
    (select name from hackers where hacker_id = id)
from
    (select distinct submission_date from submissions) as s1
group by submission_date;

start_date는 2016-03-01이며 end_date는 2016-03-16이다.

join 보다는 서브쿼리 문을 활용하는것이 더 좋다.

Result