실전 문제 풀이 (4)

✅ 1번. 유저별 가장 최근 주문 1건 조회하기

문제 설명
orders 테이블에서 각 유저의 가장 최근 주문 1건만 조회하세요.

-- 테이블 생성 및 샘플 데이터
CREATE TABLE orders (
  order_id INT,
  user_id INT,
  order_date DATE,
  amount INT
);

INSERT INTO orders VALUES
(1, 101, '2025-08-01', 500),
(2, 101, '2025-08-02', 300),
(3, 102, '2025-08-01', 200),
(4, 103, '2025-08-01', 700),
(5, 103, '2025-08-03', 400),
(6, 104, '2025-08-02', 900),
(7, 104, '2025-08-02', 800);

WITH rank_table AS (
  SELECT
    order_id,
    user_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY order_date DESC
      ) AS rn
  FROM orders
  )
  
SELECT
 order_id,
 user_id,
 order_date,
 amount
FROM rank_table
WHERE rn = 1

 

 

 

✅ 2번. 전월 대비 매출이 증가한 유저 조회

문제 설명
sales 테이블에서 각 유저의 월별 매출을 기준으로, 이전 달보다 매출이 증가한 월을 찾으세요.

-- 테이블 생성 및 샘플 데이터
CREATE TABLE sales (
  user_id INT,
  sale_month DATE,
  amount INT
);

INSERT INTO sales VALUES
(1, '2025-06-01', 1000),
(1, '2025-07-01', 1200),
(1, '2025-08-01', 1100),
(2, '2025-06-01', 2000),
(2, '2025-07-01', 1900),
(2, '2025-08-01', 2200);

WITH avg_table AS (
  SELECT
    user_id,
    sale_month,
    amount,
    LAG(AMOUNT) OVER (
      PARTITION BY user_id
      ORDER BY sale_month
      ) AS last_month_sale
  FROM sales
)

SELECT
  user_id,
  sale_month,
  amount,
  last_month_sale
FROM avg_table
WHERE amount > last_month_sale

 

 

 

✅ 3번. 친구 추천 시스템

문제 설명
friend 테이블에서 user_id의 친구의 친구이지만, 자신과 직접 친구가 아닌 사람들을 추천 대상으로 출력하세요.
중복 없이 user_id, recommended_friend 형태로 보여주세요.

-- 테이블 생성 및 샘플 데이터
CREATE TABLE friend (
  user_id INT,
  friend_id INT
);

INSERT INTO friend VALUES
(1, 2),
(1, 3),
(2, 4),
(3, 4),
(4, 5),
(2, 5),
(5, 6),
(1, 6);  -- 직접 친구면 추천 제외

WITH ff_table AS (
  SELECT
   f1.user_id,
   f2.friend_id AS friend_of_friend
  FROM friend f1
  JOIN friend f2
    ON f1.friend_id = f2.user_id
)

SELECT
  ff.user_id,
  ff.friend_of_friend
FROM ff_table ff
JOIN friend f3
ON ff.user_id = f3.user_id
WHERE ff.friend_of_friend != f3.friend_id
GROUP BY ff.user_id, ff.friend_of_friend

 

 

 

✅ 4번. 누적 합계가 처음으로 1000을 넘는 시점 찾기

문제 설명
transactions 테이블에서 날짜 순으로 정렬했을 때, 누적 합계가 처음으로 1000을 초과한 날짜를 구하세요.

-- 테이블 생성 및 샘플 데이터
CREATE TABLE transactions (
  transaction_id INT,
  transaction_date DATE,
  amount INT
);

INSERT INTO transactions VALUES
(1, '2025-08-01', 100),
(2, '2025-08-02', 200),
(3, '2025-08-03', 300),
(4, '2025-08-04', 150),
(5, '2025-08-05', 300),
(6, '2025-08-06', 200);

WITH sum_table AS (
  SELECT
    transaction_id,
    transaction_date,
    amount,
    SUM(amount) OVER (
      ORDER BY transaction_date
      ) AS cumulate_sum
  FROM transactions
  )
  
SELECT
  transaction_date,
  cumulate_sum
FROM sum_table
WHERE cumulate_sum >= 1000
LIMIT 1

 

 

 

✅ 5번. 카테고리별 두 번째로 많이 팔린 상품 구하기

문제 설명
products 테이블에서 카테고리별 판매량 2위인 상품을 조회하세요.
category, product_name, sales를 출력하세요.

-- 테이블 생성 및 샘플 데이터
CREATE TABLE products (
  product_id INT,
  category VARCHAR(50),
  product_name VARCHAR(50),
  sales INT
);

INSERT INTO products VALUES
(1, '과일', '사과', 500),
(2, '과일', '바나나', 600),
(3, '과일', '포도', 300),
(4, '채소', '상추', 400),
(5, '채소', '시금치', 200),
(6, '채소', '당근', 450);

WITH rank_table AS (
  SELECT
    product_id,
    category,
    product_name,
    sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY sales DESC
      ) AS rn
  FROM products
  )
  
SELECT
  category,
  product_name,
  sales
FROM rank_table
WHERE rn = 2

 

 

 

---

 

 

3번을 틀렸다. 저번에 JOIN 공부할 때 비슷한 문제를 풀었던 것 같은데 여기서 다시 정립하고 가야겠다. 오히려 좋아

WITH ff_table AS (
  SELECT
    f1.user_id,
    f2.friend_id AS friend_of_friend
  FROM friend f1
  JOIN friend f2
    ON f1.friend_id = f2.user_id
  WHERE f1.user_id != f2.friend_id
)

SELECT 
  DISTINCT ff.user_id,
  ff.friend_of_friend
FROM ff_table ff
LEFT JOIN friend f3
  ON ff.user_id = f3.user_id AND ff.friend_of_friend = f3.friend_id
WHERE f3.user_id IS NULL

 

마지막 조인 조건이 조금 헷갈렸다.

LEFT JOIN friend f3
  ON ff.user_id = f3.user_id AND ff.friend_of_friend = f3.friend_id

 

요약하자면 ff_table은 user의 친구의 친구 id를 가지고 있는 테이블이다. 우리는 친구의 친구가 직접 친구인지 확인해야하는데 그걸 확인하는 과정이라고 생각하면 편했다. 직접친구인지 확인하고 직접친구라면 제거하는 의미로 마지막에 IS NULL을 사용한거다.