[SQL] 해커랭크: Basic Join 문제풀이

juyeon·2022년 11월 9일

SQL

목록 보기

5/9

출처: HackerRank: Basic Join

질문

Easy

1. Population Census

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

Input Format

The CITY and COUNTRY tables are described as follows:

풀이

두 테이블의 key 값으로 JOIN 해야한다.
-> 어떤 join을 쓸까?

SELECT SUM(CITY.POPULATION)
FROM CITY
    INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Asia';

2. African Cities

Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

Input Format

The CITY and COUNTRY tables are described as follows:

풀이

SELECT CITY.NAME
FROM CITY
JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Africa';

3. Average Population of Each Continent

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

Input Format

CITY and COUNTRY tables

풀이

SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION))
FROM CITY
JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY COUNTRY.CONTINENT;

다시풀기

alias 사용

SELECT c2.CONTINENT
     , FLOOR(AVG(c1.POPULATION))
FROM CITY c1
INNER JOIN COUNTRY c2 ON c1.COUNTRYCODE = c2.CODE
GROUP BY c2.CONTINENT

Medium

1. ⭐ The Report

report containing three columns: Name, Grade and Mark.

descending order by grade

If same grade,

8-10 grade: order those particular students by their name alphabetically.

1-7 grade: order those particular students by their marks in ascending order.

Print "NULL" as the name if the grade is less than 8.

Students

Grades

Sample Input

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88 
Scarlet 8 78
NULL 7 63
NULL 7 68

Explanation

풀이

두 테이블 조인 -> 범위로 조인?
IF문: IF(조건문, 참일때 값, 거짓일때 값)
정렬: grade, name, marks

SELECT IF(g.Grade < 8, NULL, s.Name), g.Grade, s.Marks
FROM Students AS s
INNER JOIN Grades AS g ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark
ORDER BY g.Grade DESC, s.Name ASC, s.Marks ASC;

다시풀기

처음 풀었을 때와 똑같이 풀면 놓치는 경우가 몇개 있었다.

1. 8등급 이하 null 표시

SELECT에서 CASE WHEN 구문을 사용했다. 이 문제에서는 IF와 같은 기능이지만, 일반적으로 CASE WHEN이 더 유연한 것 같다.

SELECT
      CASE WHEN g.Grade < 8 THEN NULL
           ELSE s.Name
      END
     , g.Grade
     , s.Marks
...

2. 등급별 정렬 조건

기본 정렬은 grade 내림차순이지만, 8등급 미만은 marks를, 8등급 이상이면 name을 추가로 오름차순 정렬해야한다.
이때 다시 CASE WHEN으로 조건을 나눈다.

CASE~WHEN~ELSE

ORDER BY g.Grade DESC,
         CASE WHEN g.Grade < 8 THEN s.Marks 
              ELSE s.Name
         END ASC;

CASE WHEN을 두개 쓸 경우

ORDER BY g.Grade DESC
       , CASE WHEN g.Grade < 8 THEN s.Marks END ASC
       , CASE WHEN g.Grade >= 8 THEN s.Name END ASC;

이때, ASC는 END 이후에 써야한다.

최종 쿼리문.

SELECT
      CASE WHEN g.Grade < 8 THEN NULL
           ELSE s.Name
      END AS Name
     , g.Grade
     , s.Marks
FROM STUDENTS s
INNER JOIN GRADES g ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark
ORDER BY g.Grade DESC,
         CASE WHEN g.Grade < 8 THEN s.Marks 
              ELSE s.Name
         END ASC;

2. Top Competitors

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.

Sample Input

Hackers Table:
Difficulty Table:
Challenges Table:
Submissions Table:

Sample Output

90411 Joe

Explanation

Hacker 86870 got a score of 30 for challenge 71055 with a difficulty level of 2, so 86870 earned a full score for this challenge.

Hacker 90411 got a score of 30 for challenge 71055 with a difficulty level of 2, so 90411 earned a full score for this challenge.

Hacker 90411 got a score of 100 for challenge 66730 with a difficulty level of 6, so 90411 earned a full score for this challenge.

Only hacker 90411 managed to earn a full score for more than one challenge, so we print the their hacker_id and name as 2 space-separated values.

풀이

full score 1개보다 더 많이(= 둘 이상) 맞은 경우 찾기
다 조인해야함
정렬: 만점자의 총 숫자, hacker_id
즉 같은 난이도의 같은 점수(해당 난이도의 만점 점수를 획득)

