문제 1

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

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

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

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

좋아요를 받지 않은 페이지의 page_id를 조회하는 SQL 문을 작성하세요.
결과는 page_id 컬럼만 포함하며, 오름차순으로 정렬되어야 합니다.

풀이

SELECT p.page_id 
FROM pages p LEFT JOIN page_likes pl ON p.page_id = pl.page_id
GROUP BY p.page_id
HAVING COUNT(user_id) = 0 
ORDER BY 1;

📍 point

• pages에 page_likes를 LEFT JOIN 해야 한다.

---

문제 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)이 기록된 경우만 결과에 포함합니다.
• 결과는 티커 심볼을 기준으로 오름차순 정렬해야 합니다.

풀이

어렵게 풀기

WITH CTE2 AS (
	WITH CTE AS (
		SELECT ticker,
			MAX(open) AS highest_open,
			MIN(open) AS lowest_open
		FROM stock_prices sp
		WHERE open IS NOT NULL
		GROUP BY ticker) 
	SELECT sp.date, sp.ticker, sp.open, sp.high, sp.low, sp.close, CTE.highest_open, CTE.lowest_open, 
		IF(sp.open = CTE.highest_open, 1, 0) AS MAX_DATE,
		IF(sp.open = CTE.lowest_open, 1, 0) AS MIN_DATE
	FROM stock_prices sp LEFT JOIN CTE ON sp.ticker = CTE.ticker
	WHERE sp.open = CTE.highest_open OR sp.open = CTE.lowest_open)
SELECT DISTINCT ticker, 
	(SELECT DATE_FORMAT(date, '%Y%m') FROM CTE2 AS CTE3 WHERE CTE3.ticker = CTE2.ticker AND CTE3.MAX_DATE = 1) AS highest_mth,
	highest_open,
	(SELECT DATE_FORMAT(date, '%Y%m') FROM CTE2 AS CTE3 WHERE CTE3.ticker = CTE2.ticker AND CTE3.MIN_DATE = 1) AS lowest_mth,
	lowest_open
FROM CTE2
ORDER BY ticker;

1. open이 NULL이 아닌 행에 대해 ticker 별로 그룹화하여 최고가와 최저가를 구한다. (CTE)
   
2. stock_prices에 CTE를 조인하여 최고가와 최저가인 행만 남기고, 해당 행이 최고가인 날인지, 최저가인 날인지를 저장하는 컬럼(MAX_DATE MIN_DATE)을 추가한다. (CTE)
   
3. SELECT절에서 서브쿼리를 이용하여 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;

1. date의 표기를 %Y%m 형태로 변환하고, NULL이 아닌 시작가만 남긴다.
2. ROW_NUMBER 윈도우 함수에서 ORDER BY를 이용하여 최저가와 최고가 순위를 매긴다.
3. ticker 별로 그룹화하여 최고가 날짜, 최고가, 최저가 날짜, 최저가를 출력하고 정렬한다.

📍 point

• ROW_NUMBER 윈도우 함수를 이용하여 최고가와 최저가 행을 찾는다.
• MAX()를 이용하여 그룹화에서 오류가 발생하지 않도록 한다.

---

문제 3

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

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

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

• 각 start 세션에 해당하는 stop 세션만 계산에 포함됩니다.
• server_id에서 start 또는 stop이 단독으로 존재하는 경우는 계산에서 제외합니다.
• 결과는 소수점을 버림하여 정수 일 단위로 출력해야 합니다.
  • 서버별로 실행 시간을 더하고 버림을 진행합니다.
  • 예시: 5.5일 → 5일, 5.1일 → 5일, 5.99999일 → 5일 (소수점 이하 버림)
  • 버림이 완료된 서버별 일자들을 최종적으로 더해줍니다.

풀이

WITH CTE2 AS (
	WITH CTE AS (
		SELECT *, 
			LEAD(status_time) OVER(PARTITION BY server_id) AS stop_time,
			LEAD(session_status) OVER (PARTITION BY server_id) AS next_status
		FROM server_utilization su 
	)
	SELECT FLOOR(SUM(TIMESTAMPDIFF(SECOND, status_time, stop_time)/86400)) AS sum_day
	FROM CTE
	WHERE session_status = 'start' AND next_status = 'stop'
	GROUP BY server_id
)
SELECT SUM(sum_day) AS total_uptime_days
FROM CTE2;

📍 point

• LEAD() 윈도우 함수를 이용해 다음 세션이 발생한 시간을 끌고 온다.
• WHERE절을 이용해 start로 시작해 stop으로 끝나는 행만 남긴다.