---------- Sous-requêtes where ----------------------
use AdventureWorksDW2019

-- Exemples simples pouvant être remplacés par des jointures

-- A.1 Quels sont les produits différents qui ont déjà été commandé ?
-- table FactInternetSales
-- 158 produits commandé
select ProductKey, EnglishProductName
from Dimproduct
where ProductKey in 
					( select distinct ProductKey
					from FactInternetSales)

-- A.2 Quels sont les produits différents qui n'ont pas déjà été commandé ?
-- Résultat : 448 
set statistics time on
dbcc freeproccache

-- exécution 1390 en moyenne UC et TE
select ProductKey, EnglishProductName
from Dimproduct
where ProductKey not in 
					( select distinct ProductKey
					from FactInternetSales)

-- exécution join : 15 UC et 44 TE
select distinct p.ProductKey, EnglishProductName
from Dimproduct p left join FactInternetSales s ON p.ProductKey =s.ProductKey
where s.ProductKey IS NULL

-- B.1 Quelles sont les promotions (nom français) avec des ventes ? 
-- Tables FactInternetSales et Dimpromotion
-- 4 résultats dont 1 nommée 'aucune remise'
select FrenchPromotionName
from DimPromotion
where promotionkey in (select distinct promotionkey
					   from FactInternetSales)

-- B.2 Quelles sont les noms des promotions (en français) pour lesquelles, il n'y a jamais eu de ventes ?
-- 12 remises
select FrenchPromotionName
from DimPromotion
where promotionkey not in (select distinct promotionkey
					   from FactInternetSales)

-- Exemples ne pouvant pas être remplacés par des jointures !

-- C.1 Je souhaite récupérer les promotions (frenchPromotionName) réalisées par le revendeur 'Modular Cycle Systems' mais pas par le revendeur 'Advanced Bike Components'
-- Tables DimReseller, FactResellerSales, DimPromotion
-- Résultat : 1 promotion : Déstockage Vélo de route 650

-- requête principale : Modular Cycle Systems
select distinct p.PromotionKey, p.FrenchPromotionName
from DimReseller r  join FactResellerSales rs on r.ResellerKey=rs.ResellerKey
					join DimPromotion p on p.promotionkey= rs.promotionkey
where resellerName = 'Modular Cycle Systems'
 and p.PromotionKey not in (
				-- requête secondaire : Advanced Bike Components
				select distinct p.PromotionKey
				from DimReseller r join FactResellerSales rs on r.ResellerKey=rs.ResellerKey
								   join DimPromotion p on p.promotionkey= rs.promotionkey
				where resellerName = 'Advanced Bike Components'
						)

-- C.2 Je souhaite récupérer les produits (pr.englishproductname) vendus par le revendeur 'Modular Cycle Systems' mais pas le revendeur 'Advanced Bike Components'
-- Résultat : 16 produits vendus que par Modular

-- requête principale : Modular Cycle Systems = 73
select distinct pr.productkey, pr.englishproductname
from DimReseller r  join FactResellerSales rs on r.ResellerKey=rs.ResellerKey
					join Dimproduct pr on pr.productkey=rs.ProductKey
where resellerName = 'Modular Cycle Systems'
 and pr.productkey not in (
				-- requête secondaire : Advanced Bike Components = 133
				select distinct pr.productkey
				from DimReseller r join FactResellerSales rs on r.ResellerKey=rs.ResellerKey
								   join Dimproduct pr on pr.productkey=rs.ProductKey
				where resellerName = 'Advanced Bike Components'
						)

-------------------------------------------------

-- D.1
-- Table Factresellersales : 
-- Afficher la somme des quantité vendues en 2012 que si plus que la somme des quantités en 2011
-- Résultat : 1 (81328)
select sum(OrderQuantity) as 'total quantité vendue'
From FactResellerSales s
where year(s.orderdate) = 2012
having sum(OrderQuantity) > (
		select sum(OrderQuantity) as 'total quantité vendue'
		From FactResellerSales ss
		where year(ss.orderdate) = 2011)

