줄리아가 방금 코딩 대회를 마쳤는데, 리더보드 조립하는 데 당신의 도움이 필요해요! 두 개 이상의 과제에 대해 full 점수를 획득한 해커의 각각의 hacker_id 및 이름을 인쇄하는 쿼리를 작성합니다.
해커가 전체 점수를 획득한 총 시도 횟수를 내림차순으로 출력을 정렬합니다. 둘 이상의 해커가 동일한 횟수의 도전에서 전체 점수를 받은 경우 오름차순 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.
•
오답
SELECT
*
FROM (
SELECT
s.submission_id submission_id,
s.hacker_id hacker_id,
s.challenge_id challenge_id,
s.score score,
c.difficulty_level difficulty_level,
d.score full_score,
h.name name
FROM Submissions s
LEFT JOIN Challenges c ON s.challenge_id = c.challenge_id
LEFT JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
LEFT JOIN Hackers h ON s.hacker_id = h.hacker_id
WHERE s.score = d.score
) AS t
GROUP BY t.hacker_id
HAVING COUNT(t.submission_id) > 1
ORDER BY t.hacker_id ASC
LIMIT 5
;
Python
복사
•
정답
SELECT
h.hacker_id,
h.name
FROM Submissions s
JOIN Hackers h ON s.hacker_id = h.hacker_id
JOIN Challenges c ON s.challenge_id = c.challenge_id
JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
WHERE s.score = d.score
GROUP BY 1, 2
HAVING COUNT(s.submission_id) > 1
ORDER BY COUNT(s.submission_id) DESC, h.hacker_id ASC
-- LIMIT 5
;
JavaScript
복사