------------------------------------------------------
-- 5. Answering Questions
------------------------------------------------------

-- Q1: Sales Manager with the Highest Sales in 2023
SELECT TOP 1 sm.Person, SUM(o.Sales) AS total_sales
FROM Orders o
JOIN Sales_Manager sm ON sm.Region = o.Region
WHERE YEAR(o.Order_Date) = 2023
GROUP BY sm.Person
ORDER BY total_sales DESC;

-- **Expected Answer:**
-- | Person       | Total Sales |
-- |-------------|------------|
-- | Emily Davis | 1499.99    |

------------------------------------------------------

-- Q2: Top 5 Most Returned Products by Category
SELECT TOP 5 o.Category, o.Product_Name, COUNT(*) AS return_count
FROM Orders o
JOIN Returns r ON r.Order_ID = o.Order_ID
WHERE r.Returned = 1
GROUP BY o.Category, o.Product_Name
ORDER BY return_count DESC;

-- **Expected Answer:**
-- | Category     | Product Name         | Return Count |
-- |-------------|----------------------|--------------|
-- | Technology  | Smartphone X         | 1            |
-- | Technology  | Business Laptop Pro  | 1            |

------------------------------------------------------

-- Q3: Profit Proportion by State (2023)
WITH TotalProfit AS (
    SELECT SUM(profit) AS total_profit 
    FROM Orders
    WHERE YEAR(Order_Date) = 2023
)
SELECT 
    o.State,
    ROUND((SUM(o.Profit) / tp.total_profit) * 100.0, 2) AS profit_pct,
    SUM(o.Profit) AS state_profit,
    tp.total_profit
FROM Orders o
CROSS JOIN TotalProfit tp
WHERE YEAR(o.Order_Date) = 2023
GROUP BY o.State, tp.total_profit
ORDER BY profit_pct DESC;

-- **Expected Answer:**
-- | State  | Profit % | State Profit | Total Profit |
-- |--------|----------|--------------|--------------|
-- | TX     | 66.67%   | 300.00       | 450.00       |
-- | NY     | 33.33%   | 150.00       | 450.00       |

------------------------------------------------------

-- Q4: Markets & Regions with Profit Margin > 4%
SELECT 
    o.Market, 
    o.Region, 
    ROUND((SUM(o.Profit) * 100.0) / SUM(o.Sales), 2) AS profit_margin
FROM Orders o
GROUP BY o.Market, o.Region
HAVING ROUND((SUM(o.Profit) * 100.0) / SUM(o.Sales), 2) > 4
ORDER BY profit_margin DESC;

-- **Expected Answer:**
-- | Market     | Region  | Profit Margin |
-- |-----------|--------|---------------|
-- | US Market | South  | 20.00%        |
-- | US Market | East   | 16.67%        |

------------------------------------------------------

-- Q5: Average Days Between Orders per Sales Manager
SELECT 
    order_sequence.Person, 
    AVG(date_diff) AS avg_days_between_orders
FROM (
    SELECT 
        sm.Person,
        DATEDIFF(DAY, o.Order_Date, 
                LEAD(o.Order_Date) OVER (
                    PARTITION BY sm.Person 
                    ORDER BY o.Order_Date
                )) AS date_diff
    FROM Orders o
    JOIN Sales_Manager sm ON o.Region = sm.Region
) AS order_sequence
WHERE date_diff IS NOT NULL
GROUP BY order_sequence.Person
ORDER BY avg_days_between_orders ASC;

-- **Expected Answer:**
-- | Person       | Avg Days Between Orders |
-- |-------------|------------------------|
-- | Emily Davis | 10                      |
-- | John Smith  | 6                       |

------------------------------------------------------