[HackerRank/SQL] Basic Join

---

Population Census

Problem

Submissions Leaderboard Discussions
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:

solution

SELECT SUM(c.population)
FROM city as c
JOIN country as co
ON c.countrycode=co.code
WHERE co.continent='Asia'

---

African Cities

Problem

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.

solution

SELECT c.name
FROM city as c
JOIN country as co
ON c.countrycode=co.code
WHERE co.continent='Africa'

---

Average Population of Each Continent

Problem

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.

solution

SELECT co.continent,FLOOR(AVG(c.population))
FROM city as c
JOIN country as co
ON c.countrycode=co.code
GROUP BY co.continent

The Report

Problem

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

Sample Input

Sample Output

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

Note

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

Explanation

Consider the following table with the grades assigned to the students:

So, the following students got 8, 9 or 10 grades:

Maria (grade 10)
Jane (grade 9)
Julia (grade 9)
Scarlet (grade 8)

solution

SELECT name,grade,mark
grade<8 미만은 이름 대신 NULL 출력 --IF문사용
ORDER BY grade --정렬조건1
grade(8-10) 이름 있음/name 기준 오름차순 --정렬조건2
grade(1-7) 이름 없음/mark 기준 오름차순 --정렬조건3
students 테이블에 marks컬럼은 grades테이블의 min_mark,max_mark를 활용해 INNER JOIN

