---------- Jointures internes ---------------
use AdventureWorksDW2022

-- Exercices à faire ensemble : 

-- A.1 Je souhaite afficher les 100 premières commandes (table factresellersales - afficher toutes les colonnes)
select top 100 * 
From FactResellerSales

-- A.2 Je souhaite afficher le numéro de commande (salesordernumber) et la ligne de commande (salesorderlinenumber) 
-- ainsi que la promotion réalisée (englishpromotioncategory)
-- Ne pas afficher les lignes correspondantes à No discount 
-- Résultat : 3 282
select salesordernumber, SalesOrderLineNumber,  DimPromotion.EnglishPromotionCategory
From FactResellerSales join DimPromotion on FactResellerSales.promotionkey=DimPromotion.PromotionKey
where EnglishPromotionCategory <> 'No discount'

-- avec alias
select salesordernumber, SalesOrderLineNumber,  p.EnglishPromotionCategory
From FactResellerSales r join DimPromotion p on r.promotionkey=p.PromotionKey
where EnglishPromotionCategory <> 'No discount'

-- Exercices individuels : 
-- A.3 Suite de A.2 : Je souhaite afficher le nom du revendeur (resellername) en lien avec ces lignes
-- Excepté les lignes qui concernent les revendeurs : greater bike store ET Unique Bikes
-- Résultat : 3 269
select salesordernumber, SalesOrderLineNumber,  p.EnglishPromotionCategory, re.ResellerName
From FactResellerSales r join DimPromotion p on r.promotionkey=p.PromotionKey
						 join dimreseller re on re.ResellerKey=r.ResellerKey
where EnglishPromotionCategory <> 'No discount'
		 and resellername not in ('greater bike store','Unique Bikes')

-- avec alias
select salesordernumber, SalesOrderLineNumber,  DimPromotion.EnglishPromotionCategory, dimreseller.ResellerName
From FactResellerSales join DimPromotion on FactResellerSales.promotionkey=DimPromotion.PromotionKey
						 join dimreseller on dimreseller.ResellerKey=FactResellerSales.ResellerKey
where EnglishPromotionCategory <> 'No discount'
		 and resellername not in ('greater bike store','Unique Bikes')

-- A.4 Suite de A.3 : Afficher les pays (EnglishCountryRegionName) des revendeurs en plus
-- Ne prendre que les revendeurs français et allemands
-- Résultat : 457
select salesordernumber, SalesOrderLineNumber,  DimPromotion.EnglishPromotionCategory, 
		dimreseller.ResellerName, EnglishCountryRegionName
From FactResellerSales join DimPromotion on FactResellerSales.promotionkey=DimPromotion.PromotionKey
						 join dimreseller on dimreseller.ResellerKey=FactResellerSales.ResellerKey
						 join dimgeography on dimgeography.GeographyKey=dimreseller.GeographyKey
where EnglishPromotionCategory <> 'No discount'
		 and resellername not in ('greater bike store','Unique Bikes')
		 and EnglishCountryRegionName in ('Germany', 'France')

-- avec alias
select salesordernumber, SalesOrderLineNumber,  p.EnglishPromotionCategory, 
		re.ResellerName, g.EnglishCountryRegionName
From FactResellerSales r join DimPromotion p on r.promotionkey=p.PromotionKey
						 join dimreseller re on re.ResellerKey=r.ResellerKey
						 join dimgeography g on g.GeographyKey=re.GeographyKey
where EnglishPromotionCategory <> 'No discount'
		 and resellername not in ('greater bike store','Unique Bikes')
		 and EnglishCountryRegionName in ('Germany', 'France')

-- A.5 Suite de A.4 : Afficher en plus : le nom de la categorie EnglishProductCategoryName
-- Seulement pour les lignes d'ordre correspondantes à un ordre de vente en 2011 (champ orderdate dans table factresellersales)
-- Résultat : 14
select salesordernumber, SalesOrderLineNumber,  DimPromotion.EnglishPromotionCategory, 
		dimreseller.ResellerName, EnglishCountryRegionName, EnglishProductCategoryName
