Monday, June 23, 2025

Online Retail Store Project on PostgreSQL Queries


📊 Online Retail Store Database Queries

🔢 🗂️ Assumed Schema

Customers(customer_id, name, email, City, join_date)

 Products(product_id, Product_name, category, price

Orders(order_id, customer_id, order_date)

Order_Items(order_id, product_id, quantity, Total_price

Payments(payment_id, order_id, payment_date, payment_method, amount)  

Shipments(Shipment_id, Order_id, Shipment_date, Delivery_date, Carrier, status)

🟢 Beginner Level Queries (1–7)

1️⃣✅ Show all products with name, price, and Category ?

🔰View the current product listings and their availability.

SELECT Product_name
, price
, Category
FROM Products;

2️⃣🧑 List all customers with email and join date ?

🔰Useful for sending welcome offers or newsletters.

SELECT Name
, Email
, Signup_date
FROM Customers;

3️⃣🛒 Retrieve all orders made by customers with End of Month & Year ?

🔰Tracks when and what customers ordered.

SELECT order_id
, customer_id
, order_date
FROM Orders
ORDER BY Order_Date DESC;

4️⃣📦 Get all Low-of-stock products price ?

🔰Identifies products that have low price less than 50.

SELECT Product_name
FROM Products
WHERE Price <= 50;

5️⃣🔍 Search products by category 'Electronics' ?

🔰Filters products for browsing in a specific category.

SELECT *
FROM Products
WHERE category = 'Electronics';

6️⃣💸 Find all payments made using 'Credit Card' ?

🔰Understand how often each payment method is used.

SELECT *
FROM Payments
WHERE payment_method = 'Credit Card';

7️⃣📝 Show all Details for a specific Order ?**

🔰Customer Query for a given Order (e.g., Order_Id = 250)

SELECT *
FROM shipments
WHERE Order_Id = 250;

🟡 Moderate Level Queries (8–14)

8️⃣🔎 Top 10 Best-Selling Products ?

🔰Finds most sold products by quantity

SELECT Products.Product_name
, SUM(Order_items.quantity) AS total_sold
FROM Order_items
JOIN Products ON Order_items.product_id = Products.product_id
GROUP BY Products.Product_name
ORDER BY total_sold DESC
LIMIT 10;

9️⃣👥 Top 5 customers by number of orders ?

🔰Identifies the most frequent buyers

SELECT C.name
, COUNT(O.order_id) AS total_orders
FROM Customers C
JOIN Orders O ON C.customer_id = O.customer_id
GROUP BY C.name
ORDER BY total_orders DESC
LIMIT 5;

🔟💰 Total revenue by month ?

🔰Analyzes sales trends over time

SELECT DATE_TRUNC('month', order_date) AS month
, SUM(OI.Total_Price) AS monthly_revenue
FROM Orders O
JOIN Order_Items OI ON O.Order_ID = OI.Order_ID
GROUP BY month
ORDER BY month;

1️⃣1️⃣🧾Most sold products (by quantity) ?

🔰Helps decide what products to keep or promote.

SELECT P.Product_name
, SUM(OI.quantity) AS total_sold
FROM Order_Items OI
JOIN Products P ON OI.product_id = P.product_id
GROUP BY P.Product_name
ORDER BY total_sold DESC
LIMIT 10;

1️⃣2️⃣ 🔁 Repeat customers ?

🔰Target these users with loyalty campaigns

SELECT Customer_id,
COUNT() AS Order_Count
FROM Orders
GROUP BY Customer_id
HAVING COUNT(
) > 1
ORDER BY Order_Count DESC;

1️⃣3️⃣ ⏳ Repeat customers Having the Most Order Counts ?

🔰Target this users with loyalty campaigns

WITH TopCustomer AS (
SELECT Customer_id,
COUNT(*) AS Order_Count
FROM Orders
GROUP BY Customer_id
ORDER BY Order_Count DESC
LIMIT 1
) SELECT C.*
FROM Customers C
JOIN TopCustomer T ON C.Customer_id = T.Customer_id;

1️⃣4️⃣⏳ Orders placed in the last 30 days ?

🔰Get recent activity or new trends

SELECT *
FROM Orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 Days';

🔴 Advanced Level Queries (15–20)

1️⃣5️⃣📬 Customers with no orders (abandoned signups) ?

🔰Reach out with personalized emails to re-engage.

SELECT C.customer_id, C.name
FROM Customers C
LEFT JOIN Orders O ON C.customer_id = O.customer_id
WHERE O.order_id IS NULL;

1️⃣6️⃣📊 Profit by product (requires cost_price in Products) ?

🔰Determines which products generate the most profit.

SELECT P.Product_Name
, SUM((OI.Total_price - P.price) * OI.quantity) AS total_profit
FROM Order_Items OI
JOIN Products P ON OI.product_id = P.product_id
GROUP BY P.Product_Name
ORDER BY total_profit DESC;

1️⃣7️⃣ 🛍️ Frequently bought together products ?

🔰Used to suggest combos or bundles.

SELECT oi1.product_id AS product_1
, oi2.product_id AS product_2
, COUNT(*) AS frequency
FROM Order_Items oi1
JOIN Order_Items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id
GROUP BY product_1, product_2
ORDER BY frequency DESC
LIMIT 10;

1️⃣8️⃣ 🕒 Average delivery time (requires delivery_date) ?

🔰Monitors logistics performance.

SELECT ROUND(AVG(S.delivery_date - O.order_date),3) AS avg_delivery_days
FROM Orders O
JOIN Shipments S ON O.Order_ID = S.Order_ID
WHERE delivery_date IS NOT NULL;

1️⃣9️⃣ 🔢 Product category contribution to revenue ?

🔰Identify top-performing categories.

SELECT P.category, SUM(OI.Quantity * OI.Total_price) AS revenue
FROM Order_Items OI
JOIN Products P ON OI.product_id = P.product_id
GROUP BY P.category
ORDER BY revenue DESC;

2️⃣0️⃣ 🔗 Most active days (peak order days)

🔰Helps plan marketing and promotions around high-traffic days.

SELECT Order_date, COUNT(*) AS Total_Orders
FROM Orders
GROUP BY Order_date
ORDER BY Total_Orders DESC
LIMIT 10;

✅ Online Retail Store File's Embedded

🔰Online Retail Store related csv file's

customers.csv

order_items.csv

orders.csv

payments.csv

products.csv

shipments.csv

No comments:

Retail Analytics Dashboard on Zepto Dataset using PostgreSQL, Excel, and Power BI

📁 Retail Analytics Dashboard on Zepto Dataset using PostgreSQL, Excel, and Power BI 📝 Project Description: 🃏The project focuses on analyz...