실전 문제 풀이 (5)

오늘은 SQL 고급 문제를 풀어보려고 합니다. GPT한테 고급문제를 요청하니까 문제마다 테이블을 주는 것이 아니라 여러 테이블을 주고 그걸 JOIN 해가면서 푸는 문제를 주더라고요. 그래서 이번엔 문제별로 테이블을 작성하는 것이 아니기 때문에 보기는 조금 불편할 수 있을 것 같습니다.

 

- 테이블 생성

-- 주문 테이블
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount INT
);

INSERT INTO orders (order_id, user_id, order_date, amount) VALUES
(1, 101, '2025-07-01', 500),
(2, 101, '2025-07-03', 300),
(3, 102, '2025-07-01', 200),
(4, 102, '2025-07-02', 150),
(5, 102, '2025-07-04', 400),
(6, 103, '2025-07-01', 700),
(7, 103, '2025-07-10', 100),
(8, 104, '2025-07-02', 250),
(9, 104, '2025-07-03', 350),
(10, 104, '2025-07-05', 500);

-- 사용자 테이블
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    join_date DATE
);

INSERT INTO users (user_id, name, join_date) VALUES
(101, 'Alice', '2025-01-10'),
(102, 'Bob', '2025-02-14'),
(103, 'Charlie', '2025-03-20'),
(104, 'Diana', '2025-04-05');

-- 리뷰 테이블
CREATE TABLE reviews (
    review_id INT PRIMARY KEY,
    user_id INT,
    order_id INT,
    rating INT
);

INSERT INTO reviews (review_id, user_id, order_id, rating) VALUES
(1, 101, 1, 5),
(2, 101, 2, 4),
(3, 102, 3, 3),
(4, 102, 5, 5),
(5, 103, 6, 4),
(6, 104, 8, 2),
(7, 104, 10, 5);

 

생성한 테이블은 아래와 같다.

 

📦 orders (주문 테이블)

order_id	user_id	order_date	amount
1	101	2025-07-01	500
2	101	2025-07-03	300
3	102	2025-07-01	200
4	102	2025-07-02	150
5	102	2025-07-04	400
6	103	2025-07-01	700
7	103	2025-07-10	100
8	104	2025-07-02	250
9	104	2025-07-03	350
10	104	2025-07-05	500

 

👤 users (사용자 테이블)

user_id	name	join_date
101	Alice	2025-01-10
102	Bob	2025-02-14
103	Charlie	2025-03-20
104	Diana	2025-04-05

 

⭐ reviews (리뷰 테이블)

review_id	user_id	order_id	rating
1	101	1	5
2	101	2	4
3	102	3	3
4	102	5	5
5	103	6	4
6	104	8	2
7	104	10	5

 

문제 1 — 최근 주문 금액 TOP 2

각 사용자별로 가장 최근 주문일 기준으로 주문 금액 상위 2건을 구하시오.
단, 같은 날 여러 주문이 있을 경우 모두 포함하세요.

WITH rank_table AS (
  SELECT
    order_id,
    user_id,
    order_date,
    amount,
    DENSE_RANK() OVER (
      PARTITION BY user_id
      ORDER BY order_date DESC, amount DESC
      ) AS rn
  FROM orders
  )

SELECT
  order_id,
  user_id,
  order_date,
  amount
FROM rank_table
WHERE rn <= 2

 

 

 

문제 2 — 평균 이상 주문

사용자별 평균 주문 금액보다 높은 주문만 조회하시오.
결과에는 user_id, order_id, amount, avg_amount 컬럼이 포함되어야 함.

WITH avg_table AS (
  SELECT
    order_id,
    user_id,
    order_date,
    amount,
    AVG(amount) OVER (
      PARTITION BY user_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      ) AS avg_amount
  FROM orders
  )

SELECT
  order_id,
  user_id,
  amount,
  avg_amount
FROM avg_table
WHERE amount > avg_amount

 

 

 

문제 3 — 연속 주문 고객

3일 연속으로 주문한 적이 있는 고객의 user_id와 해당 시작 날짜를 구하시오.

SELECT
  o1.user_id,
  o1.order_date
FROM orders o1
JOIN orders o2
  ON o1.user_id = o2.user_id
JOIN orders o3
  ON o1.user_id = o3.user_id
WHERE DATEDIFF(o2.order_date, o1.order_date) = 1 AND DATEDIFF(o3.order_date, o1.order_date) = 2

 

이 문제를 풀다보니 저번에 3일 연속 이상으로 주문한 고객 찾는 문제가 떠올라서 복습겸 한번 더 풀어보기로 했다.

WITH date_table AS (
  SELECT
    *,
    DATE_SUB(order_date, INTERVAL ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ) DAY) AS day
  FROM orders
  )
  
SELECT
  user_id,
  day
FROM date_table
GROUP BY user_id, day
HAVING COUNT(*) >= 3

 

 

 

문제 4 — 리뷰 점수 순위

모든 리뷰의 평균 점수를 구하고, 이 평균보다 높은 평균 점수를 가진 사용자들을 평균 점수 내림차순으로 출력하시오.
출력 컬럼: user_id, avg_rating

WITH avg_table AS (
  SELECT
    *,
    AVG(rating) OVER (
      PARTITION BY user_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      ) AS avg_rating,
    AVG(rating) OVER (
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      ) AS total_avg_rating
  FROM reviews
  )
  
SELECT
 DISTINCT user_id,
 avg_rating,
 total_avg_rating
FROM avg_table
WHERE avg_rating > total_avg_rating

 

 

 

문제 5 — 첫 주문과 마지막 주문 간 차이

각 사용자별로 첫 주문일과 마지막 주문일의 날짜 차이를 계산하시오.
컬럼명: user_id, first_order, last_order, day_diff

WITH date_table AS (
  SELECT
  *,
  FIRST_VALUE(order_date) OVER (
    PARTITION BY user_id
    ) AS first_order,
  LAST_VALUE(order_date) OVER (
    PARTITION BY user_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order
  FROM orders
  )

SELECT
  user_id,
  first_order,
  last_order,
  DATEDIFF(last_order, first_order) AS day_diff
FROM date_table

 

 

 

---

 

오늘 GPT 5로 버전이 바뀌었는데. 뭔가 문제설명이 조금 난해해졌다. 내가 문제 생성 요청을 잘못한건가? 다음부터 신경 써야겠다.