해커랭크 SQL 공부 정리 2

Solve SQL

Basic Join 내용 정리

Basic Join

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.

SELECT CT.CONTINENT, FLOOR(AVG(C.POPULATION)) -- TRUNCATE(AVG(C.POPULATION), 0)
FROM COUNTRY AS CT
JOIN CITY AS C
    on C.COUNTRYCODE = CT.CODE
GROUP BY CT.CONTINENT

• 반올림: ROUND, 올림: CEILING, 내림: FLOOR

The Report

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.

SELECT (CASE WHEN GRADE < 8 THEN NULL 
             ELSE NAME END) AS NAME -- IF (GRADE < 8, NULL, NAME) AS NAME 
    , GRADE 
    , MARKS
FROM STUDENTS
JOIN GRADES G
    ON MARKS BETWEEN MIN_MARK AND MAX_MARK -- ON G.GRADE = CASE WHEN MARKS BETWEEN MIN_MARK AND MAX_MARK THEN GRADE END
ORDER BY GRADE DESC, NAME, MARKS 

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

SELECT H.HACKER_ID, NAME, SUM(SCORE)
FROM (SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) SCORE FROM SUBMISSIONS GROUP BY 1, 2) S
LEFT JOIN HACKERS H
    on H.HACKER_ID = S.HACKER_ID
WHERE SCORE != 0
GROUP BY 1, 2
ORDER BY 3 DESC, 1

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.

SELECT H.HACKER_ID, NAME
FROM SUBMISSIONS S 
JOIN CHALLENGES C
    on C.CHALLENGE_ID = S.CHALLENGE_ID
JOIN DIFFICULTY D
    on C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
JOIN HACKERS H 
	on H.HACKER_ID = S.HACKER_ID 
WHERE D.SCORE = S.SCORE
GROUP BY 1, 2
HAVING COUNT(S.CHALLENGE_ID) > 1
ORDER BY COUNT(S.CHALLENGE_ID) DESC, 1

• CHALLENGE_ID 기준으로 제출 TBL과 챌린지 TBL 조인
• 이 테이블에 DIFFICULTY_LEVEL 기준으로 DIFFICULTY TBL 조인
• 이 테이블에 HACKER_ID 기준으로 HACKERS TBL과 조인
• HAVING COUNT(CHALLENGE_ID) > 1: 한 챌린지 이상

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.

SELECT H.HACKER_ID, H.NAME, COUNT(*) as CNT 
FROM HACKERS as H
JOIN CHALLENGES C 
    on C.HACKER_ID = H.HACKER_ID
GROUP BY 1, 2
HAVING
    CNT = (SELECT COUNT(*) 
           FROM CHALLENGES
           GROUP BY HACKER_ID 
           ORDER BY COUNT(*) DESC 
           LIMIT 1) OR
    CNT IN (SELECT CNT 
            FROM (SELECT HACKER_ID, COUNT(*) AS cnt 
                  FROM CHALLENGES
                  GROUP BY 1) SUB
            GROUP BY CNT
            HAVING COUNT(*)=1)
ORDER BY 3 DESC, 1

Ollivander's Inventory

SELECT W.ID, WP.AGE, W.COINS_NEEDED, W.POWER 
FROM WANDS W
JOIN WANDS_PROPERTY WP
    ON WP.CODE = W.CODE
WHERE WP.IS_EVIL=0 
    AND W.COINS_NEEDED in (SELECT MIN(W1.COINS_NEEDED) 
                           FROM WANDS as W1
                           JOIN WANDS_PROPERTY as WP1
                                ON WP1.CODE = W1.CODE
                           WHERE W1.POWER = W.POWER 
                            AND WP1.AGE = WP.AGE)
ORDER BY 4 DESC, 2 DESC