[TIL] 25.03.07 Postgre 코드 테스트 복기(진짜 시험 아님)

Prologue

오늘한 것: SQL 코드 테스트

SQL 코드 테스트

---

1. Laptop vs Mobile Viewership, New York Times

링크
목표: PC(Laptop)과 Mobile의 Viewership을 집계
Viewership: 본 횟수, 시청률

• ship: 집합, 상태 (Friendship: 우정, Citizenship: 시민권)

laptop과 mobile 각각의 view 수를 한 행에 2개의 열로 나타내면됨
-> Groupby 없이 CASE WHEN으로 값 바꾼 후, SUM으로 집계하자.

SELECT
  SUM(CASE WHEN device_type = 'laptop' THEN 1 ELSE 0 END) AS laptop_views
  ,SUM(CASE WHEN device_type IN ('tablet', 'phone') THEN 1 ELSE 0 END) AS mobile_views
FROM viewership

2. User's Third Transaction, Uber

링크
목표: 유저별 3번째 탑승(Transaction) 기록 구하기
-> 3번째 이용한 고객의 수, 특징 파악할 수 있을 듯

유저별로 3번째 이용 -> RANK() OVER () 윈도우 함수로 유저별 이용 순서 구하기
유저별 이용 순서 중 3인 것만 필터링하기 위해 구한 데이터를 FROM 절의 서브쿼리(인라인 뷰)로 만들기
원하는 열을 골라주기(전체였음)

-- 유저별 3번째 기록
SELECT
  user_id, spend, transaction_date
FROM
(SELECT *, RANK() OVER (PARTITION BY user_id ORDER BY transaction_date) as rank
FROM transactions) a
WHERE rank = 3

3. Second Highest Salary, FAANG

링크
FAANG: 미국 거대 IT 기업(2022년까지)- 페북, 애플, 아마존, 넷플, 구글
목표: 두번째 가장 높은 연봉 구하기 -> HR 팀의 내 매니저는 연봉의 분포에 관심 있어서 2번째 높은 연봉을 알고 싶어함

가장 높은 연봉만 1행 1열로 표현
-> 연봉의 순위를 DENSE_RANK() OVER () 윈도우 함수로 표현: 공동 1등이 나올 수 있음
-> WITH 구문으로 인라인 뷰로 활용, 2순위인 연봉 데이터를 표시, DISTINCT를 사용: 2순위인 연봉이 여러 개 있을 수 있음

-- 모든 직원 중 2번째 높은 연봉 중복되도 1개만 출력
WITH salary_rank AS(
SELECT
  *
  ,DENSE_RANK() OVER (ORDER BY salary DESC) as rank
FROM employee
)
SELECT DISTINCT salary
FROM salary_rank
WHERE rank = 2

4. Sending vs Opening Snaps, Snapchat

링크
Snapchat: 미국 메신저 서비스, 수신인이 내용 확인하면 메시지가 사라짐
목표: Open(메시지를 열기), Send(메시지를 보내기)한 시간의 비율을 연령별로 구하기

연령별로 그룹-> acitivities테이블에 연령 테이블(age_breakdown)을 Inner join: 연령 정보가 있어야 집계 가능
관심 있는 Open, Send acitivity만 WHERE IN('open', 'send')로 필터: 처리속도 증가, 비율을 구할 때 전체가 open과 send임
Send time 구하기: age_bucket으로 그룹바이 후 CASE WHEN으로 send일 때 time, 아니면 0이 나오게 처리. 그리고 SUM으로 합계 구하기
Open time도 같은 방법으로 해주기
구한 시간 각각에 SUM(time)으로 총 시간을 나눠주고, 100.0을 곱하기: Python3에서는 상관없지만 C나 Java에서는 정수끼리 나누면 정수로 나오는 문제가 있음- 3/2 => 1 ;; -> 실수(1.0)를 곱해야 실수로 결과가 출력됨
ROUND로 소수점 2째자리 까지 표현하기

SELECT
  age_bucket
  ,ROUND(SUM(CASE WHEN activity_type = 'send' THEN time_spent ELSE 0 END) / SUM(time_spent) * 100.0, 2) AS send_perc
  ,ROUND(SUM(CASE WHEN activity_type = 'open' THEN time_spent ELSE 0 END) / SUM(time_spent) * 100.0, 2) AS open_perc
FROM activities a
INNER JOIN age_breakdown b
ON a.user_id = b.user_id
WHERE activity_type IN ('open', 'send')
GROUP BY age_bucket