Adventureworks_EDA.sql

Use AdventureWorks2019
go



------Here we will be exploring our data to answer key business questions
------What are the most popular products among customers?




-----Q1 most common product among customers

select distinct pp.name, count(oh.customerID) as product_pur_count
from Production.product pp
join sales.SalesOrderDetail od
on pp.productID = od.ProductID
join sales.SalesOrderHeader oh
on od.SalesOrderID = oh.SalesOrderID
group by pp.name
order by 2 desc
 
---top 10 most common product among customers

select top 10  pp.name, count(oh.customerID) as product_pur_count
from Production.product pp
join sales.SalesOrderDetail od
on pp.productID = od.ProductID
join sales.SalesOrderHeader oh
on od.SalesOrderID = oh.SalesOrderID
group by pp.name
order by 2 desc
-----Q2 Which geographic regions generate the most sales?

select distinct oh.TerritoryID, ot.Name, count(oh.customerID) as sales_count, sum(oh.TotalDue) as Total_sales
from sales.SalesOrderDetail od
join sales.SalesOrderHeader oh
on od.SalesOrderID = oh.SalesOrderID
join sales.SalesTerritory ot
on oh.TerritoryID = ot.TerritoryID
group by oh.TerritoryID, ot.Name 
order by count(oh.CustomerID) desc

------Q3 How has sales volume changed overtime?

select distinct order_year, count(customerID) as purchase_count, sum(totaldue) as total_sales
from sales.SalesOrderHeader
group by Order_Year
order by 1 asc


----looking at the percentage change in sales over the years
select * from sales.SalesOrderHeader

select order_year, sum(totaldue) as total_sales,
			100 * round((sum(totaldue) - LAG(sum(totaldue)) OVER (ORDER BY order_year)) / LAG(sum(totaldue)) OVER (ORDER BY order_year),2) AS percentage_change
FROM sales.SalesOrderHeader
GROUP BY  Order_Year
ORDER BY  1 asc

----drilling down percentage change in sales to month and quarter
select order_month, order_quarter, sum(totaldue) as total_sales, 
				100 * round((sum(totaldue) - LAG(sum(totaldue)) OVER (ORDER BY  Order_quarter)) / LAG(sum(totaldue)) 
				OVER (ORDER BY Order_quarter),2)  as sales_percent
from sales.SalesOrderHeader
group by order_month, order_quarter
order by 2, 4 asc

------------Q4 Which customer segments generate the most revenue?
select distinct pp.persontype, sum(oh.totaldue) as rev_generated, (100* sum(oh.totaldue)/(select sum(totaldue) from sales.SalesOrderHeader)) as rev_percent
from person.person pp
join sales.customer sc
on pp.BusinessEntityID = sc.PersonID
join sales.SalesOrderHeader oh 
on oh.CustomerID = sc.CustomerID
group by pp.PersonType
order by 2 desc



----how effective are promotional campaigns in driving sales

select distinct pp.emailpromotion, count(sc.CustomerID) as sales_count, count(so.productID), sum(sh.totaldue) as total_revenue
from person.Person pp
join sales.customer sc
on sc.PersonID = pp.BusinessEntityID
join sales.SalesOrderHeader sh
on sh.customerID = sc.customerID
join sales.salesorderdetail so
on so.SalesOrderID = sh.SalesOrderID
---where pp.emailpromotion != 0
group by pp.emailpromotion
order by 1

---- rev generated by various promotional campaigns

select distinct (sspp.type) as type_,  count(so.productID) as sales_count, sum(sh.totaldue) as total_revenue
				,round(100 * sum(sh.totaldue)/(select sum(totaldue) from sales.salesorderheader),2) as sales_percent
from sales.SpecialOffer sspp
join sales.SpecialOfferProduct ssop
on sspp.specialofferID = ssop.specialofferID
join sales.SalesOrderDetail so
on ssop.ProductID = so.ProductID
join sales.SalesOrderHeader sh
on so.SalesOrderID = sh.SalesOrderID
join sales.customer sc
on sh.CustomerID = sc.CustomerID
join person.Person pp
on pp.BusinessEntityID = sc.CustomerID
group by sspp.Type
order by 2

select * from sales.SalesOrderHeader
----effectiveness of promotional campaigns

