[패스트캠퍼스] SQL 강의 학습후기 5주차

길고도 짧았던 SQL 강의 마지막 학습후기 5주차입니다.  

 

5주라는 시간동안 SQL이 어떤 개념과 로직을 가지고 풀어나가는 지 이해할 수 있는 좋은 시간이었습니다.

다만 실무에서 제가 원하는 데이터로 가공 및 추출하기 위해서는 계속된 연습만이 살 길 같습니다 ㅎㅎ

 

강사님께서 'HakerRank'  사이트를 가르쳐주셨는데 강의 완강 이후에도 틈틈이 이용해서 실력을 올릴 수 있도록 연습해봐야 할 것 같아요. 

Dashboard | HackerRank

---

# [HackerRank] The Blunder

ERROR CODE : Your submission contains non ASCII characters, we dont accept submissions with non ASCII characters for this challenge.
* 주석에 한글 포함 시, 에러 발생함.. Run code 시, 한글 주석 제외 필요

# 사용 쿼리문 일부 발췌
REPLACE(salary, '0','') -- salary 컬럼에 0이 있다면 공백으로 치환(3000 >> 3)

* 접근방식 : '0' 누락으로 인한 평균값 계산 오류 발생, REPLACE 함수 기반 실제값 - 오류값 차이 계산

---

# [HackerRank] Top Earners

* 접근방식 : 연봉(salary*months) 및 최대 연봉을 받는 직원 수 계산

# 사용 쿼리문(WHERE절 서브쿼리)
WHERE salary*months = (select max(salary*months)
			from employee;
-- 최대연봉을 받는 직원과 동일한 직원 정보 조회

---

# [HackerRank] Weather observation station 19

* 접근방식 : WITH문 기반 임시테이블 생성 후, P1(a,b), P2(b,d) 간의 유클리드 거리 계산

> 유클리드 거리 계산을 위해, SQRT(제곱근), POWER(제곱) 사용

with station_result as(
select a,b,c,d
from (select min(lat_n) as a, 
             max(lat_n) as b,
             min(long_w) as c,
             max(long_w) as d
     from STATION)sub
)
-- select * from station_result;

select round(sqrt(power(a-b,2)+power(c-d,2)),4) as dist
from station_result;

-- 예제에서는 임시테이블 생성 없이, min/max 함수 기반 유클리드 거리 계산해도 정답 처리됨

---

# [HackerRank] Weather observation station 20

* 접근방식 : 중앙값(median) 계산하기

> 오라클은 중앙값 내장함수를 지원하나, SQL의 경우 미지원 기능이었음

> SQL로 중앙값을 계산한 여러 블로그를 서칭한 결과, 아래 쿼리문이 가장 짧고 간단하게 출력되는 결과(*레퍼런스 참고)

 

▶ PERCENT_RANK

- 기본 형태는 RANK, DENSE_RANK, ROW_NUMBER 과 유사함

-- 0~1까지의 값을 반환함 중앙값 계산을 위해 WHERE 조건절에 '0.5'가 입력되었음

select round(lat_n,4)
from (select lat_n, percent_rank() over (order by lat_n) as per
    from STATION) a
where per = 0.5;

(+) 그 외에도 ROW_NUMBER로 값들의 순위를 매긴 뒤 중앙값 구하는 방법도 있었음

---

# [HackerRank] The PADS

* 접근방식 : CONCAT 기반 NAME, OCCUPATION 글자 합침

> 1st 쿼리(UPPER 기반 대문자 변환), 2nd 쿼리(Lower 기반 소문자 변환)

> 첫글자를 추출했기 때문에 LEFT 함수를 사용했으나 다른 케이스의 경우, SUBSTR 함수 사용하여 일부만 추출 가능 

select concat(NAME,'(', upper(left(occupation,1)),')')
              FROM OCCUPATIONS
              ORDER BY NAME;
              
select concat('There are a total of ', count(occupation),' ', lower(occupation),'s.')
              from OCCUPATIONS
              group by occupation
              order by count(occupation), occupation;

---

# [HackerRank] Occuptations

* 접근방식 : CASE WHEN문 기반 조건 쿼리문 작성, 피벗테이블 작성을 위해 Partition by 기반 그룹화 진행

> min 집계함수의 경우, group by절을 실행시키기 위해 작성되었음

SELECT min(case when Occupation = 'Doctor' then Name end) as Doctor,
       min(case when Occupation = 'Professor' then Name end) as Professor,
       min(case when Occupation = 'Singer' then Name end) as Singer,
       min(case when Occupation = 'Actor' then Name end) as Actor
FROM (Select Name, Occupation,
            Rank() over (partition by Occupation order by Name) as name_order
      FROM OCCUPATIONS) a
      group by name_order;

---

# [HackerRank] Binary Tree Nodes

* 접근방식 : 계층형 질의로 쿼리문 작성을 위해 아래와 같이 추가 정리한 뒤 쿼리 작성함

o 문제 정리

select case when P is null then concat(N,' Root')
            when N in(select DISTINCT P from BST) then concat(N,' Inner') 
            else concat(N,' Leaf')
            end as LEAF
from BST
order by N;

---

# [HackerRank] The Report

* 접근방식 : Students & Grade 두 테이블 간 join 수행, 8등급 미만일 경우 NULL값 출력(Case when 조건 사용)

두 테이블 간 매칭되는 컬럼이 없기 때문에, on/and 문을 사용하여 조인 수행

select case when B.Grade < 8 then NULL
            else A.Name
            end as name
            , B.grade, A.marks
from Students A
inner join Grades B
on A.Marks >= B.Min_mark
and A.Marks <= B.Max_mark
order by 2 desc, 1 asc, 3 asc;

---

# [HackerRank] Contest Leaderboard

* 접근방식 : 1) Submissions 테이블의 challenge_id별 max(score) 출력(B.score) 

                    2) Hackers 테이블 sum(B.score) 값 간의 join

                    3) sum(B.score)가 0인 값은 출력에서 제외 ( != 이용)

