Project Title: Data Analytics with SQL
Subtitle: Business Insights from Retail Sales Data
Presented by: Tanishq Dosaya
Tools Used: MySQL
Date: 19 July 2025
Introduction
This SQL project focuses on analyzing a retail sales database.
Objective: Extract meaningful business insights using SQL queries.
Dataset includes: Customers, Orders, Products, Stores, Stocks, and
Categories.
Business Goals
Understand overall revenue generation.
Identify top-selling products and stores.
Analyze customer behavior and loyalty.
Optimize inventory and pricing strategy.
Query 1 - Total Sales Revenue
Purpose: Calculates overall revenue.
Logic: Multiplies product price by quantity and adjusts for discounts.
Business Value: Helps evaluate total income from sales.
Query:
SUM((list_price * quantity) * (1 - discount)) AS total_revenue FROM
order_items;
OUTPUT:
Query 2 - Top 5 Selling Products
Purpose: Identifies best-selling products.
Logic: Sums quantity sold per product.
Business Value: Useful for inventory planning and promotion.
Query:
SELECT OUTPUT:
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 5;
Query 3 - Monthly Sales Trend
Purpose: Shows how revenue varies month by month.
Logic: Groups order dates by month and sums revenues.
Business Value: Reveals seasonal trends and business cycles.
Query: OUTPUT:
SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS month,
SUM((oi.list_price * oi.quantity) * (1 - oi.discount))
AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY month
ORDER BY month;
Query 4 - Highest Spending Customer
Purpose: Finds the top customer by total purchases.
Logic: Aggregates total amount spent per customer.
Business Value: Important for customer relationship management.
Query:
SELECT
c.first_name,
c.last_name,
SUM((oi.list_price * oi.quantity) * (1 - oi.discount)) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id OUTPUT:
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 1;
Query 5 - Best Performing Store
Purpose: Shows which store generates the most sales.
Logic: Aggregates revenue by store.
Business Value: Aids in performance comparisons and strategic planning.
Query:
SELECT
s.store_id,
SUM((oi.list_price * oi.quantity) * (1 - oi.discount)) AS
revenue OUTPUT:
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN stores s ON o.store_id = s.store_id
GROUP BY s.store_id
ORDER BY revenue DESC
LIMIT 1;
Query 6 - Inventory Summary
Purpose: Shows current stock level per product.
Logic: Joins products with stocks and sums quantities.
Business Value: Essential for stock control and restocking.
Query:
SELECT
OUTPUT:
p.product_name,
SUM(s.quantity) AS total_stock
FROM products p
JOIN stocks s ON p.product_id = s.product_id
GROUP BY p.product_name;
Query 7 - Category-wise Sales
Purpose: Analyzes how different product categories are performing.
Logic: Sums quantity sold by category.
Business Value: Useful for product line planning.
Query:
SELECT OUTPUT:
cat.category_name,
SUM(oi.quantity) AS items_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY cat.category_name;
Query 8 - Brand-wise Product Count
Purpose: Counts how many products belong to each brand.
Logic: Group by brand and count rows in products.
Business Value: Understands brand spread in inventory.
Query:
OUTPUT:
SELECT
b.brand_name, COUNT(*) AS product_count
FROM products p
JOIN brands b ON p.brand_id = b.brand_id
GROUP BY b.brand_name;
Query 9 - Total Customers per City
Purpose: Measures geographic distribution of customers.
Logic: Groups customer data by city.
Business Value: Useful for regional marketing strategies.
Query:
OUTPUT:
SELECT
city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
ORDER BY customer_count DESC;
Query 10 - Repeat Customers
Purpose: Finds customers with more than one order.
Logic: Groups by customer_id and filters with HAVING.
Business Value: Measures customer loyalty and retention.
Query: OUTPUT:
SELECT
customer_id, COUNT(*) AS orders_placed
FROM orders
GROUP BY customer_id
HAVING orders_placed > 1;
Challenges Faced
Dealing with SQL mode restrictions (e.g., ONLY_FULL_GROUP_BY).
Data consistency checks before aggregation.
Understanding relational joins across tables.
Learnings & Outcomes
Improved skills in writing advanced SQL queries.
Gained business-oriented thinking from raw data.
Able to interpret insights useful for decision-making.
Thank You
Questions & Feedback Welcome!
Email:
[email protected]
