SQL를 풉시다(4)

해커랭크 더 이상 풀 문제가 없어서
오늘을 마지막으로, LeetCode로 넘어갑니다.

깃허브 링크

---

1.Contest LeaderBoard

문제
You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!

The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of from your result.

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.

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 for which the submission belongs to, and score is the score of the submission.

Sample Input

Hackers Table:

Submissions Table:

Sample Output

4071 Rose 191
74842 Lisa 174
84072 Bonnie 100
4806 Angela 89
26071 Frank 85
80305 Kimberly 67
49438 Patrick 43
Explanation

Hacker 4071 submitted solutions for challenges 19797 and 49593, so the total score .

Hacker 74842 submitted solutions for challenges 19797 and 63132, so the total score

Hacker 84072 submitted solutions for challenges 49593 and 63132, so the total score .

The total scores for hackers 4806, 26071, 80305, and 49438 can be similarly calculated.

핵심

"최고점들의 합"
이 문제는 두 단계로 나눠서 생각하면 아주 쉬움.

각 해커가 각 챌린지에서 받은 점수들 중 최댓값(MAX)만 뽑아내기

그 최댓값들을 해커별로 합산(SUM)

단, 총점이 0점인 해커는 결과에서 제외

코드

SELECT 
    H.hacker_id, 
    H.name, 
    SUM(max_score) AS total_score
FROM Hackers H
JOIN (
    -- 1단계: 해커별, 챌린지별 최고 점수만 먼저 구하기
    SELECT hacker_id, MAX(score) AS max_score
    FROM Submissions
    GROUP BY hacker_id, challenge_id
) S ON H.hacker_id = S.hacker_id
GROUP BY H.hacker_id, H.name
-- 2단계: 총점이 0점보다 큰 경우만 출력
HAVING total_score > 0
-- 3단계: 점수 내림차순, 같으면 ID 오름차순 정렬
ORDER BY total_score DESC, H.hacker_id ASC;

---

2.Print Prime Numbers

문제
Write a query to print all prime numbers less than or equal to . Print your result on a single line, and use the ampersand () character as your separator (instead of a space).

For example, the output for all prime numbers would be:

2&3&5&7

핵심

문제 해결의 핵심숫자 목록 만들기
2부터 1000까지의 숫자 리스트가 필요

소수 판별 (나누어 떨어지는지 확인)
어떤 수 $n$이 소수이려면, 2부터 $n-1$까지의 나누어 떨어지면 안 됨

한 줄로 합치기
GROUP_CONCAT 함수를 사용하여 결과값들을 &로 잇기

코드

SET @num := 1;
SET @prime := 1;
SELECT GROUP_CONCAT(ELEM_N SEPARATOR '&')
FROM (
    SELECT @num := @num + 1 AS ELEM_N
    FROM information_schema.tables t1, information_schema.tables t2
    LIMIT 1000
) AS Table_N
WHERE NOT EXISTS (
    SELECT 1 
    FROM (
        SELECT @prime := @prime + 1 AS ELEM_P
        FROM information_schema.tables t3, information_schema.tables t4
        LIMIT 1000
    ) AS Table_P
    WHERE ELEM_P <= SQRT(ELEM_N) AND ELEM_N % ELEM_P = 0 AND ELEM_N <> ELEM_P
)
AND ELEM_N <= 1000;

---

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

Note: A specific contest can be used to screen candidates at more than one college, but each college only holds screening contest.

Input Format

The following tables hold interview data:

Contests: The contest_id is the id of the contest, hacker_id is the id of the hacker who created the contest, and name is the name of the hacker.

Colleges: The college_id is the id of the college, and contest_id is the id of the contest that Samantha used to screen the candidates.

Challenges: The challenge_id is the id of the challenge that belongs to one of the contests whose contest_id Samantha forgot, and college_id is the id of the college where the challenge was given to candidates.

View_Stats: The challenge_id is the id of the challenge, total_views is the number of times the challenge was viewed by candidates, and total_unique_views is the number of times the challenge was viewed by unique candidates.

Submission_Stats: The challenge_id is the id of the challenge, total_submissions is the number of submissions for the challenge, and total_accepted_submission is the number of submissions that achieved full scores.

Sample Input

Contests Table: Colleges Table: Challenges Table: View_Stats Table: Submission_Stats Table:

Sample Output

66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15

핵심

거대 통계 테이블을 바로 JOIN하지 않고, 서브쿼리에서 챌린지별로 미리 합계(SUM)를 구해 압축한 뒤 붙여야 데이터 오류와 성능 문제 막기

기록이 없는 챌린지도 결과에 포함되어야 하므로 LEFT JOIN을 사용하고, NULL 값 때문에 합계가 망가지지 않게 IFNULL 처리를 해주기

모든 조인과 합산이 끝난 후, HAVING 절을 통해 모든 통계 수치의 합이 0인 무의미한 데이터만 골라내기

코드

SELECT 
    con.contest_id, 
    con.hacker_id, 
    con.name, 
    SUM(ss.ts), 
    SUM(ss.tas), 
    SUM(vs.tv), 
    SUM(vs.tuv)
FROM Contests con
INNER JOIN Colleges col ON con.contest_id = col.contest_id
INNER JOIN Challenges cha ON col.college_id = cha.college_id
-- 1. 제출 통계를 챌린지별로 요약해서 조인
LEFT JOIN (
    SELECT challenge_id, 
           SUM(total_submissions) AS ts, 
           SUM(total_accepted_submissions) AS tas
    FROM Submission_Stats 
    GROUP BY challenge_id
) ss ON cha.challenge_id = ss.challenge_id
-- 2. 조회 통계를 챌린지별로 요약해서 조인
LEFT JOIN (
    SELECT challenge_id, 
           SUM(total_views) AS tv, 
           SUM(total_unique_views) AS tuv
    FROM View_Stats 
    GROUP BY challenge_id
) vs ON cha.challenge_id = vs.challenge_id
GROUP BY con.contest_id, con.hacker_id, con.name
-- 3. 모든 합계가 0인 경우 필터링 (NULL 방지를 위해 IFNULL 사용 권장)
HAVING (SUM(IFNULL(ss.ts, 0)) + SUM(IFNULL(ss.tas, 0)) + SUM(IFNULL(vs.tv, 0)) + SUM(IFNULL(vs.tuv, 0))) > 0
ORDER BY con.contest_id;

---

해커랭크는 여기서 끄읏.