with promo_cte (type_, sales_count, tot_rev, rev_percent)
as
(
select distinct (sspp.type) as type_,  count(so.productID) as sales_count, sum(sh.totaldue) as total_revenue
				,round(100 * sum(sh.totaldue)/(select sum(totaldue) from sales.salesorderheader),2) as sales_percent
from sales.SpecialOffer sspp
join sales.SpecialOfferProduct ssop
on sspp.specialofferID = ssop.specialofferID
join sales.SalesOrderDetail so
on ssop.ProductID = so.ProductID
join sales.SalesOrderHeader sh
on so.SalesOrderID = sh.SalesOrderID
join sales.customer sc
on sh.CustomerID = sc.CustomerID
join person.Person pp
on pp.BusinessEntityID = sc.CustomerID
group by sspp.Type

)

select sum(sales_count) as promo_sales, sum(tot_rev) as promo_rev, round(100 * sum(tot_rev)/(select sum(tot_rev) from promo_cte),2) as promo_rev_percent
from promo_cte
where type_ != 'No Discount'


------What are the demographic characteristics of our customer base?
select * from person.Person



------------Which customer segments are most loyal?
select max(order_date) as maxx, min(order_date) as minn, DATEDIFF(month,min(order_date), max(order_date)) as no_of_months
from sales.SalesOrderHeader

----to get loyal customer segment, we will drill to know how many times they made repeat purchases within the last 37months

-----customers category with repeat purchases of more than 10 in the last six months

select pp.persontype, count(sh.customerID) as rep_purchase_count 
from person.person pp
join sales.Customer sc
on pp.businessentityID = sc.customerID
join sales.SalesOrderHeader sh
on sc.CustomerID = sh.CustomerID
----group by pp.persontype
where sh.customerID in 
		(select customerID
				from (select sc.customerID, count(sh.salesorderID) as purchase_count
						from sales.Customer sc
						join sales.SalesOrderHeader sh
						on sc.CustomerID = sh.CustomerID
						join sales.salesorderdetail sd
						on sh.salesorderId = sd.salesorderID
						group by sc.CustomerID
						having count(sh.customerID) >10)sub) 
group by pp.persontype


-----tracking customers category with more than 5 purchases in the last six months
-----customers category with either repeat purchases of more than 30 or have made purchases in the last six months

select pp.persontype, count(sh.customerID) as rep_purchase_count 
from person.person pp
join sales.Customer sc
on pp.businessentityID = sc.customerID
join sales.SalesOrderHeader sh
on sc.CustomerID = sh.CustomerID
----group by pp.persontype
where sh.customerID in 
		(select customerID
				from (select sc.customerID, count(sh.salesorderID) as purchase_count, ---min(sh.order_date), max(sh.order_date)
							datediff(month, min(sh.order_date), max(sh.order_date)) as last_purchase
						from sales.Customer sc
						join sales.SalesOrderHeader sh
						on sc.CustomerID = sh.CustomerID
						join sales.salesorderdetail sd
						on sh.salesorderId = sd.salesorderID
						group by sc.CustomerID
						having count(sh.customerID) >30 or datediff(month, min(sh.order_date), max(sh.order_date))<=6)sub)  
group by pp.persontype

--------How does customer behavior vary by geographic region?
----for the purpose of our analysis, this will be addressed in two ways
------i. looking into the category of items purchased by customers across all regions
------ii. looking at buying pattern/ frequency of purchase

-----i

select distinct (st.name) as region, (pp.name) as product_name, count(sd.ProductID) as purchase_count
from sales.SalesTerritory st
join sales.Customer sc
on st.TerritoryID = sc.TerritoryID
join sales.SalesOrderHeader sh
on sh.CustomerID = sc.CustomerID
join sales.SalesOrderDetail sd
on sh.SalesOrderID = sd.SalesOrderID
join production.product pp
on sd.productID = pp.ProductID
group by st.name, pp.name
having count(sh.customerID) > 50
order by 1,3 asc

with buypatt_cte (region, product_name, purchase_count)
as
(
select distinct (st.name) as region, (pp.name) as product_name, count(sd.ProductID) as purchase_count
from sales.SalesTerritory st
join sales.Customer sc
on st.TerritoryID = sc.TerritoryID
join sales.SalesOrderHeader sh
on sh.CustomerID = sc.CustomerID
join sales.SalesOrderDetail sd
on sh.SalesOrderID = sd.SalesOrderID
join production.product pp
on sd.productID = pp.ProductID
group by st.name, pp.name
-----having count(sh.customerID) > 50
-----order by 1,3 asc
)

