[QCC] qcc_7회차 해설

Arin lee·2025년 1월 24일

문제 1

테이블 설명 :

pages 테이블은 Facebook 페이지 정보를 담고 있습니다.
테이블 구조와 각 컬럼의 의미는 다음과 같습니다.

컬럼명	타입	설명
page_id	INT	페이지 ID (PK)
page_name	VARCHAR	페이지 이름

page_likes 테이블은 페이지 좋아요 정보입니다.
테이블 구조와 각 컬럼의 의미는 다음과 같습니다.

컬럼명	타입	설명
user_id	INT	사용자 ID
page_id	INT	페이지 ID (FK)
liked_date	DATETIME	좋아요 날짜

분석해야 할 내용은 다음과 같습니다 :

좋아요를 받지 않은 페이지의 page_id를 조회하는 SQL 문을 작성하세요.

결과는 page_id 컬럼만 포함하며, 오름차순으로 정렬되어야 합니다.

출력 값 예시

pages , page_likes 테이블이 다음과 같다면 :

pages

page_id	page_name
20001	SQL Solutions
20045	Brain Exercises
20701	Tips for Data Analysts

page_likes

user_id	page_id	liked_date
111	20001	2022/04/08
121	20045	2022/03/12
156	20001	2022/07/25

page_id가 20701인 페이지는 좋아요를 받은 기록이 없습니다.

다음과 같이 결과 출력이 되어야 합니다.
(해당 테이블은 예시이며, 실제 정답과 다를 수 있습니다.)

page_id
20701

◾ > 내 풀이 ```sql SELECT p.page_id from qcc.pages p left join page_likes pl on p.page_id = pl.page_id where pl.page_id is NULL order by 1; ``` > 튜터님 풀이 ```sql SELECT p.page_id FROM qcc.pages p LEFT JOIN qcc.page_likes pl ON p.page_id = pl.page_id WHERE pl.page_id IS NULL ORDER BY p.page_id ASC ```

문제 2

테이블 설명 :

stock_prices 테이블은 주식의 날짜별 성과 데이터를 포함하고 있습니다.
테이블 구조와 각 컬럼의 의미는 다음과 같습니다.

컬럼명	타입	설명
date	DATETIME	주식 데이터의 날짜
ticker	VARCHAR	주식을 식별하는 고유한 티커 심볼 (e.g., 애플 티커 심볼 : AAPL)
open	DECIMAL	거래일 시작 시의 주식 개시 가격
high	DECIMAL	거래일 중 최고가
low	DECIMAL	거래일 중 최저가
close	DECIMAL	거래일 종료 시의 종가

분석해야 할 내용은 다음과 같습니다 :

주어진 데이터를 기반으로 주식 티커 심볼 별로 다음 정보를 계산하세요:

각 종목의 최고 개시 가격 (highest open)과 해당 가격이 찍힌 해당 월
(highest_open, highest_mth)
각 종목의 최저 개시 가격 (lowest open)과 해당 가격이 찍힌 해당 월
(lowest_open, lowest_mth)
결과에서 월은 %Y%m 형식으로 표기해야 합니다.
개시 가격(open)이 기록된 경우만 결과에 포함합니다.
결과는 티커 심볼을 기준으로 오름차순 정렬해야 합니다.

출력 값 예시

stock_prices 테이블이 다음과 같다면 :

date	ticker	open	high	low	close
2023/01/31	AAPL	142.28	144.34	140	144.29
2023/02/28	AAPL	146.83	149.08	145	147.41
2023/03/31	AAPL	161.91	165	160	164.9
2023/04/30	AAPL	167.88	169.85	166	169.68
2023/05/31	AAPL	176.76	179.35	175	177.25

다음과 같이 결과 출력이 되어야 합니다.
(해당 테이블은 예시이며, 실제 정답과 다를 수 있습니다.)

ticker	highest_mth	highest_open	lowest_mth	lowest_open
AAPL	2023.05	176.76	2023.01	142.28

◾

내 풀이

SELECT a.ticker,
       a.highest_mth,
       a.highest_open,
       b.lowest_mth,
       b.lowest_open
from
(SELECT ticker,
       max(open) as highest_open,
       DATE_FORMAT(date, '%Y%m')as highest_mth
FROM qcc.stock_prices
group by 1
having 'open' = max('open'))a
join
(SELECT ticker,
       min(open) as lowest_open,
       DATE_FORMAT(date, '%Y%m')as lowest_mth
FROM qcc.stock_prices
group by 1,3
having 'open' = min('open'))b 
on a.ticker = b.ticker
order by a.ticker;

튜터님 풀이

