-- Given table contains reported covid cases in 2020. 
-- Calculate the percentage increase in covid cases each month versus cumulative cases as of the prior month.
-- Return the month number, and the percentage increase rounded to one decimal. Order the result by the month.

drop table if exists covid_cases;
create table covid_cases
(
	cases_reported	int,
	dates			date	
);
insert into covid_cases values(20124,convert(date, '10/01/2020', 5));
insert into covid_cases values(40133,convert(date, '15/01/2020', 5));
insert into covid_cases values(65005,convert(date, '20/01/2020', 5));
insert into covid_cases values(30005,convert(date, '08/02/2020', 5));
insert into covid_cases values(35015,convert(date, '19/02/2020', 5));
insert into covid_cases values(15015,convert(date, '03/03/2020', 5));
insert into covid_cases values(35035,convert(date, '10/03/2020', 5));
insert into covid_cases values(49099,convert(date, '14/03/2020', 5));
insert into covid_cases values(84045,convert(date, '20/03/2020', 5));
insert into covid_cases values(100106,convert(date, '31/03/2020', 5));
insert into covid_cases values(17015,convert(date, '04/04/2020', 5));
insert into covid_cases values(36035,convert(date, '11/04/2020', 5));
insert into covid_cases values(50099,convert(date, '13/04/2020', 5));
insert into covid_cases values(87045,convert(date, '22/04/2020', 5));
insert into covid_cases values(101101,convert(date, '30/04/2020', 5));
insert into covid_cases values(40015,convert(date, '01/05/2020', 5));
insert into covid_cases values(54035,convert(date, '09/05/2020', 5));
insert into covid_cases values(71099,convert(date, '14/05/2020', 5));
insert into covid_cases values(82045,convert(date, '21/05/2020', 5));
insert into covid_cases values(90103,convert(date, '25/05/2020', 5));
insert into covid_cases values(99103,convert(date, '31/05/2020', 5));
insert into covid_cases values(11015,convert(date, '03/06/2020', 5));
insert into covid_cases values(28035,convert(date, '10/06/2020', 5));
insert into covid_cases values(38099,convert(date, '14/06/2020', 5));
insert into covid_cases values(45045,convert(date, '20/06/2020', 5));
insert into covid_cases values(36033,convert(date, '09/07/2020', 5));
insert into covid_cases values(40011,convert(date, '23/07/2020', 5));	
insert into covid_cases values(25001,convert(date, '12/08/2020', 5));
insert into covid_cases values(29990,convert(date, '26/08/2020', 5));	
insert into covid_cases values(20112,convert(date, '04/09/2020', 5));	
insert into covid_cases values(43991,convert(date, '18/09/2020', 5));	
insert into covid_cases values(51002,convert(date, '29/09/2020', 5));	
insert into covid_cases values(26587,convert(date, '25/10/2020', 5));	
insert into covid_cases values(11000,convert(date, '07/11/2020', 5));	
insert into covid_cases values(35002,convert(date, '16/11/2020', 5));	
insert into covid_cases values(56010,convert(date, '28/11/2020', 5));	
insert into covid_cases values(15099,convert(date, '02/12/2020', 5));	
insert into covid_cases values(38042,convert(date, '11/12/2020', 5));	
insert into covid_cases values(73030,convert(date, '26/12/2020', 5));	




WITH MonthlyCases AS (
    SELECT 
        EXTRACT(MONTH FROM TO_DATE(record_date, 'YYYY-MM-DD')) AS month_number,
        SUM(cases_count) AS total_cases
    FROM 
        daily_cases
    WHERE 
        EXTRACT(YEAR FROM TO_DATE(record_date, 'YYYY-MM-DD')) = 2020
        
    GROUP BY 
        EXTRACT(MONTH FROM TO_DATE(record_date, 'YYYY-MM-DD'))
),
CumulativeCases AS (
    SELECT 
        month_number,
        total_cases,
        SUM(total_cases) OVER (ORDER BY month_number) AS cumulative_cases
    FROM 
        MonthlyCases
)
SELECT 
    c.month_number AS Month,
    c.cumulative_cases AS Cases,
    m.total_cases AS Previous_Month_Cases,
    ROUND(((c.cumulative_cases - m.total_cases) / m.total_cases) * 100, 1) AS Increase
FROM 
    CumulativeCases c
JOIN 
    MonthlyCases m ON c.month_number = m.month_number + 1
ORDER BY 
    c.month_number;