SELECT IF(g.grade < 8, NULL, s.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, s.name, s.marks

The Competitors

Problem

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.

solution

hacker:해커아이디와 이름
difficulty: 챌린지의 어려움 레벨,점수
challenges:챌린지 아이디, 해커아이디: 챌린지 만든사람, difficulty level
submissions: 서비미션아이디,해커아이디 :submission만든사람
챌린지 아이디: 서브미션에 속한 것서브미션 점수

SELECT hacker_id , name
1개 이상 챌린지에서 full scores 획득한 사람 -- 조건
full score을 얻은 해커의 total challenge 갯수로 내림차순-정렬조건
hacker_id 오름차순 -정렬조건 2

Problem explanation

inner join으로 각 테이블 합치기
1개 이상 챌린지에서 full scores획득한 사람 구하기

1. submission 테이블 기준으로 데이터 뽑아오기 
2. submission 제출한 hacker 이름을 알기위해 hacker 테이블 조인
3. submission의 challengd 난이도를 알기위해 challenges 테이블 조인 
4. difficulty_level에 따른 만점 점수를 알기위해 difficulty테이블 조인 
5. 챌린지에서 full score획득한 사람 구하기
=> difficulty.level= challenges.level --조건 1
difficulty.score=submission.score  --조건2
challenges.challenge_id=submission.challenge_id--조건3
6. GROUP BY 로 id,name별로 묶어주기 => count할예정이므로
7. submission의 hacker_id가 1개 이상 데이터 조회
=>GROUP BY의 조건절로 HAVING사용
8.정렬조건 2개 나열 

solution

# INNER 사용하기
SELECT h.hacker_id, h.name
FROM submissions s
INNER JOIN challenges c
ON s.challenge_id = c.challenge_id
INNER JOIN difficulty d
ON c.difficulty_level = d.difficulty_level
INNER JOIN hackers h
ON s.hacker_id = h.hacker_id
WHERE s.score = d.score AND c.difficulty_level = d.difficulty_level
GROUP BY h.hacker_id, h.name
HAVING COUNT(s.hacker_id) > 1
ORDER BY COUNT(s.hacker_id) DESC, s.hacker_id ASC

extra solution

# WHERE 조건 사용하기
SELECT h.hacker_id,h.name 
FROM  hackers h,challenges c ,difficulty d,submissions s 
WHERE h.hacker_id=s.hacker_id
AND c.challenge_id=s.challenge_id
AND c.difficulty_level=d.difficulty_level
AND s.score=d.score
GROUP BY h.hacker_id,h.name 
HAVING  COUNT(h.hacker_id)>1
ORDER BY COUNT(c.challenge_id) DESC, h.hacker_id

Ollivander's Inventory

Problem

Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.

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.

Problem explanation

1. id, age, coins_needed, power of the wands 출력
2. age와 power가 같은 것이 여러개라면 그 중 coins_needed 값이 가장 작은 것을 출력
3. is_evil 값이 0이어야 함
4. power순으로 내림차순, power가 같다면 age순으로 내림차순 하기
5. code가 동일하면  age도 동일함 (1:1)

=> INNER JOIN을 통해 코드가 동일한 테이블만 추출
=> power,age가 같고,is_evil=0인 값 추출
=> 해당 값중 최소값 찾기 

solution

SELECT w.id, p.age, w.coins_needed, w.power
FROM WANDS w join Wands_Property p using(code)
WHERE w.coins_needed = (SELECT MIN(w2.coins_needed)
                        FROM WANDS w2 
                        INNER JOIN Wands_Property p2 ON w2.code = p2.code
                        WHERE w.power = w2.power
                        AND p.age = p2.age
                        AND p.is_evil = 0)
ORDER BY w.power DESC, p.age DESC

Challenges

Problem

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.

Problem explanation

1.hacker_id,name,total number(challenges) 추출
2.total number(challenges)로 내림차순 정렬 
3.같은 number of challenge일경우, hacker_id로 정렬 
4.같은 number of challenge이고, challenges 최대 숫자보다 적은 갯수이면 결과에서 제외 
=> total number(challenges)가 같고,max보다 작으면 결과에서 제외
=> total number(challenges)가 같고,max면 결과 포함
=> 결과적으로 중복값이 없거나,max면 됨 

Problem solution order

1. hacker_id,name 별 challegest_created 추출

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
ORDER BY challenges_created DESC,h.hacker_id

2.challenges_created의 조건 구하기

# MAX값 구하기 (FROM 의 서브쿼리 사용)
SELECT MAX(challenges_created)
FROM (SELECT hacker_id,COUNT(*) AS challenges_created
      FROM challenges 
      GROUP BY hacker_id) AS sub

# COUNT한 값을 다시 COUNT, HAVING으로 중복없는 데이터추출
SELECT challenges_created
FROM (SELECT hacker_id,COUNT(*) AS challenges_created
      FROM challenges 
      GROUP BY hacker_id) AS sub2
GROUP BY challenges_created 
HAVING COUNT(*)=1

solution

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 =(
                            SELECT MAX(challenges_created)
                            FROM (SELECT hacker_id,COUNT(*) AS challenges_created
                                  FROM challenges 
                                GROUP BY hacker_id ) AS sub1
                            )
                          
                            OR 
                          
     challenges_created IN ( 
                            SELECT challenges_created
                             FROM (SELECT hacker_id,COUNT(*) AS challenges_created
                                   FROM challenges
                                 GROUP BY hacker_id) sub2
                             GROUP BY challenges_created
                             HAVING COUNT(*)=1
                            )
ORDER BY challenges_created DESC,hacker_id

Contest Leaderboard

Problem

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 0 from your result.

Problem explanation

1.hacker_id,name,total scores(hackers) 추출
2.scores(hackers)로 내림차순 정렬 
3.같은 total scores(hackers)일경우, hacker_id로 정렬 
4.total scores(hackers)가 0인 직원 제외 
=> total scores(hackers)가 !=0 
5. hacker_id,challenge_id별 점수 조회에서 
challenge_id가 중복일경우는 max값 선택

Problem solution order

1.hacker_id,challenge_id 별 score의 max값 구하기

SELECT hacker_id,challenge_id,MAX(score) AS ms
FROM submissions
GROUP BY hacker_id,challenge_id

2. 1번을 FROM절 서브쿼리로 사용,hacker테이블과 join하여 id,name추출
3. 집계함수 조건절 HAVING에 total_score이 0이 아닌값 추출
4. 정렬조건 1,2 추가

solution

SELECT h.hacker_id,h.name,sum(sub.ms)  total_score
FROM (SELECT hacker_id,challenge_id,MAX(score) ms
      FROM submissions
      GROUP BY hacker_id,challenge_id ) AS sub
INNER JOIN hackers AS h ON h.hacker_id=sub.hacker_id
GROUP BY h.hacker_id,h.name
HAVING total_score !=0
ORDER BY total_score DESC, h.hacker_id