[MySQL] 손코딩 연습 - 2

🔹 손코딩 전략

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

 

** LAG (참조열, 몇번째 이전행, 다음행이 없을떄 채울값)

** LEAD(참조열,  몇번째 다음행, 다음행이 없을 때 채울값)

---

문제 1. N등 구하기

테이블: Scores
| user_id | game_id | score | game_date |

• 각 game_id별 점수를 기준으로 2등 점수를 조회하시오.
• 2등이 없는 경우(게임이 1번만 있는 경우)는 결과에서 제외하시오.

 

 

 

 

 

 

 

문제 2. 월별 순위

테이블: Orders
| order_id | product_id | amount | order_date |

• 월별(YYYY-MM) product_id별 총 판매량(SUM amount)을 구하시오.
• 각 월별 판매량 순위(RANK)를 매겨 출력하시오.
• 결과 컬럼: month, product_id, total_amount, rank

 

 

문제 3. 전월 대비 증감

테이블: Sales
| month | product_id | revenue |

• product_id별로 월별 revenue를 조회하되,
  전월 대비 증감액(revenue - 이전달 revenue)을 함께 출력하시오.
• 결과 컬럼: month, product_id, revenue, diff_from_last_month

WITH diff_revenue AS (
    SELECT
        month,
        product_id,
        revenue,
        revenue - LAG(revenue, 1, 0) OVER (
            PARTITION BY product_id
            ORDER BY month
        ) AS diff_from_last_month
    FROM Sales
),

SELECT month, product_id, revenue, diff_from_last_month
FROM diff_revenue
ORDER BY product_id, month;

 

 

 

문제 4. 연속 출석일수

테이블: Attendance
| user_id | attend_date |

• 유저별로 연속 출석일수를 계산하시오.
• 결과는 user_id, attend_date, streak_count 형태로 출력하시오.

WITH streak_table AS (
    SELECT
        user_id,
        attend_date,
        CASE 
            WHEN DATEDIFF(
                attend_date,
                LAG(attend_date) OVER (PARTITION BY user_id ORDER BY attend_date)
            ) = 1
            THEN 1
            ELSE 0
        END AS is_consecutive
    FROM Attendance
),
streak_calc AS (
    SELECT
        user_id,
        attend_date,
        SUM(is_consecutive) OVER (
            PARTITION BY user_id 
            ORDER BY attend_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) + 1 AS streak_count
    FROM streak_table
)
SELECT user_id, attend_date, streak_count
FROM streak_calc
ORDER BY user_id, attend_date;

 

 

 

 

문제 5. 팀별 최고 점수자

테이블: Users, Scores

Users
| user_id | team_id | user_name |

Scores
| user_id | score | game_date |

• 각 team_id별로 가장 높은 점수를 기록한 유저의 user_name과 score를 조회하시오.
• 동점일 경우 game_date가 가장 빠른 유저를 선택하시오.

WITH ranked_scores AS (
    SELECT
        u.team_id,
        u.user_name,
        s.score,
        s.game_date,
        ROW_NUMBER() OVER (
            PARTITION BY u.team_id
            ORDER BY s.score DESC, s.game_date ASC
        ) AS rn
    FROM Users u
    JOIN Scores s ON u.user_id = s.user_id
)
SELECT team_id, user_name, score, game_date
FROM ranked_scores
WHERE rn = 1;