SQL

QCC | 7회차 코드 리뷰 & 피드백 (+마지막 qcc)

성장하는 쿠키의 로그 기록 2025. 3. 21. 14:45

☑️  좋아요를 받지않은 페이지 조회

[문제]

[문제 풀이]

내 코드 

SELECT DISTINCT p.page_id AS page_id
FROM pages p 
	LEFT JOIN page_likes pl ON p.page_id =pl.page_id
WHERE pl.page_id IS NULL
ORDER BY 1;

 

이슈 및 해결 과정

  • left join 후 null 값 집계 하는 문제
  • page_id 만 출력하는 부분에서, group by 쓸지 distinct 쓸지 고민하다가 한줄 추가하기 싫어서 distinct로 해결
  • 실무에서 사용한다면 group by 가 더 적합할듯

한줄 포인트

  • pass 

☑️  티커별 최고가/최저가와 해당날짜 조회하기

[문제]

[문제 풀이]

내 코드 

-- 1. high /low 의 open 과 일짜 각각cte로 만들어서 join 해주기 -> high/low값 각각집계해서 최적화 필요
WITH high_t AS(
	SELECT date,ticker,open
		  ,DENSE_RANK () OVER (PARTITION BY ticker ORDER BY open DESC) AS high
	FROM stock_prices
	WHERE OPEN IS NOT NULL
), low_t AS(
	SELECT date,ticker,open
		  ,DENSE_RANK () OVER (PARTITION BY ticker ORDER BY open asc) AS low
	FROM stock_prices
	WHERE OPEN IS NOT NULL
), high_t2 AS(
SELECT ticker
	 ,DATE_FORMAT(date,'%Y%m') AS highest_mth
	 ,OPEN AS highest_open
FROM high_t
WHERE high=1
), low_t2 AS(
SELECT ticker
	 ,DATE_FORMAT(date,'%Y%m') AS lowest_mth
	 ,OPEN AS lowest_open
FROM low_t
WHERE low=1
)
SELECT h.ticker,highest_mth,highest_open,lowest_mth,lowest_open
FROM high_t2 h
	INNER JOIN low_t2 l ON h.ticker =l.ticker
ORDER BY ticker;

 

 

내코드2 (high /low 각 테이블에서 구한 후 집계하기)

-- WHERE 절 다중컬럼 서브쿼리 활용해서 HIGH/LOW CTE 각각 만들어서 해결 
WITH high AS (
	SELECT ticker,DATE_FORMAT(date,'%Y%m') AS highest_mnth,OPEN AS highest_open
	FROM stock_prices
	WHERE (ticker,open) IN (SELECT ticker,MAX(OPEN) FROM stock_prices GROUP BY ticker)
	 AND OPEN IS NOT null
),low AS (
	SELECT ticker,DATE_FORMAT(date,'%Y%m') AS lowest_mnth,OPEN AS lowest_open
	FROM stock_prices
	WHERE (ticker,open) IN (SELECT ticker,MIN(OPEN) FROM stock_prices GROUP BY ticker)
	AND OPEN IS NOT null
)
SELECT h.ticker,highest_mnth,highest_open,lowest_mnth,lowest_open
FROM high h 
	INNER JOIN low l ON h.ticker=l.ticker 
ORDER BY 1

 

최종 최적화 쿼리

-- 순서 부여 CTE 만든후, CASE WHEN 조건으로 피벗팅 
WITH rank_t AS(
	SELECT DATE_FORMAT(date,'%Y%m') AS dt,ticker,open
		  ,DENSE_RANK () OVER (PARTITION BY ticker ORDER BY open DESC) AS high
		  ,DENSE_RANK () OVER (PARTITION BY ticker ORDER BY open ASC) AS low
	FROM stock_prices
	WHERE OPEN IS NOT NULL
)
SELECT ticker
        ,MAX(CASE WHEN high =1 THEN dt END) AS highest_mnth
        ,MAX(CASE WHEN high =1 THEN open END) AS highest_open
        ,MAX(CASE WHEN low =1 THEN dt END) AS lowest_mnth
        ,MAX(CASE WHEN low =1 THEN open END) AS lowest_open
