서브쿼리 기초 2

문제 2: 중복 데이터 필터링

데이터셋: orders

order_id	customer_id	total_amount
1	101	300
2	102	500
3	101	700
4	103	200
5	102	1000

 

문제

각 고객(customer_id)의 **가장 큰 주문 금액(total_amount)**을 출력하세요.

 

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    total_amount INT
);

INSERT INTO orders (order_id, customer_id, total_amount) VALUES
(1, 101, 300),
(2, 102, 500),
(3, 101, 700),
(4, 103, 200),
(5, 102, 1000);

SELECT customer_id, total_amount
FROM orders o1
WHERE total_amount = (SELECT MAX(o2.total_amount) FROM orders o2 WHERE o1.customer_id = o2.customer_id)