/* You are working with a transactions table that contains the following columns:

transaction_id INT: Unique ID for each transaction
transaction_date DATE: Date of the transaction
amount INT: Amount of the transaction

Write a SQL query to calculate the total revenue generated for each month in the last year.
If no transactions occurred in a month, display that month with a total revenue of 0. */

CREATE TABLE transactions (  
    transaction_id INT PRIMARY KEY,  
    transaction_date DATE NOT NULL,  
    amount INT NOT NULL  
);  

INSERT INTO transactions (transaction_id, transaction_date, amount) VALUES  
(1, '2024-01-15', 200),  
(2, '2024-02-20', 150),  
(3, '2024-03-10', 300),  
(4, '2024-04-25', 250),  
(5, '2024-05-05', 180),  
(6, '2024-06-14', 220),  
(7, '2024-07-19', 270),  
(8, '2024-08-09', 320),  
(9, '2024-09-12', 190),  
(10, '2024-10-08', 230),  
(11, '2024-11-18', 400),  
(12, '2024-12-05', 280),
(13, '2023-01-12', 210),  
(14, '2023-02-22', 180),  
(15, '2023-03-15', 290),  
(16, '2023-04-18', 260),  
(17, '2023-05-25', 170),  
(18, '2023-06-20', 300),  
(19, '2023-07-07', 310),  
(20, '2023-08-15', 220),  
(21, '2023-09-14', 400),  
(22, '2023-10-10', 230),  
(23, '2023-11-05', 310),  
(24, '2023-12-12', 280),  
(25, '2023-01-08', 250),  
(26, '2023-02-17', 310),  
(27, '2023-03-11', 200),  
(28, '2023-04-22', 180),  
(29, '2023-05-30', 190),  
(30, '2023-06-05', 320),  
(31, '2023-07-20', 270),  
(32, '2023-08-30', 290),  
(33, '2023-09-03', 210),  
(34, '2023-10-22', 240),  
(35, '2023-11-30', 260),  
(36, '2023-12-25', 300);  

SELECT  
    MONTH(transaction_date) AS month,  
    COALESCE(SUM(amount), 0) AS total_revenue  
FROM transactions
WHERE YEAR(transaction_date) = YEAR(CURDATE()) - 1 
GROUP BY month
ORDER BY month;