% of revenue chnage from previous month and previous year of same month - MySQL

Question

Input data

    Date         State  Revenue 
   31-01-2020     M       100
   05-05-2020     M       500
   05-05-2020     k       500
   31-05-2020     M       100
   12-04-2021     K       250
   15-04-2021     M       300
   20-05-2021     K       250
   21-05-2021     M       300

Desired output Expected Output:

The expected output is expected only for the Month of May'21. Other values can be discarded

STATE Total_Revenue  % change of revenue compared to the previous month of the state     % change of revenue compared to same month from previous year for the state
 M
 K

Im stuck with the problem and I'm still stuck in learning phase of SQL

Answer 1

SELECT
State,
SUM(Revenue) AS Total_Revenue,
(SUM(Revenue) - LAG(SUM(Revenue)) OVER (PARTITION BY State ORDER BY 
MONTH(Date))) / LAG(SUM(Revenue)) OVER (PARTITION BY State ORDER BY MONTH(Date)) 
* 100 AS `% change of revenue compared to the previous month of the state`,
(SUM(Revenue) - SUM(CASE WHEN YEAR(Date) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR) 
AND MONTH(Date) = MONTH(CURRENT_DATE - INTERVAL 1 YEAR) THEN Revenue ELSE 0 
END)) / SUM(CASE WHEN YEAR(Date) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR) AND 
MONTH(Date) = MONTH(CURRENT_DATE - INTERVAL 1 YEAR) THEN Revenue ELSE 0 END) * 
100 AS `% change of revenue compared to same month from previous year for the 
 state`
 FROM
 table_name
 WHERE
 MONTH(Date) = 5 -- Filter for the month of May
 AND YEAR(Date) = YEAR(CURRENT_DATE) -- Filter for the current year
 GROUP BY
  State;

The query uses window functions (LAG) to calculate the revenue change compared to the previous month for each state. It also uses conditional aggregation to calculate the revenue change compared to the same month from the previous year for each state.