실전 문제 풀이 (6)

문제 1 — 고객별 최근 주문 2건 조회

CREATE TABLE orders_p1 (
  order_id INT,
  customer_id INT,
  order_date DATE,
  amount INT
);

INSERT INTO orders_p1 VALUES
(101, 1, '2025-07-01', 100),
(102, 1, '2025-07-05', 150),
(103, 1, '2025-07-05', 200),
(201, 2, '2025-06-01', 50),
(202, 2, '2025-07-10', 75),
(301, 3, '2025-05-20', 300);

 

목표

• 각 고객별로 최근 주문일 순으로 2건만 출력
• 같은 날짜 주문은 order_id 오름차순 정렬
• 출력: customer_id, order_id, order_date, amount

WITH date_table AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC, order_id
      ) AS rn
  FROM orders_p1
  )
  
SELECT
  customer_id,
  order_id,
  order_date,
  amount
FROM date_table
WHERE rn <= 2

 

 

 

문제 2 — 카테고리별 매출 상위 1개 상품

CREATE TABLE products (
  product_id INT,
  category VARCHAR(50)
);

CREATE TABLE sales (
  sale_id INT,
  product_id INT,
  quantity INT,
  price INT
);

INSERT INTO products VALUES
(1, '전자'), (2, '전자'), (3, '가구'), (4, '가구');

INSERT INTO sales VALUES
(1, 1, 3, 1000),
(2, 1, 2, 1000),
(3, 2, 5, 500),
(4, 3, 1, 2000),
(5, 4, 3, 1000),
(6, 4, 2, 1000);

 

목표

• products와 sales를 조인
• 카테고리별 매출합계가 가장 높은 상품 1개 출력
• 동점이면 상품ID 오름차순
• 출력: category, product_id, total_sales

WITH join_table AS (
  SELECT
    s.product_id,
    p.category,
    sale_id,
    quantity,
    price
  FROM sales s
  JOIN products p
    ON s.product_id = p.product_id
),

rank_table AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY price * quantity DESC, product_id
      ) AS rn
  FROM join_table
  )
  
SELECT
  category,
  product_id,
  quantity,
  price
FROM rank_table
WHERE rn = 1

 

 

 

문제 3 — 전월 대비 매출 증가율

CREATE TABLE monthly_sales (
  month DATE,
  revenue INT
);

INSERT INTO monthly_sales VALUES
('2025-01-01', 1000),
('2025-02-01', 1200),
('2025-03-01', 1100),
('2025-04-01', 1500);

 

목표

• monthly_sales 테이블에서 각 달의 매출과 전월 대비 증가율(%) 계산
• 증가율 = (이번달 - 지난달) / 지난달 * 100
• 첫 달은 증가율 NULL
• 출력: month, revenue, pct_change

WITH grow_table AS (
  SELECT
    month,
    revenue,
    (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100 AS pct_change
  FROM monthly_sales
)

SELECT
  month,
  revenue,
  pct_change
FROM grow_table

 

 

 

문제 4 — 직원별 부서 평균급여 대비 차이

CREATE TABLE employees (
  emp_id INT,
  dept_id INT,
  salary INT
);

INSERT INTO employees VALUES
(1, 10, 5000),
(2, 10, 6000),
(3, 20, 7000),
(4, 20, 6500),
(5, 20, 8000);

 

목표

• employees 테이블에서 같은 부서(dept_id) 평균급여를 구하고,
  직원의 급여와 평균급여 차이 계산
• 출력: emp_id, dept_id, salary, avg_salary, salary_diff

WITH avg_table AS (
  SELECT
    *,
    AVG(salary) OVER (
      PARTITION BY dept_id
      ) AS avg_salary
  FROM employees
  )
  
SELECT
  emp_id,
  dept_id,
  salary,
  avg_salary,
  salary - avg_salary AS salary_diff
FROM avg_table

 

 

 

문제 5 — 연속 로그인 일수 구하기

CREATE TABLE user_logins (
  user_id INT,
  login_date DATE
);

INSERT INTO user_logins VALUES
(1, '2025-07-01'),
(1, '2025-07-02'),
(1, '2025-07-03'),
(1, '2025-07-05'),
(2, '2025-06-10'),
(2, '2025-06-11'),
(2, '2025-06-13');

 

목표

• user_logins에서 각 유저별로 가장 긴 연속 로그인 일수 계산
• 연속은 하루씩 차이날 때로 판단
• 출력: user_id, max_streak

WITH count_table AS (
  SELECT
    user_id,
    login_date,
    DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS criterion
  FROM user_logins
  ),

group_table AS (
  SELECT 
    user_id,
    COUNT(*) AS continue_login_count
  FROM count_table
  GROUP BY user_id, criterion
  )

SELECT 
 user_id,
 MAX(continue_login_count)
FROM group_table
GROUP BY user_id

 

 

 

---

 

2번 문제에서 total_sales를 구해서 그것을 기준으로 계산해야 했지만, CTE를 한번 더 사용하기에는 코드가 길어질 것 같아서 total_sales를 안구하고 문제를 풀었더니 GPT가 귀신같이 잡아냈다... 무섭다...