-- D.2 Corrélation Suite D.1
-- Afficher les revendeurs avec plus de quantités vendues en 2012 qu'en 2011
-- Résultat : 132
select sum(OrderQuantity) as 'total quantité vendue', r.ResellerKey, ResellerName
From FactResellerSales s join DimReseller r on r.ResellerKey=s.ResellerKey
where year(s.orderdate) = 2012
group by r.ResellerKey, ResellerName
having sum(OrderQuantity)  > 
		( select sum(OrderQuantity) as 'total quantité vendue'
		  From FactResellerSales ss join DimReseller rr on rr.ResellerKey=ss.ResellerKey
		  where year(ss.orderdate) = 2011
		  and r.ResellerKey=rr.ResellerKey
		  group by rr.ResellerKey, ResellerName
		)

-- D.3 Corrélation Suite D.2
-- je veux afficher en plus le nombre de ventes 
-- et regrouper le tout en plus par pays (+revendeur déjà présent)
-- résultat : 132 lignes
select EnglishCountryRegionName, r.ResellerKey, ResellerName,
		sum(OrderQuantity) as 'somme quantités',
		count(distinct SalesOrderNumber) as 'nombre de ventes'
From FactResellerSales f Join DimReseller r on f.ResellerKey=r.ResellerKey
						 Join DimGeography g on g.GeographyKey=r.GeographyKey
where year(orderdate)= 2012
Group by EnglishCountryRegionName, r.ResellerKey, ResellerName
having sum(OrderQuantity) >
			-- copier/coller puis changer le nom des tables dans sous requête + ANNEE 2012
			-- rajouter where entre le pays et le revendeur
		(select sum(OrderQuantity) as 'somme quantités'
		From FactResellerSales ff Join DimReseller rr on ff.ResellerKey=rr.ResellerKey
						 Join DimGeography gg on gg.GeographyKey=rr.GeographyKey
		where year(orderdate)= 2011
			and rr.ResellerKey=r.ResellerKey
			and gg.EnglishCountryRegionName=g.EnglishCountryRegionName
		Group by EnglishCountryRegionName, rr.ResellerKey, ResellerName
		)
order by resellerkey asc

-- D.4 Corrélation Suite D.3
-- quand le nombre de vente en 2013 (par pays et revendeur) est inférieur à celle de 2012
-- Résultat : 6 lignes
select EnglishCountryRegionName, r.ResellerKey, ResellerName,
		sum(OrderQuantity) as 'somme quantités',
		count(distinct SalesOrderNumber) as 'nombre de ventes'
From FactResellerSales f Join DimReseller r on f.ResellerKey=r.ResellerKey
						 Join DimGeography g on g.GeographyKey=r.GeographyKey
where year(orderdate)= 2013
Group by EnglishCountryRegionName, r.ResellerKey, ResellerName
having sum(OrderQuantity) >
		(select sum(OrderQuantity) as 'somme quantités'
		 From FactResellerSales ff Join DimReseller rr on ff.ResellerKey=rr.ResellerKey
						 Join DimGeography gg on gg.GeographyKey=rr.GeographyKey
		where year(orderdate)= 2012 
			and rr.ResellerKey=r.ResellerKey
			and gg.EnglishCountryRegionName=g.EnglishCountryRegionName
		Group by EnglishCountryRegionName, rr.ResellerKey, ResellerName
		)

AND count(distinct SalesOrderNumber) <
			-- même requête que première sous-requête mais avec nombre de ventes dans le select
		(select count(distinct SalesOrderNumber) as 'nombre de ventes'
		 From FactResellerSales ff Join DimReseller rr on ff.ResellerKey=rr.ResellerKey
						 Join DimGeography gg on gg.GeographyKey=rr.GeographyKey
		where year(orderdate)= 2012 
			and rr.ResellerKey=r.ResellerKey
			and gg.EnglishCountryRegionName=g.EnglishCountryRegionName
		Group by EnglishCountryRegionName, rr.ResellerKey, ResellerName
		)

---------- Sous-requêtes FROM ----------------------

-- E.1 Quelle est en moyenne, le nombre de clients par promotion ?
-- en excluant la promotionkey 1 (correspondant à no discount)
-- Table FactInternetSales
-- Résultat : 674
select avg([nombre de clients]) as Moyenne
from    (
-- Requête principale : La moyenne (calcul final)
		-- Requête secondaire : Compter le nombre de couleurs par produits (1er calcul à faire)
		select count(distinct customerkey) as 'nombre de clients', promotionkey
		From FactInternetSales
		where promotionkey <> 1
		group by promotionkey
		) as nb_col

