hackerrank - Basic Join - Top Competitors

这题有多个table，尽量避免全部join在一起，可以两两join，找相同

---

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

---

Input Format

The following tables contain contest data:

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

• Difficulty: The difficult_level is the level of difficulty of the challenge, and score is the score of the challenge for the difficulty level. 

• Challenges: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker who created the challenge, and difficulty_level is the level of difficulty of the challenge. 

• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge that the submission belongs to, and score is the score of the submission. 

---

---

这道题：

1. 首先找出每个学生每道题的最高分数

2. 每个学生提交的问题分数为满分，且不止一题拿到了满分，则output该学生

第一个小问直接在submissions的table中即可得到，使用group by hacker_id和challenge_id；第二个问题需要join Challenge table 和Difficulty table，这里注意的是Challenge table里面的hacker_id为创建此题的hacker_id，而不是提交问题的hacker。最后Hacker table可以跟Submissions table join来找出对应的name

coding 如下：

Select t1.hacker_id as hacker_id, max(t1.name) as name
from (
select s.hacker_id as hacker_id, challenge_id, max(score) as score, max(name) as name
from Submissions s inner join Hackers h
    on s.hacker_id = h.hacker_id
Group by hacker_id, challenge_id) t1
inner join
(
select c.challenge_id as challenge_id, d.score as full_score
from Challenges c inner join Difficulty d 
on c.difficulty_level = d.difficulty_level) t2
on t1.challenge_id = t2.challenge_id
where t1.score = t2.full_score
group by t1.hacker_id
having count(t1.challenge_id) > 1
order by count(t1.challenge_id) desc, t1.hacker_id;