WITH monthly_data AS (
    SELECT 
        ticker,
        DATE_FORMAT(date, '%Y%m') AS month_year,
        open
    FROM stock_prices
    WHERE open is not null 
), ranked_data AS (
    SELECT
        ticker,
        month_year,
        open,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open DESC) AS rank_high,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open ASC) AS rank_low
    FROM monthly_data
)
SELECT
    ticker,
    MAX(CASE WHEN rank_high = 1 THEN month_year END) AS highest_mth,
    MAX(CASE WHEN rank_high = 1 THEN open END) AS highest_open,
    MAX(CASE WHEN rank_low = 1 THEN month_year END) AS lowest_mth,
    MAX(CASE WHEN rank_low = 1 THEN open END) AS lowest_open
FROM ranked_data
GROUP BY ticker
ORDER BY ticker;

CTE (Common Table Expression) 정의

WITH monthly_data AS (
    SELECT 
        ticker,
        DATE_FORMAT(date, '%Y%m') AS month_year,
        open
    FROM stock_prices
    WHERE open IS NOT NULL 
)

monthly_data CTE: 이 부분은 stock_prices 테이블에서 ticker, date를 %Y%m 형식으로 변환한 month_year, 그리고 open 컬럼을 선택.
WHERE open IS NOT NULL: 개시 가격이 NULL이 아닌 데이터만 필터링하여, 유효한 개시 가격이 있는 데이터만 포함.

순위 매기기

, ranked_data AS (
    SELECT
        ticker,
        month_year,
        open,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open DESC) AS rank_high,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open ASC) AS rank_low
    FROM monthly_data
)

ranked_data CTE: monthly_data에서 각 티커(ticker)에 대해 개시 가격(open)을 기준으로 순위를 매김.
ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open DESC): 각 티커별로 개시 가격이 높은 순서로 순위를 매김. 가장 높은 개시 가격은 rank_high = 1이 된다.
ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open ASC): 각 티커별로 개시 가격이 낮은 순서로 순위를 매김. 가장 낮은 개시 가격은 rank_low = 1이 된다.

최종 결과 선택

SELECT
    ticker,
    MAX(CASE WHEN rank_high = 1 THEN month_year END) AS highest_mth,
    MAX(CASE WHEN rank_high = 1 THEN open END) AS highest_open,
    MAX(CASE WHEN rank_low = 1 THEN month_year END) AS lowest_mth,
    MAX(CASE WHEN rank_low = 1 THEN open END) AS lowest_open
FROM ranked_data
GROUP BY ticker
ORDER BY ticker;

최종 SELECT 문: ranked_data에서 각 티커에 대해 최고 및 최저 개시 가격과 해당 월을 선택.
MAX(CASE WHEN rank_high = 1 THEN month_year END): rank_high가 1인 경우(즉, 가장 높은 개시 가격에 해당하는 경우)의 month_year를 선택하여 highest_mth로 설정.
MAX(CASE WHEN rank_high = 1 THEN open END): rank_high가 1인 경우의 open 값을 선택하여 highest_open으로 설정.
MAX(CASE WHEN rank_low = 1 THEN month_year END): rank_low가 1인 경우의 month_year를 선택하여 lowest_mth로 설정.
MAX(CASE WHEN rank_low = 1 THEN open END): rank_low가 1인 경우의 open 값을 선택하여 lowest_open으로 설정.
GROUP BY ticker: 각 티커별로 그룹화하여 결과를 집계.
ORDER BY ticker: 결과를 티커 심볼(ticker) 기준으로 오름차순 정렬.

문제 3

테이블 설명

server_utilization 테이블은 AWS 서버 그룹의 실행 정보를 담고 있습니다.
테이블 구조와 각 컬럼의 의미는 다음과 같습니다.

컬럼명	타입	설명
server_id	INT	서버 ID
status_time	TIMESTAMP	상태 변경 시각
session_status	STRING	상태 (start 또는 stop)

분석해야 할 내용은 다음과 같습니다 :

모든 서버 ID의 총 실행 시간을 일 단위로 계산하는 SQL 문을 작성하세요.

[추가 조건]

각 start 세션에 해당하는 stop 세션만 계산에 포함됩니다.
server_id에서 start 또는 stop이 단독으로 존재하는 경우는 계산에서 제외합니다.
결과는 소수점을 버림하여 정수 일 단위로 출력해야 합니다.
- 예시: 5.5일 → 5일, 5.1일 → 5일, 5.99999일 → 5일 (소수점 이하 버림)

출력 값 예시

server_utilization 테이블이 다음과 같다면:

server_id	status_time	session_status
1	2022/08/01 08:00:00	start
1	2022/08/02 10:00:00	stop
1	2022/08/03 22:00:00	start
1	2022/08/04 10:00:00	stop
2	2022/08/10 10:00:00	start
2	2022/08/12 14:00:00	stop
3	2022/08/12 14:00:00	stop

