[코드카타 연습하기]SQL_(170-173)

코드카타 170-173

170. A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to decimal places.
     오라클 버전

SELECT ROUND(MEDIAN(LAT_N),4)
FROM STATION;

SELECT ROUND(A.LAT_N,4)
FROM (
      SELECT LAT_N, PERCENT_RANK() OVER(ORDER BY LAT_N)PR
      FROM STATION
      )A
WHERE PR = 0.5;

중앙값구하기
내가 푼풀이와 달리 푸는법에 대한 추가적인 설명.

171. 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 IF(G.GRADE <8 , NULL, S.NAME), G.GRADE, S.MARKS
FROM STUDENTS S JOIN GRADES G
ON S.MARKS BETWEEN MIN_MARK AND MAX_MARK
ORDER BY 2 DESC, 1,3;

172. 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 , 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(H.NAME) >= 2
ORDER BY COUNT(H.NAME)DESC, 1;

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

문제 해석
1) id, age, coins_needed, power of the wands 출력하기
2) age와 power가 같은 것이 여러개라면 그 중 coins_needed 값이 가장 작은 것을 출력하기
3) is_evil 값이 0이어야 함
3) power순으로 내림차순, power가 같다면 age순으로 내림차순 하기

SELECT  id, age, coins_needed, power   
FROM Wands W JOIN Wands_Property WP
ON W.code = WP.code
WHERE is_evil = 0
AND coins_needed =(SELECT MIN(coins_needed)
                   FROM Wands W1 JOIN Wands_Property WP1
                   ON W1.code = WP1.code
                   WHERE is_evil = 0
                   AND WP1.age = WP.age
                   AND W1.power = W.power)
ORDER BY 4 DESC, 2 DESC;

다른 풀이.

SELECT w.id
     , wp.age
     , a.min_coins
     , a.power   -- (3)
  FROM (SELECT code, power, MIN(coins_needed) AS min_coins
          FROM wands
         GROUP BY code, power) AS a
  LEFT JOIN wands AS w
         ON a.min_coins = w.coins_needed AND a.code = w.code
  LEFT JOIN wands_property AS wp
         ON a.code = wp.code   -- (1)
 WHERE wp.is_evil = 0
 ORDER BY a.power DESC, wp.age DESC;   -- (2)

인사이트

문제를 해석하는 과정에서 시간이 많이 소요되었다.
172번의 경우 여러개의 테이블을 조인하는 과정이 복잡해보이지만, S.SCORE = D.SCORE이 조건을 생각해내면 생각보다는 쉽게 풀리는 문제였다.

그리고 173번의 경우는 문제를 해석하는게 쉽지 않았고, 더 나아가 쿼리문도 다소 복잡했다.