use AdventureWorksDW2019

-- A.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 distinct FrenchPromotionName
From DimPromotion JOIN FactInternetSales
	ON DimPromotion.promotionkey = FactInternetSales.promotionkey

-- avec
select distinct FrenchPromotionName
from DimPromotion pp join FactInternetSales i on pp.promotionkey=i.PromotionKey

-- A.2 Afficher tous les noms des promotions (en français) avec ventes ou non
-- ainsi que le numéro de commande
-- 29 239 lignes de résultat
select distinct FrenchPromotionName, SalesOrderNumber
From DimPromotion LEFT JOIN FactInternetSales
	ON DimPromotion.promotionkey = FactInternetSales.promotionkey

-- avec
select distinct FrenchPromotionName, SalesOrderNumber
from DimPromotion pp left join FactInternetSales i 
				on pp.promotionkey=i.PromotionKey

-- A.3 Quelles sont les noms des promotions (en français) pour lesquelles, il n'y a jamais eu de ventes ?
-- 12 remises
select distinct FrenchPromotionName, SalesOrderNumber
From DimPromotion LEFT JOIN FactInternetSales
	ON DimPromotion.promotionkey = FactInternetSales.promotionkey
where FactInternetSales.promotionkey is null

-- avec
select distinct FrenchPromotionName
from DimPromotion pp left join FactInternetSales i 
				on pp.promotionkey=i.PromotionKey
where i.promotionkey is null

-- A.4 suite de A.3
-- Je souhaite aussi afficher les colonnes date de début et date de fin de chaque promotion !
-- Seulement quand le frenchpromotioncategory est égal à Revendeur
-- Résultat : 10
select distinct FrenchPromotionName, SalesOrderNumber, 
 DimPromotion.StartDate, DimPromotion.EndDate
From DimPromotion LEFT JOIN FactInternetSales
	ON DimPromotion.promotionkey = FactInternetSales.promotionkey
where FactInternetSales.promotionkey is null
and FrenchPromotionCategory = 'Revendeur'

-- avec
select distinct FrenchPromotionName, pp.FrenchPromotionCategory, pp.StartDate, pp.EndDate
from DimPromotion pp left join FactInternetSales i 
				on pp.promotionkey=i.PromotionKey
where i.promotionkey is null and FrenchPromotionCategory = 'Revendeur'

-- A.5 suite de A.4
-- Quand le frenchpromotionname contient le mot remise
-- et quand la date de fin de promotion n'est pas en 2012
-- Résultat : 5
select distinct FrenchPromotionName, SalesOrderNumber, 
 DimPromotion.StartDate, DimPromotion.EndDate
From DimPromotion LEFT JOIN FactInternetSales
	ON DimPromotion.promotionkey = FactInternetSales.promotionkey
where FactInternetSales.promotionkey is null
and FrenchPromotionCategory = 'Revendeur'
and frenchpromotionname like '%remise%'
		and year(enddate) <> 2012

-- avec
select distinct FrenchPromotionName, pp.FrenchPromotionCategory, pp.StartDate, pp.EndDate
from DimPromotion pp left join FactInternetSales i 
				on pp.promotionkey=i.PromotionKey
where i.promotionkey is null and FrenchPromotionCategory = 'Revendeur'
		and frenchpromotionname like '%remise%'
		and year(enddate) <> 2012

-------------
-- B.1 Quels sont les produits qui ont déjà été commandé ?
-- FactInternetSales et Dimproduct
-- 158 produits commandé
select distinct Dimproduct.productkey, Dimproduct.EnglishProductName
from Dimproduct join FactInternetSales
			on Dimproduct.productkey = FactInternetSales.productkey

-- avec
select distinct p.productkey, p.EnglishProductName
from Dimproduct p join FactInternetSales i
			on P.productkey = i.productkey

-- B.2 Afficher tous les produits avec ou sans commandes :
-- Afficher le productkey de la table produit, le nom anglais du produit
-- et le numéro de produit (productkey) de la table commande
-- Résultat : 606
select distinct Dimproduct.productkey, Dimproduct.EnglishProductName,
		FactInternetSales.productkey
from Dimproduct LEFT join FactInternetSales
			on Dimproduct.productkey = FactInternetSales.productkey

-- avec
select distinct p.productkey, p.EnglishProductName, I.productkey
from Dimproduct p left join FactInternetSales i
			on P.productkey = i.productkey

-- B.3 Quels sont les produits qui n'ont jamais été commandé ? (id + nom)
-- Résultat : 448 
select distinct Dimproduct.productkey, Dimproduct.EnglishProductName,
		FactInternetSales.productkey
from Dimproduct LEFT join FactInternetSales
			on Dimproduct.productkey = FactInternetSales.productkey
where FactInternetSales.ProductKey IS NULL

-- avec
select distinct p.productkey, p.EnglishProductName, p.Status
from Dimproduct p left join FactInternetSales i
			on P.productkey = i.productkey
where i.ProductKey IS NULL

