/*
Amazon SQL Interview Question

Find the best selling item for each month (no need to separate months by year) where the biggest total invoice was paid.
The best selling item is calculated using the formula (unitprice * quantity).
Output the month, the description of the item along with the amount paid.
*/

-- Schema Setup
CREATE TABLE online_retail (invoiceno VARCHAR(50),stockcode VARCHAR(50),description VARCHAR(255),quantity INT,invoicedate DATETIME,unitprice FLOAT,customerid FLOAT,country VARCHAR(100));

INSERT INTO online_retail (invoiceno, stockcode, description, quantity, invoicedate, unitprice, customerid, country) VALUES('536365', '85123A', 'WHITE HANGING HEART T-LIGHT HOLDER', 10, '2021-01-15 10:00:00', 2.55, 17850, 'United Kingdom'),('536366', '71053', 'WHITE METAL LANTERN', 5, '2021-02-10 12:00:00', 3.39, 13047, 'United Kingdom'),('536367', '84406B', 'CREAM CUPID HEARTS COAT HANGER', 8, '2021-03-05 15:00:00', 2.75, 17850, 'United Kingdom'),('536368', '22423', 'REGENCY CAKESTAND 3 TIER', 2, '2021-04-12 16:30:00', 12.75, 13047, 'United Kingdom'),('536369', '85123A', 'WHITE HANGING HEART T-LIGHT HOLDER', 15, '2021-05-18 11:00:00', 2.55, 13047, 'United Kingdom'),('536370', '21730', 'GLASS STAR FROSTED T-LIGHT HOLDER', 12, '2021-06-25 14:00:00', 4.25, 17850, 'United Kingdom');

-- Solution
WITH month_sales AS
(
 SELECT
     MONTH(invoicedate) AS month,
     description,
     ROUND(SUM(unitprice * quantity),1) AS total_sales
 FROM online_retail
 GROUP BY month,description
)
,ranked_monthly_sales AS
(
 SELECT
    month,
    description,
    total_sales,
    DENSE_RANK() OVER(PARTITION BY month ORDER BY total_sales DESC) AS rnk
 FROM month_sales
)
    
SELECT
    month,
    description,
    total_sales
FROM ranked_monthly_sales
WHERE rnk = 1;