/*
 *		TECH 60701 -- Technologies de l'intelligence d'affaires
 *					HEC Montréal
*/
 use AdventureWorks2019
 go

/*
	Question #1 :
		AdventureWorks would like to implement former General Electric Chairman and CEO Jack Welch's "The vitality model",
		Jack Welch, which has been described as a "20-70-10" system. The "most important 20%" of employees are the most productive, and 70% (the "indispensable 70 
		indispensable") work well. The remaining 10% are non-producers and must be let go.

		Using a ranking clause and a subquery, you need to write a query to identify the "top 20%" of sales people
		(to congratulate and encourage them!) as well as the bottom 10% (to kick them out <insert evil laugh here>)!!!
		So we don't want the salespeople belonging to the remaining 70% to appear in the report.

		(By salespeople, we're referring to sales clerks, regardless of job title.)
		
		Since AdventureWorks sells mainly bicycles, spring (March to May, incl.) is crucial to its financial results.
		Therefore, the analysis should only take into account the subtotal sales that salespeople have achieved for this 
		period, whatever the year. For each result, the following should be displayed

			- Salesperson ID
			- Seller's National ID Number
			- Seller's first name
			- Seller's surname
			- The seller's subtotal sales for the fourth quarter (formatted in dollars, i.e. $xxx.xx)
			- The seller's percentage rank (formatted as a percentage with two points of precision)
			- The decile to which the seller belongs
			- Personalized message for: 1st decile 'Excellent performance!'; 2nd decile 'Keep up the good work!
				10th decile 'Are you looking for a job elsewhere?
*/



select 
       *,
	    Case
			when Decile =1 then 'Excellente performance !'
			when Decile =2 then 'Continuez, vous allez bien !'
			when Decile =10 then 'Cherchez vous un emploi ailleurs !'
			else 'Whatever'
		end as 'Status'
 from (
    select
       soh.SalesPersonID,
		e.NationalIDNumber,
		p.FirstName,
		p.LastName,
		FORMAT(SUM(soh.SubTotal), 'c', 'en-us') as 'Somme sous-total Ventes',
		/*Le rang en pourcentage du vendeur (formaté en pourcentage avec deux points de précision)*/
		FORMAT(ROUND(percent_rank() over(order by sum(soh.SubTotal) desc), 2), 'p') as 'Le rang en %',
		NTILE(10) over(order by SUM(soh.SubTotal) desc) as 'Decile'
		from
	Sales.SalesOrderHeader soh
	inner join Sales.SalesPerson sp1 on soh.SalesPersonID = sp1.BusinessEntityID
	inner join Person.Person p on sp1.BusinessEntityID = p.BusinessEntityID
	inner join HumanResources.Employee e on p.BusinessEntityID= e.BusinessEntityID

	where Month(OrderDate) in (3,4,5) 
	group by soh.SalesPersonID, e.NationalIDNumber, p.FirstName, p.LastName
	) as Table2
	where Decile in (1,2,10)
	


/*
	Question #2 :
		AdventureWorks would like to explore its customers' purchases of accessories (non-manufactured products). We are particularly interested in accessories that were ordered
		were ordered by stores located in Canada at the same time as they made bicycle purchases (products manufactured by AdventureWorks).
		Therefore, data should be displayed only for sales made to stores (not individual customers) who purchased bicycles.
		
		Using a CTE, you should display a list containing information grouped by product identifier, product name,
		product number.

		Your report should contain only four columns, as follows:


		
		ProductID	|Name						|ProductNumber	|OrderCount	|Rang
		715			|Long-Sleeve Logo Jersey, L	|LJ-0192-L		|238		|1
		712			|AWC Logo Cap				|CA-1098		|237		|2
		708			|Sport-100 Helmet, Black	|HL-U509		|190		|3
		...			|...						|...			|...		|...

		This indicates, for example, that of all the orders placed by stores in which manufactured products were purchased, 238 
		orders also included the purchase of product 715 (Long-Sleeve Logo Jersey, L), 237 orders included the purchase of product 712 (AWC Logo Cap),
		etc. The rank used does not allow value jumps.

		Sort by "OrderCount", in descending order.
*/
	--7385 
