/*
You are working as a data analyst for an e-commerce platform. The company is interested in analyzing product sales across different categories.
To gain insights, you have been asked to use SQL ranking functions to evaluate the performance of products based on their total sales.  

Schema:   
| Column Name    | Data Type     | Description                               |  
|----------------|---------------|-------------------------------------------|  
| category       | VARCHAR(50)   | The category to which the product belongs |  
| product_name   | VARCHAR(100)  | Name of the product                       |  
| total_sales    | INT           | Total sales for the product               |  

Task:  
1. Use the ranking functions to analyze product sales within each category:  
   - Rank products by total sales, allowing gaps for ties.  
   - Rank products sequentially, even for ties.  
   - Assign a unique number to each product, regardless of ties.  

2. Display these columns:  
   - category  
   - product_name
   - total_sales  
   - rnk, dense_rnk, and rn.  

3. Identify the top 3 products in each category using RANK().  
4. Compare the impact of ties on each ranking method.

Expected Output:  
| category         | product_name      | total_sales | rnk | dense_rnk | rn |
|------------------|-------------------|-------------|-----|-----------|----|
| Electronics      | Smartphone X      | 5000        | 1   | 1         | 1  |
| Electronics      | Laptop Y          | 4500        | 2   | 2         | 2  |
| Electronics      | Tablet Z          | 4500        | 2   | 2         | 3  |
| Fashion          | Sneakers A        | 2000        | 1   | 1         | 1  |
| Fashion          | Handbag B         | 1800        | 2   | 2         | 2  |
| Fashion          | Jacket C          | 1700        | 3   | 3         | 3  |
| Home Appliances  | Vacuum Cleaner E  | 2500        | 1   | 1         | 1  |
| Home Appliances  | Air Purifier F    | 2300        | 2   | 2         | 2  |
| Home Appliances  | Blender G         | 2300        | 2   | 2         | 3  |
*/
    
CREATE TABLE product_sales (
    category VARCHAR(50),
    product_name VARCHAR(100),
    total_sales INT
);

INSERT INTO product_sales (category, product_name, total_sales) VALUES  
('Electronics', 'Smartphone X', 5000),  
('Electronics', 'Laptop Y', 4500),  
('Electronics', 'Tablet Z', 4500),  
('Electronics', 'Smartwatch W', 3000),  
('Fashion', 'Sneakers A', 2000),  
('Fashion', 'Handbag B', 1800),  
('Fashion', 'Jacket C', 1700),  
('Fashion', 'Scarf D', 1600),  
('Home Appliances', 'Vacuum Cleaner E', 2500),  
('Home Appliances', 'Air Purifier F', 2300),  
('Home Appliances', 'Blender G', 2300),  
('Home Appliances', 'Microwave H', 2200);  

WITH ranked_sales AS
(SELECT
    category,
    product_name,
    total_sales,
    RANK() OVER(PARTITION BY category ORDER BY total_sales DESC) AS rnk,
    DENSE_RANK() OVER(PARTITION BY category ORDER BY total_sales DESC) AS dense_rnk,
    ROW_NUMBER() OVER(PARTITION BY category ORDER BY total_sales DESC) AS rn
FROM product_sales)
    
SELECT
    category,
    product_name,
    total_sales,
    rnk,
    dense_rnk,
    rn
FROM ranked_sales
WHERE rnk <= 3;