From FactResellerSales join DimPromotion on FactResellerSales.promotionkey=DimPromotion.PromotionKey
						 join dimreseller on dimreseller.ResellerKey=FactResellerSales.ResellerKey
						 join dimgeography on dimgeography.GeographyKey=dimreseller.GeographyKey
						 join DimProduct on DimProduct.productkey=FactResellerSales.productkey
						 join DimProductSubcategory on DimProductSubcategory.ProductSubcategoryKey=DimProduct.ProductSubcategoryKey
						 join DimProductCategory on DimProductCategory.ProductCategoryKey=DimProductSubcategory.ProductCategoryKey
where EnglishPromotionCategory <> 'No discount'
		 and resellername not in ('greater bike store','Unique Bikes')
		 and EnglishCountryRegionName in ('Germany', 'France')
		 and year(OrderDate) = '2011'

-- avec alias
select salesordernumber, SalesOrderLineNumber,  p.EnglishPromotionCategory, 
		re.ResellerName, g.EnglishCountryRegionName, EnglishProductCategoryName
From FactResellerSales r join DimPromotion p on r.promotionkey=p.PromotionKey
						 join dimreseller re on re.ResellerKey=r.ResellerKey
						 join dimgeography g on g.GeographyKey=re.GeographyKey
						 join DimProduct pr on pr.productkey=r.productkey
						 join DimProductSubcategory psc on psc.ProductSubcategoryKey=pr.ProductSubcategoryKey
						 join DimProductCategory pc on pc.ProductCategoryKey=psc.ProductCategoryKey
where EnglishPromotionCategory <> 'No discount'
		 and resellername not in ('greater bike store','Unique Bikes')
		 and EnglishCountryRegionName in ('Germany', 'France')
		 and year(OrderDate) = '2011'

-- B Je souhaite connaître le nom des revendeurs ayant réalisé des ventes
-- Tables factresellersales et dimreseller
-- Attention, des revendeurs peuvent avoir le même nom (que faut-il ajouter dans le select pour avoir le bon nombre de lignes ?)
-- Résultat : 635 revendeurs différents
select distinct DimReseller.ResellerName, DimReseller.ResellerKey
from FactResellerSales join DimReseller on FactResellerSales.ResellerKey=DimReseller.ResellerKey

-- avec alias
select distinct r.ResellerName, r.ResellerKey
from FactResellerSales fr join DimReseller r on fr.ResellerKey=r.ResellerKey

-- B.2 Suite de B
-- Je souhaite afficher le nom de la promotion (français) en plus du nom du revendeur (et son numéro) 
-- le numéro de commande (salesordernumber), le numéro de ligne de commande (SalesOrderLineNumber) et de la quantité vendue
-- Trier par salesordernumber asc
-- Résultat : 60855
select distinct DimReseller.ResellerName, DimReseller.ResellerKey, SalesOrderNumber, SalesOrderLineNumber,
				FrenchPromotionName, FactResellerSales.OrderQuantity
from FactResellerSales join DimReseller on FactResellerSales.ResellerKey=DimReseller.ResellerKey
					Join dimpromotion on dimpromotion.PromotionKey=FactResellerSales.PromotionKey

-- avec alias
select distinct SalesOrderNumber, SalesOrderLineNumber, r.ResellerName, r.ResellerKey, 
		FrenchPromotionName, Fr.OrderQuantity
from FactResellerSales fr join DimReseller r on fr.ResellerKey=r.ResellerKey
						  Join dimpromotion p on p.PromotionKey=fr.PromotionKey
order by salesordernumber asc

-- B.3 Suite de B.2
-- Je souhaite rajouter le nom anglais du produit associé pour chaque ligne de résultat
-- Résultat : 60855
select distinct DimReseller.ResellerName, DimReseller.ResellerKey, SalesOrderNumber, SalesOrderLineNumber,
				FrenchPromotionName, FactResellerSales.OrderQuantity, EnglishProductName
