Customer Sales Insights

Project Overview

This project gives sales insights into a hardware manufacturing company from 2017 to 2020 with ten regional offices in three zones across India generating an overall revenue of close to 100 crores from 20 lakhs sales in 4 years.

Raw Data Source - db_dump.sql

Steps

• import the dataset to my SQL workbench
• Perform QA testing and Data Profiling
• Import this SQL database in Power BI
• Use power Queries to build KPIs and Create an insightful interactive dashboard interactive Dashboard
• Data validation using My SQL queries SQL Queries
• Refer to Schema Schema

Tools

• MY SQL - For QA testing, Data profiling and Data Validation
• Power BI - For Data Transformation, build KPIs and create an interactive dashboard,

My SQL Queries and Results

Q1: Show total revenue in years 2017-2020

SELECT d.year, SUM(t.sales_amount) AS Total_Revenue
FROM transactions AS t
INNER JOIN date AS d
ON t.order_date= d.date
GROUP BY d.year;

Q2: Show revenue generated from each zone in 2020

SELECT d.year, m.zone, SUM(t.sales_amount) AS Total_Revenue
FROM transactions AS t
INNER JOIN markets AS m
ON t.market_code=m.markets_code
INNER JOIN date AS d
ON t.order_date=d.date
WHERE d.year=2020
GROUP BY m.zone
ORDER BY SUM(t.sales_amount)DESC;

Q3: Show markets(City) present in diffrent zones

SELECT zone, markets_name AS City
FROM markets
GROUP BY zone , markets_name
ORDER BY zone DESC;

Q4: Show total number of customers from each market(city)

SELECT  m.markets_name AS market, COUNT(distinct t.customer_code) AS No_of_Customers
FROM transactions AS t
INNER JOIN markets AS m
ON t.market_code=m.markets_code
INNER JOIN customers AS c
ON t.customer_code= c.customer_code
GROUP BY m.markets_name
ORDER BY COUNT(distinct t.customer_code)DESC;

Q5: Show total revenue generated from each Market(city) in year 2020

SELECT d.year, m.markets_code, m.markets_name AS Market, SUM(sales_amount) AS Total_Revenue
FROM transactions AS t
INNER JOIN date AS d
ON t.order_date= d.date
INNER JOIN markets AS m
ON t.market_code= m.markets_code
WHERE d.year= 2020
GROUP BY m.markets_name,m.markets_code
ORDER BY SUM(sales_amount)DESC;

Q6: Show total number of transactions in Delhi NCR in year 2020 (Delhi NCR market code is Mark004)

SELECT d.year ,COUNT(*) 
FROM transactions AS t
INNER JOIN DATE AS d
ON t.order_date= d.date
WHERE market_code= "Mark004" AND d.year=2020;

Q7: Show total revenue from Delhi NCR in year 2020

SELECT d.year, SUM(sales_amount) AS revenue_Delhi_NCR
FROM transactions AS t
INNER JOIN date AS d
ON t.order_date = d.date
WHERE market_code= "Mark004" and d.year= 2020
GROUP BY d.year;

Q8: Which store generated highest revenue in Delhi NCR in year 2020

SELECT d.year,c.custmer_name, SUM(sales_amount) AS Total_Revenue
FROM transactions AS t
INNER JOIN date AS d
ON t.order_date=d.date
INNER JOIN customers as c
ON t.customer_code= c.customer_code
WHERE market_code= "Mark004" and d.year=2020
GROUP BY d.year, c.custmer_name
ORDER BY SUM(sales_amount)DESC;

Q9: Show bestselling product codes from all stores in Delhi NCR in year 2020

WITH bestsellers AS
(
SELECT distinct c.custmer_name, d.year, t.product_code, COUNT(t.product_code) AS Total_Sales,
ROW_NUMBER() OVER(PARTITION BY custmer_name ORDER BY COUNT(t.product_code) DESC) AS row_no
FROM transactions AS t
INNER JOIN customers AS c
ON t.customer_code=c.customer_code
INNER JOIN date AS d
ON t.order_date=d.date
WHERE market_code= "Mark004" and d.year=2020
GROUP BY t.customer_code, d.year, t.product_code
ORDER BY COUNT(t.product_code) DESC
)
SELECT * FROM bestsellers WHERE row_no = 1 ;

10: Which store generated highest revenue in Mumbai in year 2020

SELECT d.year,c.custmer_name, SUM(sales_amount) AS Total_revenues
FROM transactions AS t
INNER JOIN date AS d
ON t.order_date=d.date
INNER JOIN customers as c
ON t.customer_code= c.customer_code
WHERE market_code= "Mark002" and d.year=2020
GROUP BY d.year, c.custmer_name
ORDER BY SUM(sales_amount)DESC
LIMIT 5;

Q11: Show bestselling product codes from all stores in Mumbai in year 2020

WITH bestsellers AS
(
SELECT distinct c.custmer_name, d.year, t.product_code, COUNT(t.product_code) AS Total_sales,
ROW_NUMBER() OVER(PARTITION BY c.custmer_name ORDER BY COUNT(t.product_code) DESC) AS row_no
FROM transactions AS t
INNER JOIN customers AS c
ON t.customer_code=c.customer_code
INNER JOIN date AS d
ON t.order_date=d.date
WHERE market_code= "Mark004" and d.year=2020
GROUP BY t.customer_code, d.year, t.product_code
ORDER BY COUNT(t.product_code) DESC
)
SELECT * FROM bestsellers WHERE row_no = 1 ;