3.Useful Queries.sql

-- 1. Get All Orders by a User : user maybe(customer or vendor)
-- For analysis like promotion or bot accounts etc.
-- Table Req : User table , Order table ,Vendor table

--On behalf of users
SELECT o.OrderId,o.orderDate,o.totalAmount,u.fullName
FROM Orders o JOIN Users u
ON o.userId = u.userId 
WHERE o.userID = 2;


--on behalf of vendors 
SELECT o.OrderId,o.orderDate,o.totalAmount,v.vendorName
FROM Orders o JOIN Vendors v
ON o.userId = v.vendorId 
WHERE o.userID = 2;

-- on behalf of users and to which vendors??
SELECT o.orderId,u.fullName,o.orderDate,o.totalAmount,v.vendorName
FROM Orders o Join Users u
ON o.userId = u.userId
JOIN Vendors v
ON o.vendorId = v.vendorId
WHERE o.userID = 12;




--2 Order Summary with Items (Order ,OrderItems, Products)
Select o.orderId , p.name ,o.orderDate ,oi.quantity , oi.Price 
From Orders o
JOIN OrderItem oi ON o.orderId = oi.orderId
JOIN Products p ON p.productID = oi.productID;



-- 3 TOTAL REVENUE GENERATED BY EACH VENDOR
SELECT v.vendorName as 'Vendor Name' , SUM(o.totalAmount) as 'Total Revenue'
FROM Orders o 
Join Vendors v on o.vendorId = v.vendorId
GROUP BY v.vendorName
ORDER BY 'Total Revenue' DESC;


--4 TOP 5 BESTSELLING PRODCUTS
SELECT TOP 5 p.name , SUM(oi.quantity) as TotalSold
FROM OrderItem oi 
JOIN Products p ON oi.productID = p.productId
group by p.name
order by TotalSold desc;


--5 Average Product Ratings
SELECT p.name , AVG(r.rating) as 'Average Product Rating'
FROM Reviews r 
JOIN Products p on r.productID = p.productId
group by p.name
order by 'Average Product Rating' DESC;


--6 Delivery Status of Particular Order
SELECT ds.orderId,ds.updatedAt, s.statusName
FROM DeliveryStatus ds
JOIN Status s on ds.statusId = s.statusId
ORDER BY s.statusName ASC;


--7 Daily Order Count 
SELECT CAST(orderDate AS date) as 'Order Date', COUNT(*) as 'Total Count' 
from Orders
group by CAST(orderDate AS date);


--8 TOP CUSTOMERS BY ORDER VALUE
SELECT 
    u.userId AS "USER ID",
    u.fullName AS Customer,
    SUM(o.totalAmount) AS "Total Amount"
FROM Users u 
JOIN Orders o ON o.userId = u.userId
GROUP BY u.userId, u.fullName
ORDER BY "Total Amount" DESC;

-- 9 LOW STOCK Product per vednor
select v.vendorName , SUM(i.quantityAvailable) as "Remaining Items"
from Inventory i 
join Vendors v on i.vendorId = v.vendorId
group by v.vendorName
order by SUM(i.quantityAvailable) desc;


select v.vendorName, p.name,i.quantityAvailable
from Inventory i 
join Vendors v on i.vendorId = v.vendorId
join Products p on p.productId = i.productId
where i.quantityAvailable < 20
order by i.quantityAvailable;



-- 10 SEARCH PRODUCT BY KEYWORDS(Flexible Query)
Select * FROM Products
WHERE name LIKE '%a%';