select top 10 product_name, max(purchase_count) as pur_count
from buypatt_cte 
----where region = 'canada'
group by region, product_name
order by 2 desc


------ii. looking at buying pattern/ frequency of purchase by region

select (st.name) as region, count(sh.CustomerID) as pur_count
from sales.SalesTerritory st
join sales.Customer sc
on st.TerritoryID = sc.TerritoryID
join sales.SalesOrderHeader sh
on sh.CustomerID = sc.CustomerID
where sh.CustomerID in
					(select customerID 
						from(select sc.customerID, count(sh.customerID) as pur_count
							from sales.Customer sc
							join sales.SalesOrderHeader sh
							on sc.CustomerID =sh.CustomerID
							join sales.SalesOrderDetail sd
							on sd.SalesOrderID = sh.SalesOrderID
							group by sc.CustomerID
							having count(sh.customerID) >10
							)
							sub)
group by st.name

------Are there any correlations between customer demographics and purchasing behavior?

----first we create a temp table in order to enable us bring in the required columns since they reside in different tables


-----the query below gives us a total count of customers by region
select  (st.name) as region, count(sc.CustomerID) as cus_count
from sales.Customer sc
join sales.salesterritory st
on st.territoryID = sc.territoryID
group by st.name;


-----the query here gives us the number of purchase made per the number of customers from each region as well as the amount they spent
select  (st.name) as region, count(sh.customerID) as pur_count, sum(totaldue) as Amount_spent
from sales.Customer sc
join sales.salesterritory st
on st.territoryID = sc.territoryID
join sales.SalesOrderHeader sh
on sc.CustomerID = sh.CustomerID
group by st.Name;
							
----bringing them together

with cus_count_cte as
						(
							select  (st.name) as region, count(sc.CustomerID) as cus_count
							from sales.Customer sc
							join sales.salesterritory st
							on st.territoryID = sc.territoryID
							group by st.name
						),
	pur_count as (
							select  (st.name) as region, count(sh.customerID) as pur_count, sum(totaldue) as Amount_spent
							from sales.Customer sc
							join sales.salesterritory st
							on st.territoryID = sc.territoryID
							join sales.SalesOrderHeader sh
							on sc.CustomerID = sh.CustomerID
							group by st.Name
				)

	select cc.region, cc.cus_count, pc.pur_count, pc.Amount_spent
	from cus_count_cte cc
	join pur_count pc
	on cc.region = pc.region
	group by cc.region, cc.cus_count, pc.pur_count, pc.Amount_spent



-----inserting into our temp table
drop table if exists #cus_buy_pattern
create table ##cus_buy_pattern 
							(Region varchar (50)
							,Cus_count int
							,pur_count int
							,Amount_spent float)

insert into ##cus_buy_pattern  (Region, cus_count, pur_count, Amount_spent) 
	select Region, cus_count, null, null
							from( 
							select  (st.name) as region, count(sc.CustomerID) as cus_count
							from sales.Customer sc
							join sales.salesterritory st
							on st.territoryID = sc.territoryID
							group by st.name
						) as cus_count_by_region_cte 
						union all
	select null, null, pur_count, Amount_spent 
							from (
							select  (st.name) as region, count(sh.customerID) as pur_count, sum(totaldue) as Amount_spent
							from sales.Customer sc
							join sales.salesterritory st
							on st.territoryID = sc.territoryID
							join sales.SalesOrderHeader sh
							on sc.CustomerID = sh.CustomerID
							group by st.Name
				) as pur_count_cte

-----due to the result of the above query we have to create two temp tables and bring them together using joins
 create table ##cus_count_by_region (
									Region varchar (50)
									,Cus_count int
									)
insert into ##cus_count_by_region (Region, Cus_count)
select Region, cus_count
							from( 
							select  (st.name) as region, count(sc.CustomerID) as cus_count
							from sales.Customer sc
							join sales.salesterritory st
							on st.territoryID = sc.territoryID
							group by st.name) as cus_count_cte


create table ##pur_count_by_region (
									Region varchar (50)
									,Pur_count int
									,Amount_spent float
									)
