Data Analytics Projects - SQL, Excel, Tableau, Python & Power BI Interview Resources
SQL Project Series #3
E-Commerce Sales Analysis – Intermediate SQL Business Questions
Let's solve more real-world business problems using SQL.
Business Questions
11. Find Repeat Customers
SELECT
customer_id,
COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;
12. Find Customers Who Never Placed an Order
SELECT
c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
13. Find Inactive Customers (No Orders in the Last 90 Days)
SELECT
c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '90 days'
OR MAX(o.order_date) IS NULL;
14. Find the Best-Selling Product Category
SELECT
p.category,
SUM(oi.quantity) AS units_sold
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY units_sold DESC
LIMIT 1;
15. Find the Highest Revenue Product
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY revenue DESC
LIMIT 1;
16. Find the Lowest Revenue Product
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY revenue
LIMIT 1;
17. Calculate Average Products per Order
SELECT
ROUND(AVG(product_count), 2) AS avg_products_per_order
FROM (
SELECT
order_id,
SUM(quantity) AS product_count
FROM order_items
GROUP BY order_id
) t;
18. Find Orders Worth More Than 10,000
SELECT
order_id,
SUM(quantity * unit_price) AS order_value
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 10000;
19. Find Customers with the Highest Average Order Value
SELECT
customer_id,
ROUND(AVG(order_value), 2) AS avg_order_value
FROM (
SELECT
o.customer_id,
o.order_id,
SUM(oi.quantity * oi.unit_price) AS order_value
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY o.customer_id, o.order_id
) t
GROUP BY customer_id
ORDER BY avg_order_value DESC;
20. Find the Top 3 Cities by Revenue
SELECT
c.city,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY c.city
ORDER BY revenue DESC
LIMIT 3;
SQL Concepts Practiced
• LEFT JOIN
• HAVING
• Aggregate Functions
• Nested Queries
• GROUP BY
• Business KPI Analysis
• Customer Segmentation
• Revenue Analysis
💡 Double Tap ❤️ For More
467 ·