e_commerce_project_solved_queries.sql

-- How many customers are there in the database?
SELECT COUNT(*) AS TotalCustomers
FROM Customers;

-- What is the total order amount for each customer?
SELECT c.FirstName, c.LastName, SUM(o.Total_order_amount) AS TotalOrderAmount
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.FirstName, c.LastName;

-- What is the average sale price for each product category?
SELECT c.CategoryName, AVG(p.Sale_Price) AS AvgSalePrice
FROM Category c
INNER JOIN Products p ON c.CategoryID = p.Category_ID
GROUP BY c.CategoryName;

-- What is the total number of orders placed by each customer in the year 2020?
SELECT c.FirstName, c.LastName, COUNT(*) AS TotalOrders
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE YEAR(o.OrderDate) = 2020
GROUP BY c.FirstName, c.LastName;

-- What is the total revenue generated by each product category in the year 2021?
SELECT c.CategoryName, SUM(p.Sale_Price * od.Quantity) AS TotalRevenue
FROM Category c
INNER JOIN Products p ON c.CategoryID = p.Category_ID
INNER JOIN OrderDetails od ON p.ProductID = od.ProductID
INNER JOIN Orders o ON od.OrderID = o.OrderID
WHERE YEAR(o.OrderDate) = 2021
GROUP BY c.CategoryName;

-- What is the total number of orders placed by each customer in the month of December 2020?
SELECT c.FirstName, c.LastName, COUNT(*) AS TotalOrders
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE YEAR(o.OrderDate) = 2020 AND MONTH(o.OrderDate) = 12
GROUP BY c.FirstName, c.LastName;

-- Retrieve the product name, sale price, and market price for all products that have a sale price greater than the average sale price of all products in the same category.
SELECT Product, Sale_Price, Market_Price
FROM Products
WHERE Sale_Price > (SELECT AVG(Sale_Price) FROM Products WHERE Category_ID = Products.Category_ID);

-- Retrieve the total revenue generated by each supplier in the year 2020 and 2021, sorted in descending order by total revenue, where the total revenue is greater than 100000.
SELECT CompanyName, YEAR(OrderDate) AS OrderYear, SUM(Sale_Price * Quantity) AS TotalRevenue
FROM Suppliers
INNER JOIN OrderDetails ON Suppliers.SupplierID = OrderDetails.SupplierID
INNER JOIN Products ON orderdetails.ProductID = Products.ProductID
INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE YEAR(OrderDate) IN (2020, 2021)
GROUP BY CompanyName, YEAR(OrderDate)
HAVING TotalRevenue > 100000
ORDER BY TotalRevenue DESC;

-- Retrieve the product name, sale price, and market price for all products that have a sale price greater than the average sale price of all products and have been ordered at least 5 times in the year 2021, and sort the results in descending order by sale price.
SELECT Product, Sale_Price, Market_Price
FROM Products
INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE Sale_Price > (SELECT AVG(Sale_Price) FROM Products)
AND YEAR(OrderDate) = 2021
GROUP BY Product, Sale_Price, Market_Price
HAVING COUNT(*) >= 5
ORDER BY Sale_Price DESC;

-- Retrieve the product name, sale price, and market price for all products that have a sale price greater than the average sale price of all products and have been ordered at least 5 times in the year 2021, and have not been ordered by any customer who has placed an order in the year 2022.
SELECT Product, Sale_Price, Market_Price
FROM Products
INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE Sale_Price > (SELECT AVG(Sale_Price) FROM Products)
AND YEAR(OrderDate) = 2021
AND NOT EXISTS (
    SELECT *
    FROM Orders
    INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
    WHERE Orders.CustomerID IN (
        SELECT CustomerID
        FROM Orders
        WHERE YEAR(OrderDate) = 2022
    ) AND OrderDetails.ProductID = Products.ProductID
)
GROUP BY Product, Sale_Price, Market_Price
HAVING COUNT(*) >= 5;

-- Find the total order amount for each customer, along with the rank of their order amount within their respective city?
SELECT customers.CustomerID, customers.City, Total_order_amount,
       RANK() OVER (PARTITION BY City ORDER BY Total_order_amount DESC) AS rank_
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

-- Find the average time between orders for each customer?
SELECT CustomerID, AVG(DATEDIFF(OrderDate, prev_order_date)) AS avg_days_between_orders
FROM (
  SELECT CustomerID, OrderDate,
         LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS prev_order_date
  FROM Orders
) AS order_dates
WHERE prev_order_date IS NOT NULL
GROUP BY CustomerID;

-- Find the running total of sales by month and year?
SELECT DATE_FORMAT(OrderDate, '%Y-%m') AS month_year,
       SUM(Total_order_amount) OVER (ORDER BY OrderDate) AS running_total
FROM Orders;

-- Find the top 3 categories by average sale price, along with the rank of their average sale price?
SELECT CategoryName, AVG(Sale_Price) AS avg_sale_price,
       RANK() OVER (ORDER BY AVG(Sale_Price) DESC) AS rank_
FROM Products
JOIN Category ON Products.Category_ID = Category.CategoryID
GROUP BY CategoryName
ORDER BY avg_sale_price DESC
LIMIT 3;

-- Write a query to print the cumulative sum of quantity for every order placed by each customer on the basis of their increasing OrderdetailsID.
-- Print CustomerID, OrderdetailsID, Cumulative Quantity.
-- Sort the order in ascending order of customerid.
select o.customerid, od.orderdetailid, sum(od.quantity) over(partition by o.customerid order by od.orderdetailid) from orders o 
join orderdetails od on od.orderid = o.orderid 
order by 1

-- Find out that one category which has the least number brands under it, display all the products in that category.
-- Output should contain list of products sorted in alphabetical order.
-- Note: Least brand means lowest number of brand.
with cte as ( select *, count(p.brand) over(partition by c.categoryname) as cc, dense_rank() over(partition by c.categoryname) as rnk from category c 
join products p on c.categoryid = p.category_id ) 
select product from cte where rnk = 1