from FactResellerSales join DimReseller on FactResellerSales.ResellerKey=DimReseller.ResellerKey
					Join dimpromotion on dimpromotion.PromotionKey=FactResellerSales.PromotionKey
					Join DimProduct on DimProduct.productkey=FactResellerSales.productkey

-- avec alias
select distinct SalesOrderNumber, SalesOrderLineNumber, r.ResellerName, r.ResellerKey, 
		FrenchPromotionName, Fr.OrderQuantity, pp.EnglishProductName
from FactResellerSales fr Join DimReseller r on fr.ResellerKey=r.ResellerKey
						  Join Dimpromotion p on p.PromotionKey=fr.PromotionKey
						  Join DimProduct pp on pp.productkey=fr.productkey
order by salesordernumber asc

-- B.4 Suite de B.3
-- Je souhaite rajouter le pays (EnglishCountryRegionName) et la ville du revendeur
-- et afficher seulement les lignes correspondantes aux Etats Unis et au Canada
-- Seulement quand il y a une remise
-- Trier par Salesordernumber ascendant puis salesorderlinenumber ascendant
-- Résultat : 2435
select distinct DimReseller.ResellerName, DimReseller.ResellerKey, SalesOrderNumber, SalesOrderLineNumber,
				FrenchPromotionName, FactResellerSales.OrderQuantity, EnglishProductName
				, EnglishCountryRegionName, City
from FactResellerSales join DimReseller on FactResellerSales.ResellerKey=DimReseller.ResellerKey
					Join dimpromotion on dimpromotion.PromotionKey=FactResellerSales.PromotionKey
					Join DimProduct on DimProduct.productkey=FactResellerSales.productkey
					Join Dimgeography on Dimgeography.GeographyKey=DimReseller.GeographyKey
where EnglishCountryRegionName in ('United States','Canada') and FrenchPromotionName <> 'Aucune remise'
order by salesordernumber asc, SalesOrderLineNumber asc

-- avec alias
select distinct SalesOrderNumber, SalesOrderLineNumber, r.ResellerName, r.ResellerKey, FrenchPromotionName, Fr.OrderQuantity, pp.EnglishProductName
		, g.EnglishCountryRegionName, g.City
from FactResellerSales fr Join DimReseller r on fr.ResellerKey=r.ResellerKey
						  Join Dimpromotion p on p.PromotionKey=fr.PromotionKey
						  Join DimProduct pp on pp.productkey=fr.productkey
						  Join Dimgeography g on g.GeographyKey=r.GeographyKey
where EnglishCountryRegionName in ('United States','Canada') and FrenchPromotionName <> 'Aucune remise'
order by salesordernumber asc, SalesOrderLineNumber asc

-- B.5 Reprendre SEULEMENT les where et from du B.4 puis rajouter ce qui suit :
-- Je souhaite compter le nombre de commandes différentes
-- la somme des quantités vendues
-- Par produit (englishproductname + ID), par pays et ville
-- Résultat : 1770
select EnglishProductName,  DimProduct.productkey,
		EnglishCountryRegionName, 
		city,
		count(distinct salesordernumber) as 'nombre de commandes',
		sum(orderquantity) as 'somme quantité'
from FactResellerSales join DimReseller on FactResellerSales.ResellerKey=DimReseller.ResellerKey
					Join dimpromotion on dimpromotion.PromotionKey=FactResellerSales.PromotionKey
					Join DimProduct on DimProduct.productkey=FactResellerSales.productkey
					Join Dimgeography on Dimgeography.GeographyKey=DimReseller.GeographyKey
where EnglishCountryRegionName in ('United States','Canada') and FrenchPromotionName <> 'Aucune remise'
Group by EnglishProductName,DimProduct.productkey, EnglishCountryRegionName, city
order by [somme quantité] desc

