[중급 SQL 문제풀이] HackerRank 문제 및 풀이(1)

알고리즘 문제 및 풀이사이트

https://www.hackerrank.com/onboarding?redirect=%2Fdashboard 

Dashboard | HackerRank

 

sql 문제풀이 

1. Japan Population: https://www.hackerrank.com/challenges/japan-population/problem
2. Weather Observation Station 2: https://www.hackerrank.com/challenges/weather-observation-station-2/problem
3. Weather Observation Station 18: https://www.hackerrank.com/challenges/weather-observation-station-18/problem
4. New Companies: https://www.hackerrank.com/challenges/the-company/problem

 

 

 

 

 

 

 

- 답안

(mysql로 풀이)

 

1. Japan Population

  - SUM

/*
Query the sum of the populations for all Japanese cities in CITY. 
The COUNTRYCODE for Japan is JPN.
*/

SELECT SUM(POPULATION) FROM CITY 
WHERE COUNTRYCODE ='JPN'

2. Weather Observation Station 2

  - 두자리 숫자까지 반올림

/*
The sum of all values in LAT_N rounded to a scale of  decimal places.
The sum of all values in LONG_W rounded to a scale of  decimal places.
*/
SELECT ROUND(SUM(LAT_N),2) AS lat, 
    ROUND(SUM(LONG_W),2) AS lon
FROM STATION

3. Weather Observation Station 18

 - ABS() 절대값

Consider p1(a,b)  and p2(c,d) to be two points on a 2D plane.

위와 같으므로 해당 쿼리에서 Manhattan distance 구하는 공식 |a-c| + |b-d|

 

/*
a: happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
b: happens to equal the minimum value in Western Longitude (LONG_W in STATION).
c: happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
d: happens to equal the maximum value in Western Longitude (LONG_W in STATION).

Query the Manhattan Distance between points p1 and p2 and round it to a scale of 4 decimal places.
*/

SELECT ROUND(ABS(ST.a-ST.c) + ABS(ST.b-ST.d),4)
FROM
(
SELECT MIN(LAT_N) AS a, MIN(LONG_W) AS b, MAX(LAT_N) AS c ,MAX(LONG_W) AS d  FROM  STATION 
) ST

4. New Companies

 - company_code, founder name 별 각 manager와 employee 수 구하기 , company_code순으로 정렬

Company 테이블에 company_code, founder name 존재

Employee Table (모든 컬럼값을 가지고있음)

  -> 주의사항 :  Employee에 없는경우도 생각해야함

                     현재상황은 Company 테이블과  Employee 테이블을 INNER JOIN 해도 결과가 나옴

output 결과

/*

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.

Note:
The tables may contain duplicate records.
The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.
*/

SELECT  C.company_code ,
        C.founder ,
        COUNT(DISTINCT L.lead_manager_code ), 
        COUNT(DISTINCT S.senior_manager_code ), 
        COUNT(DISTINCT M.manager_code ),
        COUNT(DISTINCT E.employee_code )
FROM Company C
LEFT JOIN Lead_Manager L ON C.company_code = L.company_code
LEFT JOIN Senior_Manager S ON L.company_code = S.company_code
LEFT JOIN Manager M ON S.company_code = M.company_code
LEFT JOIN Employee E ON M.company_code = E.company_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code