with CTEQ2(ProductID, Name, ProductNumber,SalesOrderID, SalesOrderDetailID) as
(
select  
pt.ProductID, pt.Name, pt.ProductNumber,
soh.SalesOrderID, sod.SalesOrderDetailID
		
	from Sales.SalesOrderHeader soh 
	inner join Sales.Customer c on c.CustomerID = soh.CustomerID
	inner join Person.BusinessEntityAddress bea on c.StoreID = bea.BusinessEntityID
	inner join person.Address a on a.AddressID = bea.AddressID
	inner join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
	inner join Production.Product pt on sod.ProductID = pt.ProductID
	inner join Person.StateProvince sp on sp.StateProvinceID =a.StateProvinceID
	where sp.CountryRegionCode = 'CA' AND  pt.MakeFlag =1 --AND soh.SalesOrderID='55280'
	)
	select
	p.ProductID,
	p.Name,
	p.ProductNumber,
	COUNT( distinct(CTEQ2.SalesOrderID)) as OrderCount,
	dense_rank()over(order by COUNT(distinct(CTEQ2.SalesOrderID)) desc) as 'Rang'
	from CTEQ2
	inner join Sales.SalesOrderDetail sod on sod.SalesOrderID = CTEQ2.SalesOrderID and sod.SalesOrderDetailID <> CTEQ2.SalesOrderDetailID 
	inner join Production.Product p on p.ProductID = sod.ProductID
	where p.MakeFlag =0
	group by  p.ProductID, p.[Name], p.ProductNumber;




/*
	Question #3 a) :
		You are asked to provide a query displaying the following details of active suppliers, with preferred status, from whom
		from whom AdventureWorks has placed fewer than 30 orders. Show:
			- Supplier ID
			- Order date
			- A sequence number assigned to each order placed with the supplier, starting with the most recent order
			- The subtotal of each order (formatted in dollars, i.e. $xxx.xx)
*/



select poh.VendorID
    
	, poh.OrderDate, row_number()over(partition by poh.VendorID order by OrderDate desc) as 'No_sequence' , FORMAT(poh.SubTotal, 'C')
	from Purchasing.PurchaseOrderHeader poh
	inner join Purchasing.Vendor v on v.BusinessEntityID = poh.VendorID
	where ActiveFlag =1 AND PreferredVendorStatus=1 AND 
    poh.VendorID in (select VendorID  from Purchasing.PurchaseOrderHeader group by VendorID having count(PurchaseOrderID)<=30);
	




/*
	Question #3 b) :
		AdventureWorks would like to know which of these preferred suppliers (with whom AdventureWorks has placed fewer than 30 orders) tends to
		tend to increase their prices. The company would like to use this information to remove their "preferred supplier" status.
		supplier" status. The assumption here is that orders from a supplier remain stable over time and are therefore
		always for similar products/quantities.
				
		Using a CTE based on the query in Part a), build a query that will display the list of suppliers for which
		the average amount (using the subtotal) of their three most recent orders is greater than the average amount they have requested 
		AdventureWorks to date.

		We'd like to display :
			- Supplier ID
			- The average amount of all orders placed with the supplier
			- The average amount of the three most recent orders placed with the supplier
			- The difference between the average amount of the three most recent orders placed with the supplier and the average amount of all orders placed with the supplier.
				all orders placed with the supplier.

		Your report should contain only these four columns, and be filtered by the reduction in acquisition costs, so that 
		so that the largest reduction is at the top of the list. All amounts must be formatted in dollars, i.e. $xxx.xx.
*/




with CTEQ3b(VendorID, OrderDate, RowNum, SubTotal) as
(select poh.VendorID    
	, poh.OrderDate, 
	row_number()over(partition by poh.VendorID order by OrderDate desc) , 
	poh.SubTotal
	from Purchasing.PurchaseOrderHeader poh
	inner join Purchasing.Vendor v on v.BusinessEntityID = poh.VendorID
	where v.ActiveFlag =1 AND v.PreferredVendorStatus=1 AND 
    poh.VendorID in (select VendorID  from Purchasing.PurchaseOrderHeader group by VendorID having count(PurchaseOrderID)<=30)
	
)
select 
c.VendorID,

(select format(avg(Subtotal),'C') from Purchasing.PurchaseOrderHeader poh where c.VendorID=poh.VendorID) as 'total',
format(avg(c.SubTotal),'C') as 'total 3' ,
Format(avg(SubTotal) - (select avg(Subtotal) from Purchasing.PurchaseOrderHeader poh where c.VendorID=poh.VendorID),'C') as 'difference'
from CTEQ3b c
where c.RowNum<=3
group by c.VendorID
having avg(SubTotal) - (select avg(Subtotal) from Purchasing.PurchaseOrderHeader poh where c.VendorID=poh.VendorID) >0
order by avg(SubTotal) - (select avg(Subtotal) from Purchasing.PurchaseOrderHeader poh where c.VendorID=poh.VendorID) desc