I’m working on a SQL query for a database containing records of customer transactions. Each transaction has a transaction_id, customer_id, transaction_date, and amount. I need to find the latest transaction for each customer.
Here’s a simplified version of the table:
CREATE TABLE transactions (
transaction_id INT PRIMARY KEY,
customer_id INT,
transaction_date DATE,
amount DECIMAL(10, 2)
);
INSERT INTO transactions VALUES
(1, 101, '2023-01-15', 50.00),
(2, 102, '2023-02-10', 30.00),
(3, 101, '2023-02-25', 75.00),
(4, 103, '2023-03-05', 40.00),
(5, 102, '2023-03-12', 55.00);
I want to write an SQL query that retrieves the latest transaction for each customer. In this example, the expected result would include transactions with transaction_id 3 and 4 because they are the latest for customers 101 and 103, respectively.
Could you provide a SQL query to achieve this? Additionally, it would be helpful if you could explain the logic behind the query and any SQL functions or techniques used. Thank you for your assistance!