SELECT h.hacker_id, h.name
FROM Submissions AS s
    INNER JOIN Challenges AS c ON s.challenge_id = c.challenge_id
    INNER JOIN Difficulty AS d ON c.difficulty_level = d.difficulty_level
    INNER JOIN Hackers AS h ON s.hacker_id = h.hacker_id
WHERE d.score = s.score AND d.difficulty_level = c.difficulty_level
GROUP BY h.hacker_id, h.name
HAVING COUNT(h.hacker_id) > 1
ORDER BY COUNT(h.hacker_id) DESC, h.hacker_id;

3. Ollivander's Inventory

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

Input Format

Wands

Wands_Property
: The mapping between code and age is one-one, meaning that if there are two pairs, $(code_1, age_1)$ and $(code_2, age_2)$ , then $code_1 \neq code_2$ and $age_1 \neq age_2.$

Sample Input

Wands Table:
Wands_Property Table:

Sample Output

9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3
5 45 6020 2
14 40 5408 1

Explanation

The data for wands of age 45 (code 1):

The data for wands of age 40 (code 2):

The data for wands of age 20 (code 4):

The data for wands of age 17 (code 5):

풀이

시도 1

두 테이블 join할 것
조건:
- non-evil: is_evil=0인 것만 고르기
- 돈 최솟값
정렬: power 내림차순, age 내림차순

SELECT id
	 , age
     , coins_needed
     , power
FROM Wands AS W
	 INNER JOIN Wands_Property AS WP ON W.code = WP.code
WHERE WP.is_evil = 0
      AND W.coins_needed = (SELECT MIN(W2.coins_needed)
                            FROM Wands AS W2
                            	 INNER JOIN Wands_Property AS WP2 ON W2.code = WP2.code
                            WHERE W2.power = W.power
                            	  AND WP2.age = WP.age)
ORDER BY W.power DESC, WP.age DESC;

데이터리안 풀이

이 문제에서 MySQL은 구버전이라 윈도우 함수를 사용할 수 없기 때문에는 MS SQL Server 사용

age, power를 기준으로 그룹화하여 coin 오름차순으로 번호를 매겨보면:

SELECT w.id
     , w.code
     , w.coins_needed
     , wp.age
     , w.power
     , ROW_NUMBER() OVER (PARTITION BY wp.age, w.power ORDER BY w.coins_needed ASC) AS rn
FROM Wands w
     INNER JOIN Wands_Property wp ON w.code = wp.code
WHERE wp.is_evil = 0

즉, 윈도우 함수로 구한 row number = 1인 경우가 가장 적은 coin인 wand 이므로, 위의 쿼리를 FROM절 서브쿼리로 넣어서 row number = 1인 경우만 출력한다.

예를 들어, age=158, power=1인 wand가 3개 존재하는데, 이중 가장 coin이 작은 id=775번만 선택한다

SELECT id
     , age
     , coins_needed
     , power
FROM (
    SELECT w.id
         , wp.age
         , w.coins_needed
         , w.power
         , ROW_NUMBER() OVER (PARTITION BY wp.age, w.power ORDER BY w.coins_needed ASC) AS rn
    FROM Wands w
         INNER JOIN Wands_Property wp ON w.code = wp.code
    WHERE wp.is_evil = 0
) t
WHERE rn = 1
ORDER BY power DESC, age DESC

4. Challenges

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format

The following tables contain challenge data:

Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

Sample Input 0

Hackers Table:

Challenges Table:

Sample Output 0

21283 Angela 6
88255 Patrick 5
96196 Lisa 1

Sample Input 1

Hackers Table:

Challenges Table:

Sample Output 1

12299 Rose 6
34856 Angela 6
79345 Frank 4
80491 Patrick 3
81041 Lisa 1

Explanation

For Sample Case 0, we can get the following details:

Students 5077 and 62743 both created 4 challenges, but the maximum number of challenges created is 6 so these students are excluded from the result.

For Sample Case 1, we can get the following details:

Students 12999 and 34856 both created 6 challenges. Because 6 is the maximum number of challenges created, these students are included in the result.

풀이

챌린지 개수를 셀건데, 중복된 개수가 나오면? 최대 챌린지 갯수보다 작을 경우에는 아웃!
- 중복된 챌린지 개수는 어떻게 세야할까? 챌린지 개수로 group by 했을 때 count가 1이 아니라면 중복된 것!
- 즉, 챌린지 개수의 경우의 수는 두가지. 중복되지 않거나, 중복되더라도 챌린지 개수의 최댓값이거나
정렬: 총 문제개수 내림차순, 해커 아이디 오름차순