Select A.hacker_id,
       A.name,
       sum(B.score) as total
from Hackers A
inner join (select hacker_id, 
                   challenge_id, 
                   max(score) as score
            from Submissions
            group by 1,2) B -- score의 max값 출력을 위해 서브쿼리 사용
on A.hacker_id = B.hacker_id
group by 1,2
having sum(B.score) != 0
order by total desc, hacker_id asc

---

# [HackerRank] Placements

* 접근방식 : 1) salary보다 큰 friend_salary 구하기 (2~3단계 수행 후 진행)

                    2) students기준 Salary 구하기

                    3) friend_salary 구하기 

select A.name
from students A
inner join packages B
on A.ID = B.ID -- 2) students의 salary 구하는 쿼리문
inner join (select C.ID,
                   C.Friend_id,
                   D.Salary as friend_salary
            from friends C 
            inner join packages D
            on C.Friend_ID = D.ID) E -- 3) friend_salary 구하는 쿼리문
on A.ID = E.ID
and B.salary < E.friend_salary -- 1) 쿼리문
order by E.friend_salary

---

# References

전환율(conversion rate)이란? 전환율 계산 및 높이는 방법

리텐션 측정 기법

[Oracle VS Mysql] 중앙값(median) 구하기

[MySQL] PERCENT_RANK() 함수

---

---

국비지원교육으로 시작한 SQL 강의는 생각보다 많은 도움이 되었습니다.

SQLD도 병행해서 준비했는데, 개념 및 실습 기반으로 들은 만큼 공부할 때 크로스체크 하는 기분이었습니다.

 

제가 들었던 강의는 입문용이었기에 조금 더 심화과정으로 넘어가기 위해선 쿼리문을 짜기 위한 로직이 항상 머릿 속에 맴돌아야 더 잘 쓸 수 있을 것 같아요.

 

강의에서 충분히 이해되지 않았던 함수들도 SQLD를 공부하면서 더 알게 되기도 했고,

본 강의는 MySQL 기반으로 이뤄지다보니 Oracle에서 쓰이는 쿼리 문법도 알 수 있어서 좋았어요.

 

하지만 SQL 문제 난이도에 따라 시간이 제법 걸릴 때도 있어서 계속 연습만이 살 길인 것 같습니다..!  

남은 Hackerank 문제들도 다 풀어봐야겠습니다 ㅎㅎ