-- avec alias
select EnglishProductName,pp.productkey,
		EnglishCountryRegionName, 
		city,
		count(distinct salesordernumber) as 'nombre de commandes',
		sum(orderquantity) as 'somme quantité'
from FactResellerSales fr Join DimReseller r on fr.ResellerKey=r.ResellerKey
						  Join Dimpromotion p on p.PromotionKey=fr.PromotionKey
						  Join DimProduct pp on pp.productkey=fr.productkey
						  Join Dimgeography g on g.GeographyKey=r.GeographyKey
where EnglishCountryRegionName in ('United States','Canada') and FrenchPromotionName <> 'Aucune remise'
Group by EnglishProductName, pp.productkey, EnglishCountryRegionName, city
order by [somme quantité] desc

-- B.6 Suite de B.5
-- Je souhaite afficher les résultats que lorsque le nombre de commandes est égal ou supérieur à 5
-- Résultat : 12
select EnglishProductName, DimProduct.productkey,
		EnglishCountryRegionName, 
		city,
		count(distinct salesordernumber) as 'nombre de commandes',
		sum(orderquantity) as 'somme quantité'
from FactResellerSales join DimReseller on FactResellerSales.ResellerKey=DimReseller.ResellerKey
					Join dimpromotion on dimpromotion.PromotionKey=FactResellerSales.PromotionKey
					Join DimProduct on DimProduct.productkey=FactResellerSales.productkey
					Join Dimgeography on Dimgeography.GeographyKey=DimReseller.GeographyKey
where EnglishCountryRegionName in ('United States','Canada') and FrenchPromotionName <> 'Aucune remise'
Group by EnglishProductName,DimProduct.productkey, EnglishCountryRegionName, city
having count(distinct salesordernumber) >= 5
order by [somme quantité] desc

-- avec alias
select EnglishProductName, pp.productkey,EnglishCountryRegionName, city,
		count(distinct salesordernumber) as 'nombre de commandes', sum(orderquantity) as 'somme quantité'
from FactResellerSales fr Join DimReseller r on fr.ResellerKey=r.ResellerKey
						  Join Dimpromotion p on p.PromotionKey=fr.PromotionKey
						  Join DimProduct pp on pp.productkey=fr.productkey
						  Join Dimgeography g on g.GeographyKey=r.GeographyKey
where EnglishCountryRegionName in ('United States','Canada') and FrenchPromotionName <> 'Aucune remise'
Group by EnglishProductName,pp.productkey, EnglishCountryRegionName, city
having count(distinct salesordernumber) >= 5
order by [somme quantité] desc

-- B.7 Suite de B.6
-- Afficher les 5 meilleurs produits par pays et ville par rapport à leurs quantités vendues
-- Résultat : 5
select top 5 EnglishProductName, DimProduct.productkey,
		EnglishCountryRegionName, 
		city,
		count(distinct salesordernumber) as 'nombre de commandes',
		sum(orderquantity) as 'somme quantité'
from FactResellerSales join DimReseller on FactResellerSales.ResellerKey=DimReseller.ResellerKey
					Join dimpromotion on dimpromotion.PromotionKey=FactResellerSales.PromotionKey
					Join DimProduct on DimProduct.productkey=FactResellerSales.productkey
					Join Dimgeography on Dimgeography.GeographyKey=DimReseller.GeographyKey
where EnglishCountryRegionName in ('United States','Canada') and FrenchPromotionName <> 'Aucune remise'
Group by EnglishProductName,DimProduct.productkey, EnglishCountryRegionName, city
having count(distinct salesordernumber) >= 5
order by [somme quantité] desc

-- avec alias
select top 5 EnglishProductName, pp.productkey,EnglishCountryRegionName, city,
		count(distinct salesordernumber) as 'nombre de commandes', sum(orderquantity) as 'somme quantité'
