실전 문제 풀이 (2)

✅ 문제 1. 가장 많이 주문한 사용자 조회

CREATE TABLE orders (
  user_id INT,
  order_id INT,
  amount INT
);

INSERT INTO orders VALUES
(1, 101, 500),
(1, 102, 300),
(2, 201, 900),
(3, 301, 200),
(3, 302, 400);

 

설명: 총 주문 금액이 가장 높은 사용자를 조회한다.

 

테이블 구조:

• orders(user_id, order_id, amount)

요구사항:

• 유저별 총 주문금액 계산
• 총 주문금액이 가장 높은 1명만 출력
• user_id, total_amount 출력

 

SELECT
	user_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
ORDER BY total_amount DESC
LIMIT 1

 

 

✅ 문제 2. 친구 관계에서 상호 친구가 아닌 경우만 조회

CREATE TABLE friend (
  user_id INT,
  friend_id INT
);

INSERT INTO friend VALUES
(1, 2),
(2, 1),
(1, 3),
(4, 1),
(3, 1); -- 3→1, 1→3 둘 다 있으므로 상호 친구

 

설명: friend 테이블에서 일방적인 친구만 조회 (ex. A가 B를 친구로 추가했지만, B는 A를 친구로 추가하지 않음)

 

테이블 구조:

• friend(user_id, friend_id)

요구사항:

• 상호 친구가 아닌 경우만 조회
• user_id, friend_id 출력

SELECT
	f1.user_id,
    f1.friend_id
FROM friend f1
JOIN friend f2
ON f1.friend_id = f2.user_id
 AND f2.friend_id = f1.user_id

 

✅ 문제 3. 각 사용자의 첫 주문일과 마지막 주문일 조회

CREATE TABLE orders (
  user_id INT,
  order_date DATE
);

INSERT INTO orders VALUES
(1, '2024-07-01'),
(1, '2024-07-05'),
(2, '2024-07-02'),
(2, '2024-07-08'),
(3, '2024-07-03');

 

설명: 각 user의 첫 주문일과 마지막 주문일을 출력

 

테이블 구조:

• orders(user_id, order_date)

요구사항:

• user_id, first_order, last_order 출력

SELECT
	user_id,
    MIN(order_date) AS first_order,
    MAX(order_date) As last_order
FROM orders
GROUP BY user_id

 

 

✅ 문제 4. 팀장 기준으로 모든 부하직원 출력

CREATE TABLE employee (
  emp_id INT,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employee VALUES
(1, 'Alice', 2),
(2, 'Bob', 4),
(3, 'Charlie', 2),
(4, 'Diana', NULL),
(5, 'Eve', 1),
(6, 'Frank', 1);

 

설명: 특정 사람(예: 'Diana') 기준으로 하위 직원까지 모두 출력

 

테이블 구조:

• employee(emp_id, name, manager_id)

요구사항:

• Diana의 부하직원 전체 출력
• 컬럼: emp_id, name, manager_id, level (Diana가 level 1)

WITH RECURSIVE tree AS (
  SELECT
  	emp_id,
    name,
    manager_id,
    1 AS level
  FROM employee
  WHERE name = 'Diana'
  
  UNION ALL
  
  SELECT
    e.emp_id,
    e.name,
    e.manager_id,
    t.level + 1
  FROM employee e
  JOIN tree t
    ON e.manager_id = t.emp_id
)

SELECT * FROM tree

 

 

✅ 문제 5. 사용자별로 최근 2건의 주문 출력

CREATE TABLE orders (
  user_id INT,
  order_id INT,
  order_date DATE
);

INSERT INTO orders VALUES
(1, 101, '2024-07-01'),
(1, 102, '2024-07-05'),
(1, 103, '2024-07-10'),
(2, 201, '2024-07-02'),
(2, 202, '2024-07-07'),
(3, 301, '2024-07-03');

 

설명: 각 user의 최근 주문 2건만 출력

 

테이블 구조:

• orders(user_id, order_id, order_date)

요구사항:

• user_id, order_id, order_date 출력

WITH ord AS (
  SELECT
    user_id,
    order_id,
    order_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY order_date DESC
      ) AS rn
  FROM orders
 )
 
SELECT
  user_id,
  order_id,
  order_date
FROM ord
WHERE rn <= 2

 

 

 

 

2번 문제를 반대로 생각했다. 상호 친구가 아닌 경우를 조회해야 하는데 상호 친구인 경우를 조회했다. 따라서 LEFT JOIN 과 IS NULL을 이용해서 상호 친구가 아닌 경우를 조회했다.

SELECT
FROM friend f1
LEFT JOIN friend f2
  ON f1.friend_id = f2.user_id
    AND f1.user_id = f2.friend_id
WHERE f2.user_id IS NULL