-- E.2 Quelle est la différence entre la moyenne de clients par promotion en 2013 et en 2012 ?
-- Résultat : 11 502
select avg([nombre de clients 2013]) - avg([nombre de clients 2012]) as Diff
from    (
-- Requête principale : La moyenne (calcul final)
		-- Requête secondaire a : Compter le nombre de clients par promotion en 2013
		select count(customerkey) as 'nombre de clients 2013', promotionkey
		From FactInternetSales
		where year(orderdate) = 2013
		group by promotionkey
		) as nb_col2013 ,
		(
		-- Requête secondaire b : Compter le nombre de clients par promotion en 2012
		select count(customerkey) as 'nombre de clients 2012', promotionkey
		From FactInternetSales
		where year(orderdate) = 2012
		group by promotionkey
		) as nb_col2012

-- E.3.a DIfférence performance sur cas de comparaison de listing entre WHERE ET FROM
-- Version where environ 1500 ms 
-- les noms revendeurs qui ont fait des ventes en 2012 et en 2013
set statistics time on
dbcc freeproccache

select distinct r.ResellerKey, ResellerName
From dimreseller as r JOIN FactResellerSales as s on r.ResellerKey=s.ResellerKey
where year(orderdate) = 2012
 AND r.ResellerKey IN 
		(select distinct ResellerKey
		From FactResellerSales
		where year(orderdate) = 2013)

-- E.3.b même chose mais avec un FROM - 33 ms
select distinct REV12.Resellername, rev12.ResellerKey
From 
		(select distinct r.ResellerKey, ResellerName -- rajout du resellername pour pouvoir l'afficher dans requête principale
		From FactResellerSales join DimReseller r on FactResellerSales.ResellerKey = r.ResellerKey
		where year(orderdate) = 2012) as REV12 JOIN

		(select distinct ResellerKey
		From FactResellerSales
		where year(orderdate) = 2013) as REV13

	ON rev12.ResellerKey = rev13.ResellerKey

-- OU jointure dans le from principal pour avoir accès au nom du revendeur
select distinct ResellerName,r.ResellerKey
from 
		(select distinct ResellerKey from FactResellerSales 
		where year(OrderDate)= 2012) REV12
join	(select distinct ResellerKey from FactResellerSales 
		where year(OrderDate)= 2013) REV13
        on REV12.ResellerKey=REV13.ResellerKey
join DimReseller r on r.ResellerKey=REV13.ResellerKey

---------- Sous-requêtes select ----------------------

-- F.1 Afficher le prix moyen (lisprice) par couleur et le prix moyen global (toutes couleurs confondues)
-- Seulement pour les couleurs qui ont une moyenne supérieure à la moyenne globale
-- Table DImproduct (quand listprice est renseigné)
-- Résultat : 5 couleurs

select color, avg(listprice) as 'moy par couleur',
(select avg(listprice) From DimProduct) Moy_listprice
from DimProduct
where listprice is not null 
group by color
Having avg(Listprice) > (select avg(listprice)
					From DimProduct)

-- G.1 Je souhaite afficher le nom (lastname), le taux de base du salaire (baserate) et le département des employés
-- Ainsi que la moyenne du taux de base de tous les employés
-- Résultat : 296
SELECT lastname, baserate, departmentname
, (SELECT AVG(baserate) FROM DimEmployee) as moyenne_tauxbase
 FROM DimEmployee e

-- G.2 Suite du G.1, pour chaque ligne de résultat (chaque employé), 
-- je souhaite également afficher le taux de base de son département
-- Si dupont est du département des ventes alors sur sa ligne doit s'afficher dans ma nouvelle colonne : la moyenne du département des ventes
SELECT lastname, baserate, departmentname,
	(SELECT AVG(baserate) FROM DimEmployee) as moyenne_tauxbase,
	(SELECT AVG(baserate) FROM DimEmployee
    WHERE DepartmentName = e.departmentname) as moy_tauxbase_dep
 FROM DimEmployee e

-- G.3 Afficher les résultats quand le taux de base de l'employé
-- est inférieure à la moyenne des taux de base de son département
SELECT lastname, baserate, departmentname,
	--(SELECT AVG(baserate) FROM DimEmployee) as moyenne_tauxbase,
	(SELECT AVG(baserate) FROM DimEmployee
    WHERE DepartmentName = e.departmentname) as moy_tauxbase_dep
 FROM DimEmployee e
 where baserate < (SELECT AVG(baserate) FROM DimEmployee
					WHERE DepartmentName = e.departmentname)