from FactResellerSales fr Join DimReseller r on fr.ResellerKey=r.ResellerKey
						  Join Dimpromotion p on p.PromotionKey=fr.PromotionKey
						  Join DimProduct pp on pp.productkey=fr.productkey
						  Join Dimgeography g on g.GeographyKey=r.GeographyKey
where EnglishCountryRegionName in ('United States','Canada') and FrenchPromotionName <> 'Aucune remise'
Group by EnglishProductName,pp.productkey, EnglishCountryRegionName, city
having count(distinct salesordernumber) >= 5
order by [somme quantité] desc

-------------------------
-- C.1 Je souhaite calculer le nombre de commandes (salesordernumber) par produits (englishproductname)
-- ainsi que le montant des ventes globales
-- Attention table Factinternetsales
-- Résultat : 158
Select EnglishProductName, DimProduct.ProductKey
		, count(distinct SalesOrderNumber) as 'nombre de commandes',
	   sum(salesamount) as 'montant des ventes'
From FactInternetSales Join DimProduct ON DimProduct.ProductKey= FactInternetSales.productkey
Group by EnglishProductName, DimProduct.ProductKey

-- C.2 Suite de C.1
-- Conditions suivantes : quand le poids du produit est renseigné (weight)
-- et quand le nombre de ventes est supérieur à 200
-- Résultat : 22
Select EnglishProductName, DimProduct.ProductKey
		, count(distinct SalesOrderNumber) as 'nombre de commandes',
	   sum(salesamount) as 'montant des ventes'
From FactInternetSales Join DimProduct ON DimProduct.ProductKey= FactInternetSales.productkey
where weight is not null
Group by EnglishProductName, DimProduct.ProductKey
having count(distinct SalesOrderNumber) > 200

-- D.1 Je souhaite afficher les numéro de vente ainsi que les noms des clients
-- Résultat : 27 659
select distinct salesordernumber, DimCustomer.LastName
From FactInternetSales join DimCustomer
		on FactInternetSales.customerkey = DimCustomer.customerkey

-- avec alias
select salesordernumber, c.LastName
From FactInternetSales f join DimCustomer c
		on f.customerkey = c.customerkey

-- D.2 Je souhaite afficher les noms et prénoms des clients
-- Attention à bien prendre en compte tous les clients !
-- Résultat : 18 484
select distinct DimCustomer.customerkey, lastname, FirstName
From FactInternetSales join DimCustomer
		on FactInternetSales.customerkey = DimCustomer.customerkey

-- avec alias
select distinct c.customerkey, lastname, FirstName
From FactInternetSales f join DimCustomer c
		on f.customerkey = c.customerkey

-- D.3 Suite du D.2
-- Je souhaite afficher seulement ceux ayant effectué une commande en 2013
-- Résultat : 17 429
select distinct DimCustomer.customerkey, lastname, FirstName
From FactInternetSales join DimCustomer
		on FactInternetSales.customerkey = DimCustomer.customerkey
where year(OrderDate) = 2013

-- avec alias
select distinct c.customerkey, lastname, FirstName 
From FactInternetSales f join DimCustomer c
		on f.customerkey = c.customerkey
where year(OrderDate) = 2013

-- D.4 Suite du D.3
-- Je souhaite afficher seulement ceux qui ont eu une promotion 
-- (enlever 'aucune remise') avec le nom de la promotion (frenchpromotionname)
-- Résultat : 1 701
select distinct DimCustomer.customerkey, lastname, FirstName, FrenchPromotionName
From FactInternetSales join DimCustomer
		on FactInternetSales.customerkey = DimCustomer.customerkey
						join dimpromotion
		on dimpromotion.promotionkey = FactInternetSales.PromotionKey
where year(OrderDate) = 2013 and FrenchPromotionName <> 'Aucune remise'

-- avec alias
select distinct c.customerkey, lastname, FirstName, pm.FrenchPromotionName
From FactInternetSales f join DimCustomer c
		on f.customerkey = c.customerkey
						 join dimpromotion pm
		on pm.promotionkey = f.PromotionKey
