/*
PayPal SQL Interview Question

The table contains sales data for different individuals across various categories (Electronics, Clothing, Grocery) for specific months.
Write a sql query to create a pivot table that shows the month-wise total sales for each category
*/

-- Schema Setup
CREATE TABLE sales_data (
    name VARCHAR(20),
    category VARCHAR(20),
    month DATE,
    sales INT
);

INSERT INTO sales_data (name, category, month, sales) VALUES 
('John', 'Electronics', '2023-01-01', 4500),
('Jane', 'Clothing', '2023-01-02', 7500),
('Sam', 'Grocery', '2023-01-03', 3000),
('Alice', 'Electronics', '2023-02-04', 9000),
('Bob', 'Clothing', '2023-02-05', 1200),
('Jane', 'Grocery', '2023-02-06', 5500),
('John', 'Clothing', '2023-03-07', 8500),
('Alice', 'Electronics', '2023-03-08', 4000),
('Sam', 'Grocery', '2023-03-09', 1000);

-- Solution
SELECT
    DATE_FORMAT(month, '%Y-%m') AS 'month',
    SUM(CASE WHEN category = 'Electronics' THEN sales ELSE NULL END) AS Electronics,
    SUM(CASE WHEN category = 'Clothing' THEN sales ELSE NULL END) AS Clothing,
    SUM(CASE WHEN category = 'Grocery' THEN sales ELSE NULL END) AS Grocery
FROM sales_data
GROUP BY 1
ORDER BY 1 DESC;