insert into ##pur_count_by_region
select Region, Pur_count, Amount_spent
										
							from(
							select (st.name) as region,count(sh.customerID) as pur_count, sum(totaldue) as Amount_spent
							from sales.Customer sc
							join sales.salesterritory st
							on st.territoryID = sc.territoryID
							join sales.SalesOrderHeader sh
							on sc.CustomerID = sh.CustomerID
							group by st.Name
				) as pur_count_cte

select * from ##cus_count_by_region
order by 1
select * from ##pur_count_by_region
order by 1

-----joining both tables

select cc.Region, cc.cus_count, pc.pur_count, pc.Amount_spent
from ##cus_count_by_region cc
join ##pur_count_by_region pc
on cc.Region = pc.Region

----here we create our final temp table to hold our data
create table ##Pur_behav_by_region (
									Region varchar (50)
									,Customer_count int
									,No_of_Purchase int
									,Amount_spent float
									)

insert into ##Pur_behav_by_region
select cc.Region, cc.cus_count, pc.pur_count, pc.Amount_spent
from ##cus_count_by_region cc
join ##pur_count_by_region pc
on cc.Region = pc.Region

select * from ##Pur_behav_by_region

----now we bring in our correlation function to show if the number of customer for each location influences their number of purchase
--------region and customer purchase
DECLARE @N INT, @SumX FLOAT, @SumY FLOAT, @SumXY FLOAT, @SumXSquare FLOAT, @SumYSquare FLOAT;
SELECT 
    @N = COUNT(*),
    @SumX = SUM(Customer_count),
    @SumY = SUM(No_of_purchase),
    @SumXY = SUM(Customer_count * No_of_purchase),
    @SumXSquare = SUM(Customer_count * Customer_count),
    @SumYSquare = SUM(No_of_purchase * No_of_purchase)
FROM ##Pur_behav_by_region;

SELECT 
    (@N * @SumXY - @SumX * @SumY) / SQRT((@N * @SumXSquare - POWER(@SumX, 2)) * (@N * @SumYSquare - POWER(@SumY, 2))) AS CorrelationCoefficient
FROM ##Pur_behav_by_region;

----------region and amount spent


-------------Which sales people are the most successful?

----these are the top 5 people who generated the most revenue
select top 5 concat(pp.FirstName, ' ', pp.LastName) as sales_person, sp.businessentityID, count(sh.customerID) as sales_count, sum(sh.TotalDue) rev_gen
from person.person pp
join sales.SalesPerson sp
on sp.businessentityID = pp.BusinessEntityID 
join sales.SalesOrderHeader sh
on sp.BusinessEntityID = sh.SalesPersonID
group by sp.BusinessEntityID, concat(pp.FirstName, ' ', pp.LastName)  
order by 4 desc




------How does sales performance vary by geographic region

select * from sales.SalesTerritory
select * from sales.SalesPerson
order by 2 desc
select * from sales.SalesOrderHeader



select * from sales.SalesOrderHeader
where SalesPersonID is not null





--------How does sales performance vary by geographic region?
select st.name, count(sh.salespersonID) as sales_count_by_salespersons, sum(sh.totaldue) as rev_gen
from sales.SalesTerritory st
join sales.SalesOrderHeader sh
on st.TerritoryID = sh.TerritoryID
group by st.name
order by 1 asc

select st.name, count(sh.salespersonID) as sales_count_by_salespersons, sum(sh.totaldue) as rev_gen
from sales.SalesTerritory st
join sales.SalesPerson sp 
on st.TerritoryID = sp.TerritoryID
join sales.SalesOrderHeader sh
on sp.BusinessEntityID = sh.SalesPersonID
group by st.name
order by 1 asc


-----Which territories generate the most revenue?
select top 5 st.name, sum(sh.totaldue) as rev_gen
from sales.SalesTerritory st
join sales.SalesOrderHeader sh
on st.TerritoryID = sh.TerritoryID
group by st.Name
order by 2 desc

------How does sales performance vary by territory?

select * from sales.SalesOrderHeader
select * from sales.SalesOrderDetail

select st.name, count(sh.SalesOrderID) as sales_count
from sales.SalesTerritory st
join sales.SalesOrderHeader sh
on st.TerritoryID = sh.TerritoryID
group by st.Name
order by 2 desc

---------What are the most profitable products?