where year(OrderDate) = 2013 and FrenchPromotionName <> 'Aucune remise'

-- D.5 Suite du D.4
-- Je souhaite calculer combien de clients différents il y a eu par type de promotion en 2013
-- Résultat : 3 lignes (avec 1668 pour Remise sur quantité)
select FrenchPromotionName, count (distinct DimCustomer.customerkey) as 'nombre clients'
From FactInternetSales join DimCustomer
		on FactInternetSales.customerkey = DimCustomer.customerkey
						join dimpromotion
		on dimpromotion.promotionkey = FactInternetSales.PromotionKey
where year(OrderDate) = 2013 and FrenchPromotionName <> 'Aucune remise'
group by FrenchPromotionName

-- avec alias
select pm.FrenchPromotionName, 
		count (distinct c.customerkey) as 'nombre clients'
From FactInternetSales f join DimCustomer c on f.customerkey = c.customerkey
						 join dimpromotion pm on pm.promotionkey = f.PromotionKey
where year(OrderDate) = 2013 and FrenchPromotionName <> 'Aucune remise'
group by pm.FrenchPromotionName

-- D.6 Suite du D.5
-- Je souhaite calculer la moyenne des coûts et le max des ventes
-- Toujours par type de promotion mais aussi par mois et trimestre de vente
-- Trier les résultats par la max des ventes (desc) et par le nombre de clients (desc)
-- Résultat : 18 lignes
select FrenchPromotionName, 
		month(orderdate) as mois, 
		datepart(QUARTER,orderdate) as trimestre,
		count (distinct DimCustomer.customerkey) as 'nombre clients',
		avg(TotalProductCost) as 'moyenne des coûts',
		max(salesamount) as 'max des ventes'
From FactInternetSales join DimCustomer
		on FactInternetSales.customerkey = DimCustomer.customerkey
						join dimpromotion
		on dimpromotion.promotionkey = FactInternetSales.PromotionKey
where year(OrderDate) = 2013 and FrenchPromotionName <> 'Aucune remise'
group by FrenchPromotionName, month(orderdate), datepart(QUARTER,orderdate)
Order by [max des ventes] desc, [nombre clients] desc

-- avec alias
use AdventureWorksDW2019
select pm.FrenchPromotionName, 
		month(orderdate) as mois, 
		datepart(QUARTER,orderdate) as trimestre,
		count (distinct c.customerkey) as 'nombre clients',
		avg(f.TotalProductCost) as 'moyenne des coûts',
		max(salesamount) as 'max des ventes'
From FactInternetSales f join DimCustomer c on f.customerkey = c.customerkey
						 join dimpromotion pm on pm.promotionkey = f.PromotionKey
where year(OrderDate) = 2013 and FrenchPromotionName <> 'Aucune remise'
group by pm.FrenchPromotionName, month(orderdate), datepart(QUARTER,orderdate)
Order by [max des ventes] desc, [nombre clients] desc

-- D.7 suite du D.6 
-- quand le nombre de clients est supérieur à 50
-- Résultat : 12 lignes
select FrenchPromotionName,
		month(orderdate) as mois, 
		datepart(quarter, orderdate) as trimestre,
		count (distinct DimCustomer.customerkey) as 'nombre clients',
		avg(TotalProductCost) as 'moyenne des coûts',
		max(salesamount) as 'max des ventes'
From FactInternetSales join DimCustomer
		on FactInternetSales.customerkey = DimCustomer.customerkey
						join dimpromotion
		on dimpromotion.promotionkey = FactInternetSales.PromotionKey
where year(OrderDate) = 2013 and FrenchPromotionName <> 'Aucune remise'
group by FrenchPromotionName, month(orderdate), datepart(quarter, orderdate)
having count (distinct DimCustomer.customerkey) > 50
order by [max des ventes] desc, [nombre clients] desc