-- B.4 Suite du B.3 Produits non commandé qui sont toujours en vente (status du produit : current)
-- Résultat : 304 
select distinct Dimproduct.productkey, Dimproduct.EnglishProductName
from Dimproduct LEFT join FactInternetSales
			on Dimproduct.productkey = FactInternetSales.productkey
where FactInternetSales.ProductKey IS NULL
and Dimproduct.Status ='Current'

-- avec
select distinct p.productkey, p.EnglishProductName
from Dimproduct p left join FactInternetSales i
			on P.productkey = i.productkey
where i.ProductKey IS NULL and P.Status ='Current'

-- B.5 Suite du B.4 - Compter le nombre de produits concernés par couleurs et modelname
-- Résultat : 51 
select count(Dimproduct.productkey) as 'nombre de produits', color, modelname
from Dimproduct LEFT join FactInternetSales
			on Dimproduct.productkey = FactInternetSales.productkey
where FactInternetSales.ProductKey IS NULL
and Dimproduct.Status ='Current'
Group by color, modelname

-- avec
select count(p.productkey) as 'nombre de produits', color, modelname
from Dimproduct p left join FactInternetSales i
			on P.productkey = i.productkey
where i.ProductKey IS NULL and P.Status ='Current'
Group by color, modelname

-- B.6 Suite du B.5  - avec des conditions
-- quand la couleur n'est pas NA
-- que le nombre de produits n'est pas égal à 1 ni à 3
-- que le modelname n'est pas null et ne contient pas 'mountain'
-- puis trier par nombre de produits descendant
-- Résultat : 8
select count(Dimproduct.productkey) as 'nombre de produits', color, modelname
from Dimproduct LEFT join FactInternetSales
			on Dimproduct.productkey = FactInternetSales.productkey
where FactInternetSales.ProductKey IS NULL
and Dimproduct.Status ='Current'
and color <> 'NA' and modelname is not null and modelname not like '%mountain%'
Group by color, modelname
having count(Dimproduct.productkey) not in (1, 3)
order by [nombre de produits] desc

-- avec
select count(p.productkey) as 'nombre de produits', color, modelname
from Dimproduct p left join FactInternetSales i
			on P.productkey = i.productkey
where i.ProductKey IS NULL and P.Status ='Current'
	and color <> 'NA' and modelname is not null and modelname not like '%mountain%'
Group by color, modelname
having count(p.productkey) not in (1, 3)
order by [nombre de produits] desc

---------------------------------------
-- C.1 Aide avant de passer à l'exo suivant:  j'affiche tous les produits de la table produit avec leur catégories 
-- et sous-catégories associées (correspondance ou non, je veux tous les produits)
-- toujours en vente (status courant)
-- 406 produits au total dont les 209 premiers sans sous-catégorie ! 
select distinct DimProductCategory.ProductCategoryKey, EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, 
			EnglishProductName
			from dimproduct 
					left Join DimProductSubcategory on DimProductSubcategory.ProductSubcategoryKey=dimproduct.ProductSubcategoryKey
					left join DimProductCategory on DimProductCategory.ProductCategoryKey=DimProductSubcategory.ProductCategoryKey
where dimproduct.Status ='Current'

-- avec alias 
select distinct c.ProductCategoryKey, EnglishProductCategoryName, s.EnglishProductSubcategoryName, EnglishProductName
			from dimproduct p 
					left Join DimProductSubcategory s on s.ProductSubcategoryKey=p.ProductSubcategoryKey
					left join DimProductCategory c on c.ProductCategoryKey=s.ProductCategoryKey
where P.Status ='Current'

-- C.2 Afficher tous les produits n'ayant pas été vendus (table FactInternetSales necessaire)
-- on souhaite également afficher les noms des catégories correspondantes à ces produits-là
-- S'aider du C.1 pour rajouter la notion de catégorie :)
-- Résultat : 304 (448 si tous status confondus)
select distinct Dimproduct.productkey, Dimproduct.EnglishProductName, DimProductCategory.EnglishProductCategoryName
from Dimproduct left join FactInternetSales -- tous les produits
			on Dimproduct.productkey = FactInternetSales.productkey
					left Join DimProductSubcategory on DimProductSubcategory.ProductSubcategoryKey=Dimproduct.ProductSubcategoryKey
					-- tous les produits même si pas de correspondance avec nouvelle table
					left join DimProductCategory on DimProductCategory.ProductCategoryKey=DimProductSubcategory.ProductCategoryKey
where FactInternetSales.productkey IS NULL and Dimproduct.Status ='Current'

-- avec alias 
select distinct p.productkey, p.EnglishProductName, c.EnglishProductCategoryName
from Dimproduct p left join FactInternetSales i
			on P.productkey = i.productkey
					left Join DimProductSubcategory s on s.ProductSubcategoryKey=p.ProductSubcategoryKey
					left join DimProductCategory c on c.ProductCategoryKey=s.ProductCategoryKey
where i.productkey IS NULL and P.Status ='Current'

-- 606 produits au total dont 406 avec statut courant
-- 158 vendus mais seulement 102 vendus avec un statut courant
-- 448 pas vendus mais seulement 304 avec un statut courant

