/*
Analyze Percentage Change in Game Sales on the Steam Store  

As a data analyst for a gaming company, your task is to analyze the sales trends for selected games on the Steam store.
Using sales data for four popular games — Valorant, Marvel Rivals, COD (Call of Duty), and PUBG PC, calculate the percentage change in monthly sales.  

Schema:
Table Name: Game_Sales
| Column Name      | Data Type   | Description                                    |  
|------------------|-------------|------------------------------------------------|  
| game_name        | VARCHAR(20) | Name of the game                               |  
| sales_month      | DATE        | Month of the sales record                      |  
| sales_units      | INT         | Total number of units sold in the given month  |  

Task: 

1. Use the appropriate function to fetch the previous month's sales for each game.  
2. Calculate the percentage change in sales between the current and previous month.  
3. Display the following columns:  
   - `game_name`  
   - `sales_month`  
   - `sales_units`  
   - `previous_sales` 
   - `percentage_change`
4. Handle scenarios where there is no previous month's data by displaying `NULL` for the percentage change.  

Expected Output:
| game_name      | sales_month | sales_units | previous_sales | percentage_change |  
|----------------|-------------|-------------|----------------|-------------------|  
| Valorant       | 2024-10-01  | 120000      | NULL           | NULL              |  
| Valorant       | 2024-11-01  | 135000      | 120000         | 12.50             |  
| Valorant       | 2024-12-01  | 145000      | 135000         | 7.41              |  
| Marvel Rivals  | 2024-10-01  | 80000       | NULL           | NULL              |  
| Marvel Rivals  | 2024-11-01  | 95000       | 80000          | 18.75             |  
| Marvel Rivals  | 2024-12-01  | 97000       | 95000          | 2.11              |  
| COD            | 2024-10-01  | 100000      | NULL           | NULL              |  
| COD            | 2024-11-01  | 120000      | 100000         | 20.00             |  
| COD            | 2024-12-01  | 110000      | 120000         | -8.33             |  
| PUBG PC        | 2024-10-01  | 150000      | NULL           | NULL              |  
| PUBG PC        | 2024-11-01  | 140000      | 150000         | -6.67             |  
| PUBG PC        | 2024-12-01  | 160000      | 140000         | 14.29             |  
*/

CREATE TABLE Game_Sales (  
 game_name VARCHAR(20),  
 sales_month DATE,  
 sales_units INT  
);  

INSERT INTO Game_Sales (game_name, sales_month, sales_units) VALUES  
('Valorant', '2024-10-01', 120000),  
('Valorant', '2024-11-01', 135000),  
('Valorant', '2024-12-01', 145000),  
('Marvel Rivals', '2024-10-01', 80000),  
('Marvel Rivals', '2024-11-01', 95000),  
('Marvel Rivals', '2024-12-01', 97000),  
('COD', '2024-10-01', 100000),  
('COD', '2024-11-01', 120000),  
('COD', '2024-12-01', 110000),  
('PUBG PC', '2024-10-01', 150000),  
('PUBG PC', '2024-11-01', 140000),  
('PUBG PC', '2024-12-01', 160000);  

SELECT  
    game_name,  
    sales_month,  
    sales_units,  
    LAG(sales_units) OVER (PARTITION BY game_name ORDER BY sales_month) AS previous_sales,  
    ROUND(((sales_units - LAG(sales_units) OVER (PARTITION BY game_name ORDER BY sales_month)) /  
           LAG(sales_units) OVER (PARTITION BY game_name ORDER BY sales_month)) * 100, 2) AS percentage_change
FROM
Game_Sales;