[MySQL] 손코딩 연습 (윈도우함수, CTE, JOIN 등) - 1

🔹 손코딩 전략

1. CTE로 단계 나누기 → 논리 표현 명확, 읽기 쉬움
2. 윈도우 함수 적극 활용 → Top-N, 동점 처리 등
3. 주석 활용 → 생각 과정 전달
4. 우선 간단한 문제부터 풀고, 어려운 문제는 논리만 주석으로 표현

 

 

🔹 개념 확인

Window Function(함수 적용 열) OVER (PARTITION BY 그룹 열 ORDER BY 순서열)

• Window function: 순위 함수 rank, dense_rank, sum, max 등
• PARTITION by: 소그룹으로 분류 (=Group By와 같은 기능)
• Order by: 분류된 소그룹 정렬
• 순서열: 정렬 기준 행 설정

그룹 내 순위 함수

RANK
DENSE_RANK
ROW_NUMBER	순번 매기기

 

 

그룹 내 집계함수

SUM
MAX
MIN, COUNT, AVG

 

 

데이터 위치 바꾸기

LAG
LEAD

 

 

 

 

 

 

🔹 문제 풀이

문제 1. 유저별 최고 점수 조회 (윈도우 함수 + CTE 연습)

Scores
| user\_id | game\_id | score | game\_date |

• 각 user_id별로 최고 점수(max score)와 해당 게임_id를 조회하시오.
• 동점일 경우 game_id가 가장 작은 값을 선택.
• 결과는 user_id 오름차순으로 정렬.

 

문제 2. 월별 매출 상위 3 상품 조회

테이블: Orders

| order_id | product_id | user_id | amount | order_date |

• 월별(YYYY-MM)로 가장 많이 판매된 상위 3개 product_id와 총 판매량(amount 합계) 출력
• 동률 처리 시 product_id 작은 순

WITH monthlyTotal AS (

SELECT date_format(order_Date) as month, product_id, sum(amount) as total_amount FROM orders group by month, product_id

), 

RankedSales AS(

Select month, product_id, total_amount, row_number() over (partition by month order by total_amount order by total_amount desc, product_id) AS rn

FROM monthlyTotal

)

SELECT month, product_id, total_amount FROM RankedSales 

WHERE rn <=3 ORDER BY month, product_id;

 

 

문제 3. 미구매 유저 조회 (NULL 처리 + LEFT JOIN 연습)

Users

| user_id | user_name |

Orders

| order_id | user_id | product_id | order_date |

• 주문 내역이 없는 유저(user)를 모두 조회하시오.
• 결과는 user_id 오름차순

SELECT * FROM users u LEFT JOIN orders o ON u.user_id = order_id 
WHERE o.order_id IS NULL
ORDER BY user_id;

 

 

문제 1. 그룹 집계 + 정렬 (기본기)

 

Orders

| order_id | user_id | product_id | amount | order_date |

• 각 user_id별 총 구매 금액(SUM(amount))을 구하시오.
• 총 구매 금액이 큰 순서대로 정렬하되, 동률일 경우 user_id 오름차순으로 정렬하시오.

SELECT user_id, sum(amount) as total_amount FROM orders GROUP BY user_id
Order by total_amount desc, user_id;

 

 

문제 2. Top-N 문제 (윈도우 함수)

 

테이블: Scores

| user_id | game_id | score | game_date |

 

• 각 user_id별로 가장 높은 점수를 기록한 game_id와 score를 조회하시오. 
• 동률일 경우 game_date가 빠른 것을 선택하시오.
• 결과는 user_id 오름차순으로 정렬하시오.

WITH ranked AS (

SELECT *, ROW_NUMBER() OVER (PARTITION BY score ORDER BY gate_date) AS rn

FROM scores

)

SELECT * FROM ranked WHERE rn=1 ORDER BY user_id ASC;

 

문제 3. 존재 여부 확인 (JOIN/NOT EXISTS)

Users | user_id | user_name |

Orders | order_id | user_id | product_id | order_date |

• 주문(Orders)이 한 건도 없는 유저(user)를 조회하시오.
• 결과는 user_id 오름차순으로 정렬하시오.

SLECT u.* FROM users u 
	LEFT JOIN orders o ON u.user_id =o.user_id
	AND o.order IS NULL
	ORDER BY user_id ASC;