FROM rank_t
GROUP BY ticker
ORDER BY ticker

 

 

이슈 및 해결 과정

  • 문제 ) 최고가 /최저가 값만 추출하는게 아니라, 그 값의 날짜까지 같이 추출해주어야함 
  • 코드1 ) 최고가/최고가일자/최저가/최저가일자 각각 4개의 cte 만들어서 최종 아우터쿼리에서 join
  • 코드2 ) 최고가&최고가일자 / 최저가&최저가일자 각각 cte로 생성 ( 값 - 일자를 가져오기 위해 다중컬럼 서브쿼리 활용)
    • 쿼리는 짧아졌으나, 다중컬럼 서브쿼리는 리소스가 많이 듦 
  • 코드3 - 최적화) 최고가/최저가를 각각 넘버링 해준후 아우터쿼리에서 피벗팅 & 그룹화 뽑아줌

한줄 포인트

  • pass 
  • 값 - 해당 날짜를 여러개 조회해야하는경우 넘버링 & 피벗팅+ 그룹바이 활용!!!!!!!

☑️  모든 서버의 총 실행시간 집계하기 

[문제]

[문제 풀이]

내 코드 -> 오답 

WITH organized AS( -- 혹시 몰라 정렬 & 단독이벤트 제외
	SELECT *
	FROM server_utilization s1
	WHERE server_id IN (SELECT server_id FROM server_utilization GROUP BY server_id HAVING COUNT(DISTINCT session_status) =2)
	ORDER BY server_id,status_time
), time_t AS (
SELECT server_id,session_status
	  ,status_time AS start_time
	  ,LEAD(status_time) OVER(PARTITION BY server_id) AS stop_time
FROM organized
), time_diff AS(
SELECT *
	  ,FLOOR(TIMESTAMPDIFF(SECOND,START_time,stop_time)/86400) AS diff
FROM time_t
WHERE session_status= 'start'
)
SELECT SUM(diff) AS total_uptime_days
FROM time_diff;

 

수정 코드 

WITH organized AS( -- 혹시 몰라 정렬 & 단독이벤트 제외
	SELECT *
	FROM server_utilization s1
	WHERE server_id IN (SELECT server_id FROM server_utilization GROUP BY server_id HAVING COUNT(DISTINCT session_status) =2)
	ORDER BY server_id,status_time
), time_t AS (
SELECT server_id,session_status
	  ,status_time AS start_time
	  ,LEAD(status_time) OVER(PARTITION BY server_id) AS stop_time
FROM organized
), time_diff AS(
SELECT *
	  ,TIMESTAMPDIFF(SECOND,START_time,stop_time)/86400 AS diff
FROM time_t
WHERE session_status= 'start'
)
SELECT FLOOR(SUM(diff)) AS total_uptime_days
FROM time_diff;

 

정답 코드

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) AS total_uptime_days
FROM server_uptime_second;

이슈 및 해결 과정

  • 오답 이유 : 내림 순서 오류
    • 일자로 변경 후 -> 합집계 -> 소수점 내림 해야함 

한줄 포인트

  • FAIL
  • 행단위로 시작- 끝 시각 저장되어있는 경우,
    • 끝시각 LEAD로 가져온후 -> 원본 시작시각 & LEAD 끝시각 필터링 -> TIMESTAMPDIFF -> 합 집계

 전체 QCC  회고 (1-7회차)

더보기

마지막 QCC 까지 끝났다.

QCC 진행하면서 느낀점은 문제의 디테일/ 쿼리의 디테일을 자꾸 놓친다는 것

3번 문제에서 소수점 내림차를 먼저 해버린다거나, 6회차에서 숫자1을 문자 1로 타입을 잘못 출력한다거나..

결론은, 실무에서도 디테일을 놓치면 전체 값이 크게 틀어지는 경우가 있으니 조건을 잘 파악하고 놓치지 않는 연습이 더 필요하겠다.