SELECT H.hacker_id, H.name, COUNT(*) AS challenges_created
FROM Hackers AS H
INNER JOIN Challenges AS C ON H.hacker_id = C.hacker_id
GROUP BY H.hacker_id, H.name
HAVING challenges_created IN (SELECT sub1.challenges_created
                              FROM (SELECT COUNT(*) AS challenges_created
                                    FROM Challenges AS C2
                                    GROUP BY C2.hacker_id) AS sub1
                              GROUP BY sub1.challenges_created
                              HAVING COUNT(*) = 1)
    OR challenges_created = (SELECT MAX(sub2.challenges_created)
                             FROM (SELECT COUNT(*) AS challenges_created
                                   FROM Challenges AS C3
                                   GROUP BY C3.hacker_id) AS sub2)
ORDER BY challenges_created DESC, H.hacker_id ASC;

5. Contest Leaderboard

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 0 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 $=95+max(43, 96)=191$ .

Hacker 74842 submitted solutions for challenges 19797 and 63132, so the total score $=max(98, 5) + 76=174$ .

Hacker 84072 submitted solutions for challenges 49593 and 63132, so the total score $=100+ 0=100$ .

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

풀이

해커 아이디, 이름, 총 점수를 출력
총 점수는
- 해커 아이디, 챌린지 아이디로 group by
- 같은 챌린지를 여러번 했을 경우에는, max 점수
- 총 점수가 0인 데이터는 제외
정렬: 총 점수 내림차순, 해커 아이디 오름차순

SELECT H.hacker_id, H.name, sub2.total_score
FROM (SELECT sub.hacker_id, SUM(max_score) AS total_score
      FROM (SELECT hacker_id, MAX(score) AS max_score
            FROM Submissions 
            GROUP BY hacker_id, challenge_id) AS sub
      GROUP BY sub.hacker_id
      HAVING total_score != 0) AS sub2
 INNER JOIN Hackers H ON sub2.hacker_id = H.hacker_id
ORDER BY sub2.total_score DESC, H.hacker_id ASC;

데이터리안 풀이

우선 Submissions에서 해커 id별로, 챌린지 id별로 그룹화하여 최고점을 구함

SELECT hacker_id
	 , challenge_id
     , MAX(score) score_max
FROM Submissions
GROUP BY hacker_id, challeng_id

그리고 그걸 FROM절 서브쿼리로 놓은 뒤 해커 정보(Hackers)와 JOIN
그리고, 다시 해커 id와 이름으로 그룹화 후 총점 0점을 제외하여 정렬

SELECT h.hacker_id
	 , h.name
     , SUM(score_max) total_score -- 점수 합계를 구해야함
FROM (
    SELECT hacker_id
         , challenge_id
         , MAX(score) score_max
    FROM Submissions
    GROUP BY hacker_id, challenge_id
) s
	INNER JOIN Hackers h ON h.hacker_id = s.hacker_id
GROUP BY h.hacker_id, h.name
HAVING total_score != 0 -- GROUP BY 이후에 조건을 걸 때는 HAVING 사용
ORDER BY total_score DESC, h.hacker_id

juyeon

내 인생의 주연

이전 포스트

[SQL] 해커랭크: Aggregation 문제풀이

다음 포스트

[SQL] 해커랭크: Basic Join 문제풀이

SQL

질문

Easy

1. Population Census

Input Format

풀이

2. African Cities

풀이

3. Average Population of Each Continent

Input Format

CITY and COUNTRY tables

풀이

다시풀기

Medium

1. ⭐ The Report

Students

Grades

Sample Input

Sample Output

Explanation

풀이

다시풀기

1. 8등급 이하 null 표시

2. 등급별 정렬 조건

최종 쿼리문.

2. Top Competitors

Input Format

Sample Input

Sample Output

Explanation

풀이

3. Ollivander's Inventory

Input Format

Sample Input

Sample Output

Explanation

풀이

시도 1

데이터리안 풀이

4. Challenges

Input Format

Sample Input 0

Sample Output 0

Sample Input 1

Sample Output 1

Explanation

풀이

5. Contest Leaderboard

Input Format

Sample Input

Sample Output

Explanation

풀이

데이터리안 풀이

[SQL] 해커랭크: Aggregation 문제풀이

[SQL] 해커랭크: Advanced Join 문제풀이

0개의 댓글