서버 1:
- 08/01/2022 08:00:00 ~ 08/02/2022 10:00:00: 1.08일
- 08/03/2022 22:00:00 ~ 08/04/2022 10:00:00: 0.5일
- 합계: 1.08일 + 0.5일 = 1.58일 → 1일 (소수점 이하 버림)
서버 2:
- 08/10/2022 10:00:00 ~ 08/12/2022 14:00:00: 2.17일 → 2일 (소수점 이하 버림)
서버 3:
- stop 만 있기 때문에 제외합니다.

총 실행 시간 = 서버 1(1일) + 서버 2(2일) = 3일

다음과 같이 결과 출력이 되어야 합니다.
(해당 테이블은 예시이며, 실제 정답과 다를 수 있습니다.)

*힌트: TIMESTAMPDIFF(SECOND, current_time, next_time)*

*TIMESTAMPDIFF 함수는 두 DATETIME 간의 차이를 지정된 단위로 계산합니다.
여기서는 초 단위 차이를 계산하기 위해 SECOND를 사용합니다.
참고) 1 DAY = 86,400초*

total_uptime_days
3

내 풀이

with session_pairs as (
SELECT server_id,
       status_time as start_time,
       LEAD(status_time) over(PARTITION by server_id order by status_time)as stop_time
from qcc.server_utilization
where session_status = 'start'
),
valid_sessions as(
SELECT server_id,
       timestampdiff(SECOND, start_time,stop_time)/86400 as uptime_days
from session_pairs
where stop_time is not null
)
SELECT FLOOR(SUM(uptime_days)) as total_uptime_days
from valid_sessions;

튜터님 풀이

WITH running_time AS (
  SELECT
    server_id,
    session_status, 
    status_time,
    LEAD(session_status) OVER (PARTITION BY server_id ORDER BY status_time) AS next_status, 
    LEAD(status_time) OVER (PARTITION BY server_id ORDER BY status_time) AS next_time
  FROM qcc.server_utilization
)
select FLOOR(SUM(TIMESTAMPDIFF(SECOND, status_time, next_time) / 86400)) AS total_uptime_days
from running_time 
where session_status = 'start'
and next_status = 'stop'

with running_time as (
	select *
		, lead(status_time) over (partition by server_id order by status_time) next_status_time
		, lead(session_status) over (partition by server_id order by status_time) next_session_status
	from server_utilization
), server_uptime_second as (
	select server_id
		, floor(sum(TIMESTAMPDIFF(SECOND, status_time, next_status_time) / 86400)) uptime_days 
	from running_time 
	where session_status = 'start'
	and next_session_status = 'stop'
	group by 1
)
select sum(uptime_days)
from server_uptime_second

차이
1. 세션 쌍 생성 방식:

내 쿼리에서는 session_pairs CTE에서 start 세션만을 선택하고, 그 다음 LEAD 함수를 사용하여 다음 status_time을 가져옴. 이 경우, stop 세션이 없는 경우에는 stop_time이 NULL이 되어, 이후의 계산에서 제외.
튜터님의 쿼리에서는 running_time CTE에서 모든 세션을 가져오고, LEAD 함수를 사용하여 다음 세션의 상태와 시간을 가져옴. 이후 WHERE 절에서 session_status가 start이고 next_status가 stop인 경우만 필터링. 이 방식은 start와 stop 세션이 쌍으로 존재하는 경우를 더 명확하게 처리.

2.업타임 계산:
내 쿼리에서는 valid_sessions CTE에서 stop_time이 NULL이 아닌 경우만 선택하여 업타임을 계산. 그러나 이 과정에서 start 세션이 단독으로 존재하는 경우를 제외하는 데 있어 더 복잡한 조건이 필요할 수 있다.
튜터님의 쿼리는 running_time CTE에서 start와 stop 세션을 명확히 쌍으로 묶어 계산하므로, 더 간단하고 직관적.
1. 최종 결과 집계:
내 쿼리에서는 valid_sessions에서 SUM을 계산한 후 FLOOR 함수를 적용.
튜터님의 쿼리에서는 running_time에서 직접 SUM을 계산하고 FLOOR 함수를 적용하여 최종 결과를 도출.

Arin lee

Be DBA

이전 포스트

[챌린지] 프로덕트 데이터사이언스 - 6회차

다음 포스트

[QCC] qcc_7회차 해설

문제 1

출력 값 예시

문제 2

출력 값 예시

문제 3

출력 값 예시

[챌린지] 프로덕트 데이터사이언스 - 6회차

[TIL]2025-01-31(금)

0개의 댓글