1. Retrieve all product details from the Products table.

SELECT * FROM Products;


2. Find customers who made purchases in June 2021.

SELECT c.Customer_ID, c.Customer_Name, s.Date
FROM Customers c
JOIN Sales_Transactions s ON c.Customer_ID = s.Customer_ID
WHERE s.Date BETWEEN '2021-06-01' AND '2021-06-30';


3. List all products with stock quantity less than 100.

SELECT p.SKU_Code, p.Design_No, p.Category, i.Stock_Qty
FROM Products p
JOIN Inventory i ON p.SKU_Code = i.SKU_Code
WHERE i.Stock_Qty < 100;


4. Show the total sales amount for each customer.

SELECT c.Customer_ID, c.Customer_Name, SUM(s.Gross_Amount) AS Total_Sales
FROM Customers c
JOIN Sales_Transactions s ON c.Customer_ID = s.Customer_ID
GROUP BY c.Customer_ID, c.Customer_Name;


5. Display products with their MRP prices sorted by highest price.

SELECT p.SKU_Code, p.Design_No, pr.Final_MRP
FROM Products p
JOIN Pricing pr ON p.SKU_Code = pr.SKU_Code
ORDER BY pr.Final_MRP DESC;


6. Find products that have never been sold.

SELECT p.SKU_Code, p.Design_No, p.Category
FROM Products p
LEFT JOIN Sales_Transactions s ON p.SKU_Code = s.SKU_Code
WHERE s.SKU_Code IS NULL;


7. Calculate the average sale amount per transaction by month.

SELECT 
  EXTRACT(MONTH FROM Date) AS Month,
  AVG(Gross_Amount) AS Average_Sale_Amount
FROM Sales_Transactions
GROUP BY EXTRACT(MONTH FROM Date)
ORDER BY Month;


8. Identify customers who purchased more than 2 items in a single transaction.

SELECT c.Customer_ID, c.Customer_Name, s.Date, s.Quantity
FROM Customers c
JOIN Sales_Transactions s ON c.Customer_ID = s.Customer_ID
WHERE s.Quantity > 2;


9. Show warehouse operations that involved more than 50 items.

SELECT Operation_ID, Operation_Type, Quantity
FROM Warehouse_Ops
WHERE Quantity > 50;


10. Find products that have different prices across platforms.

SELECT SKU_Code
FROM Pricing
WHERE Amazon_MRP != Flipkart_MRP 
   OR Amazon_MRP != Myntra_MRP
   OR Flipkart_MRP != Myntra_MRP;


11. Calculate inventory turnover rate for each product.

SELECT 
  i.SKU_Code, 
  p.Design_No,
  (SUM(s.Quantity) / i.Stock_Qty) AS Turnover_Rate
FROM Inventory i
JOIN Products p ON i.SKU_Code = p.SKU_Code
LEFT JOIN Sales_Transactions s ON i.SKU_Code = s.SKU_Code
GROUP BY i.SKU_Code, p.Design_No, i.Stock_Qty;


12. Identify the most popular product category by sales volume.

SELECT p.Category, SUM(s.Quantity) AS Total_Quantity_Sold
FROM Products p
JOIN Sales_Transactions s ON p.SKU_Code = s.SKU_Code
GROUP BY p.Category
ORDER BY Total_Quantity_Sold DESC
LIMIT 1;


13. Find customers who purchased from multiple categories.

SELECT 
  c.Customer_ID, 
  c.Customer_Name,
  COUNT(DISTINCT p.Category) AS Distinct_Categories
FROM Customers c
JOIN Sales_Transactions s ON c.Customer_ID = s.Customer_ID
JOIN Products p ON s.SKU_Code = p.SKU_Code
GROUP BY c.Customer_ID, c.Customer_Name
HAVING COUNT(DISTINCT p.Category) > 1;


14. Calculate profit margin for each product (assuming TP is cost price).

SELECT 
  p.SKU_Code, 
  p.Design_No,
  ((pr.Final_MRP - pr.TP_Price) / pr.Final_MRP) * 100 AS Profit_Margin
FROM Products p
JOIN Pricing pr ON p.SKU_Code = pr.SKU_Code;


15. Create a monthly sales report with comparisons to previous month.

WITH MonthlySales AS (
  SELECT 
    EXTRACT(MONTH FROM Date) AS Month,
    SUM(Gross_Amount) AS Current_Sales
  FROM Sales_Transactions
  GROUP BY EXTRACT(MONTH FROM Date)
  
SELECT 
  m1.Month,
  m1.Current_Sales,
  m2.Current_Sales AS Previous_Month_Sales,
  ((m1.Current_Sales - m2.Current_Sales) / m2.Current_Sales) * 100 AS Growth_Percentage
FROM MonthlySales m1
LEFT JOIN MonthlySales m2 ON m1.Month = m2.Month + 1
ORDER BY m1.Month;