SQL Challenge 세션 #5 피벗테이블

240604 Today I Learn

Pivot Table

💡 피벗 테이블(pivot table)
표의 행과 열을 전환하는 등의 과정을 통하여 통계를 재정렬하고, 그 결과로 표 데이터를 요약하는 방법. 이에는 집계함수(합계, 평균 등)가 사용될 수 있다.

SQL로 Unpivot 하기

quarterly_sales

1. 일련 변호를 가진 테이블 만들기

SELECT 1 AS idx 
UNION ALL SELECT 2 AS idx
UNION ALL SELECT 3 AS idx 
UNION ALL SELECT 4 AS idx

2. 피벗 테이블을 만들고 이를 CROSS JOIN 하기

SELECT *
FROM quarterly_sales
	 CROSS JOIN (SELECT 1 AS idx 
				 UNION ALL SELECT 2 AS idx
				 UNION ALL SELECT 3 AS idx 
				 UNION ALL SELECT 4 AS idx)idxs

3. 피벗 테이블을 결합하기

SELECT q.year AS year,
	   CASE WHEN idxs.idx =1 THEN 'q1'
       		WHEN idxs.idx =2 THEN 'q2'
            WHEN idxs.idx =3 THEN 'q3'
            ELSE 'q4' END quarter,
       CASE WHEN idxs.idx =1 THEN q.q1
       		WHEN idxs.idx =2 THEN q.q2
            WHEN idxs.idx =3 THEN q.q3
            ELSE q.q4 END sales
FROM quarterly_sales q
	CROSS JOIN (SELECT 1 AS idx 
				UNION ALL SELECT 2 AS idx
				UNION ALL SELECT 3 AS idx 
				UNION ALL SELECT 4 AS idx) idxs

과제

Facebook SQL Interview Question | DataLemur

Assume you're given two tables containing data about Facebook Pages and their respective likes (as in "Like a Facebook Page").
Write a query to return the IDs of the Facebook pages that have zero likes. The output should be sorted in ascending order based on the page IDs.

SELECT DISTINCT p.page_id
FROM pages p
     LEFT JOIN page_likes l ON p.page_id = l.page_id
WHERE l.liked_date is NULL;

Tesla SQL Interview Question | DataLemur

SELECT part, assembly_step
FROM parts_assembly
WHERE finish_date is NULL;

UnitedHealth Group SQL Interview Question | DataLemur

SELECT ROUND(COUNT(CASE WHEN call_category = 'n/a' or call_category is NULL THEN 1 ELSE NULL END)*100.0
            /COUNT(*), 1) uncategorised_call_pct
FROM callers;