서브쿼리 기초 3 *

문제 3: 특정 날짜 이후의 거래 내역

데이터셋 1: transactions

transaction_id	customer_id	date	amount
1	101	2023-01-01	200
2	102	2023-03-15	500
3	103	2023-02-10	300
4	101	2023-04-20	400
5	102	2023-05-25	600

 

데이터셋 2: customers

customer_id	name
101	Alice
102	Bob
103	Charlie

 

문제

'Alice' 고객이 거래한 가장 최근 날짜 이후에 거래한 모든 고객의 이름과 거래 내역을 출력하세요.

 

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    customer_id INT,
    date DATE,
    amount INT
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO transactions (transaction_id, customer_id, date, amount) VALUES
(1, 101, '2023-01-01', 200),
(2, 102, '2023-03-15', 500),
(3, 103, '2023-02-10', 300),
(4, 101, '2023-04-20', 400),
(5, 102, '2023-05-25', 600);

INSERT INTO customers (customer_id, name) VALUES
(101, 'Alice'),
(102, 'Bob'),
(103, 'Charlie');

SELECT c.name, t.date, t.amount
FROM transactions t
JOIN customers c ON t.customer_id = c.customer_id
WHERE t.date > (
    SELECT MAX(t2.date)
    FROM transactions t2
    JOIN customers c2 ON t2.customer_id = c2.customer_id
    WHERE c2.name = 'Alice'
);