[해커랭크] SQL 심화 응용 팁들

 

 

1. SELECT 서비스쿼리에서 where 조건으로 필터링

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true

 

select a.company_code, a.founder,
(select count(distinct lead_manager_code) from lead_manager where company_code = a.company_code),
(select count(distinct senior_manager_code) from senior_manager where company_code = a.company_code),
(select count(distinct manager_code) from manager where company_code = a.company_code),
(select count(distinct employee_code) from employee where company_code = a.company_code)
from company a
order by a.company_code asc;

 

 

2. percent_rank() over (order by ooo) 문으로 중앙값 구하기

https://www.hackerrank.com/challenges/weather-observation-station-20/problem?isFullScreen=true

SELECT ROUND(AVG(LAT_N),4)
FROM (SELECT LAT_N
​​​​​​​​​​​, PERCENT_RANK() OVER (ORDER BY LAT_N) p_rn
​​​​​​FROM STATION) t
WHERE p_rn = 0.5;

 

 

3. on 절에서 원하는 조건 활용해서 join 하기

https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true

SELECT CASE WHEN g.grade < 8 THEN NULL ELSE s.name 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, s.name, s.marks