[SQL 코드카타] 169 ~ 171번(HakerRank)

169번 New_companies

select
    co.company_code,
    co.founder, --
    count(distinct lm.lead_manager_code), -- 3) 각 개수 구하기
    count(distinct sm.senior_manager_code),
    count(distinct m.manager_code),
    count(distinct em.employee_code)
from
    company co -- 1) 각 key값을 기준으로 조인
    join lead_manager lm on co.company_code = lm.company_code
    join senior_manager sm on lm.lead_manager_code = sm.lead_manager_code
    join manager m on sm.senior_manager_code = m.senior_manager_code
    join employee em on m.manager_code = em.manager_code
group by
    1,2 -- 2) company_code, founder 그룹화하여
order by
    1 ;

> (뭔가 정규화과정을 거친 테이블들을 다시 합쳐서 계산하는 의도로 느껴짐)  문제 의도가 전부 조인하는 것 같아서 위처럼 했지만 코드를 짜면서 사실상 employee 테이블에 모두 속해있어서 아래 코드로도 재작성 

select
    co.company_code,
    co.founder,
    count(distinct em.lead_manager_code),
    count(distinct em.senior_manager_code),
    count(distinct em.manager_code),
    count(distinct em.employee_code)
from
    company co
    join employee em 
    on co.company_code = em.company_code
group by
    1,2
order by
    1 ;

 

170번 weather-observation-station-20

with rn_station as(
select
    *,
    ROW_NUMBER() OVER(ORDER BY LAT_N) AS rn
from
    station
), cnt_station as (
select
    count(*) cnt
from 
    station)
select
    round(avg(lat_n),4)
from
    rn_station, cnt_station
where
    rn in (floor((cnt+1)/2),ceil((cnt+1)/2))

 

> Oracle에서는 median 함수가 있어 구하기 쉬움.

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

>percent_rank() 활용

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

> SET 함수 활용법

set @rn = -1;
select
    round(avg(lat_n),4) as median_lat_n
from
    (select 
        @rn := @rn + 1 as rn,
        lat_n
    from 
        station
     order by
        lat_n) a
where 
    rn in (floor(@rn/2), ceil(@rn/2))

 

171번 the-report

with upd_students as (
SELECT
    NAME,
    CASE WHEN marks < 10 then 1
        WHEN marks < 20 then 2
        WHEN marks < 30 then 3
        WHEN marks < 40 then 4
        WHEN marks < 50 then 5
        WHEN marks < 60 then 6
        WHEN marks < 70 then 7
        WHEN marks < 80 then 8
        WHEN marks < 90 then 9
        ELSE 10 END AS 'grade',
    MARKs
FROM
    Students
)
select
    case when grade < 8 then null
        else name end as name,
    grade,
    marks
from
    upd_students
order by
    grade desc, name asc;