REDONEAdvancedQueryChallenge (1).sql

--New Magic Store Data V2

--Finding the priceof the cheapest and the most expensive product

SELECT MIN(Price) AS MinPrice, MAX(Price) AS MaxPrice FROM Product;

--Name of the product that has been order the most
SELECT p.Name, SUM(op.Quantity) AS TimesOrdered FROM OrderProduct op
JOIN Product p ON op.ProductID = p.ID
GROUP BY p.Name ORDER BY TimesOrdered DESC
LIMIT 1;

--Average price that has been ordered at least once
SELECT AVG(p.Price) AS AveragePrice FROM Product p
WHERE p.ID IN (SELECT DISTINCT op.ProductID FROM OrderProduct op);


--Total revenue of each product
SELECT p.Name, SUM(op.Quantity * p.Price) AS TotalRevenue FROM OrderProduct op
JOIN Product p ON op.ProductID = p.ID
GROUP BY p.Name;

--5 products that generated the most revenue
SELECT p.Name, SUM(op.Quantity * p.Price) AS TotalRevenue FROM OrderProduct op
JOIN Product p ON op.ProductID = p.ID
GROUP BY p.Name ORDER BY TotalRevenue DESC
LIMIT 5;

-- 10 clients generated the most revenue in 2021
SELECT c.ID, c.FirstName, c.LastName, SUM(op.Quantity * p.Price) AS TotalRevenue FROM OrderProduct op
JOIN `Order` o ON op.OrderID = o.ID
JOIN Product p ON op.ProductID = p.ID
JOIN `Client` c ON o.ClientID = c.ID
WHERE YEAR(o.OrderedOn) = 2021
GROUP BY c.ID ORDER BY TotalRevenue DESC
LIMIT 10;

--Total revenue generated by each client who has spent more than 1000
SELECT c.ID, c.FirstName, c.LastName, SUM(op.Quantity * p.Price) AS TotalRevenue FROM OrderProduct op
JOIN `Order` o ON op.OrderID = o.ID
JOIN Product p ON op.ProductID = p.ID
JOIN `Client` c ON o.ClientID = c.ID
GROUP BY c.ID
HAVING TotalRevenue > 1000
ORDER BY TotalRevenue DESC;