[11주차]해커랭크: 36~44

36. Aggregation > Weather Observation Station 19

Consider  P1(a,c) and P2(b,d)  to be two points on a 2D plane where (a,b)  are the respective minimum and maximum values of Northern Latitude (LAT_N) and  (c,d)are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION.

Query the Euclidean Distance between points  and  and format your answer to display 4 decimal digits.

SELECT ROUND(SQRT(POW(DB.b - DB.a,2) + POW(DB.d - DB.c,2)),4)
FROM (
SELECT MIN(LAT_N) AS a,
       MAX(LAT_N) AS b,
       MIN(LONG_W) AS c,
       MAX(LONG_W) AS d
FROM STATION
) DB

• POW(,2): 2제곱
• SQRT(): 제곱근

 

 

37. Basic Join >  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.

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

 

 

 

38. Basic Join >  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.

SELECT CITY.NAME
FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = "Africa"

 

 

 

39. Basic Join >  Avereage Population of Eacg 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 COUNTRY.CONTINENT,
       FLOOR(AVG(CITY.POPULATION))
FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY 1

• FLOOR(): 정수에서 버림

 

 

40. Basic  Select > Weather Observation Station 5

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

(SELECT CITY, 
       LENGTH(CITY)
FROM STATION
ORDER BY 2, 1
LIMIT 1)
UNION
(SELECT CITY, 
       LENGTH(CITY)
FROM STATION
ORDER BY 2 DESC, 1
LIMIT 1)

MIN, MAX값을 함께 구할 때는 UNION/UNION ALL을 사용해준다.

 

 

 

41. Advanced Select > New Companies

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

SELECT C.company_code,
       C.founder,
       COUNT(DISTINCT LM.lead_manager_code),
       COUNT(DISTINCT SM.senior_manager_code),
       COUNT(DISTINCT M.manager_code),
       COUNT(DISTINCT E.employee_code)
FROM Company C
INNER JOIN Lead_Manager LM on C.company_code = LM.company_code
INNER JOIN Senior_Manager SM on LM.lead_manager_code = SM.lead_manager_code
INNER JOIN Manager M on SM.senior_manager_code = M.senior_manager_code
INNER JOIN Employee E on M.manager_code = E.manager_code
GROUP BY 1, 2

계급이 서로 연결되어 있으므로, 바로 위/아래 계급과 조인을 해줘야 한다.

 

 

 

42. Aggregation > Weather Observation Station 20: 중앙값(percent_rank() over(oreder by~)

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 4 decimal places.

 

중앙값을 구하는 문제이다.

오라클의 경우 median함수를 바로 사용할 수 있지만 mysql에서는 불가능하다.

따라서 중앙값을 구하기 위해 Window Function을 사용한다.

SELECT LAT_N,
       PERCENT_RANK() OVER(ORDER BY LAT_N) AS percent
FROM STATION

여기서 percent가 0.5인 LAT_N을 찾으면 된다.

따라서 최종 정답을 구하는 쿼리는 아래와 같다.

SELECT ROUND(DB.LAT_N,4)
FROM (
SELECT LAT_N, 
       PERCENT_RANK() OVER (ORDER BY LAT_N) percent
FROM STATION
)DB
WHERE DB.percent = 0.5

 

 

 

43. Basic Join > The Report

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.

SELECT case when A.Grade > 7 then A.Name else NULL end as name,
       A.Grade,
       A.Marks
FROM (
SELECT Name,
       Marks,
       case when Marks between 0 and 9 then 1
            when Marks between 10 and 19 then 2
            when Marks between 20 and 29 then 3
            when Marks between 30 and 39 then 4
            when Marks between 40 and 49 then 5
            when Marks between 50 and 59 then 6
            when Marks between 60 and 69 then 7
            when Marks between 70 and 79 then 8
            when Marks between 80 and 89 then 9
        else 10
        end as Grade
From Students 
) A
order by A.Grade desc, name asc, A.marks asc

select case when G.Grade > 7 then S.Name else Null 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, Name asc, S.Marks asc

조인할 컬럼이 똑같이 않다면 조건을 사용해서 조인해주면 된다. 주로 between이 많이 이용되는 것 같다!

 

 

 

44. Basic Join > 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 s.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
group by s.hacker_id, h.name
having count(s.hacker_id) > 1
order by count(s.hacker_id) desc, s.hacker_id asc

총 4개의 테이블이 존재했었고, Submissions와 Challenges테이블에 똑같이 hacker_id가 존재했지만, 테이블 설명을 읽어보면 두 개의 컬럼은 컬럼명이 똑같고 의미는 다른 컬럼이다. 따라서 두 테이블을 조인할 때는 challenge_id로만 조인해줘야 한다!!