sql challenge.Rmd

---
title: "R Notebook"
output: html_notebook
---

```{r setup}
#sql chunk, db connection not available
library(DBI)
db = dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")
knitr::opts_chunk$set(connection = "db")
```

### Scenario 1

- Comparison of total sales from the fiscal quarters of 2008 to the fiscal quarters of 2007
- Fiscal year is from June to July

**Test Example**

```{sql}
SELECT
a.SalesPersonID,
c.LastName,
/*add 6 months to orderdate to get fiscal year from July to June*/
YEAR(DATEADD(MONTH,6,a.OrderDate)) as FY, /*result is a year*/
DATEPART(QUARTER,DATEADD(MONTH,6,a.OrderDate)) as FQ, /*result is a number of quarter*/
SUM(a.SubTotal) as FQSales,  /*sum of sales*/
SUM(b.SubTotal) as SameSalesFQLast,  /*sum of sales*/
SUM(a.SubTotal)-SUM(b.SubTotal) as Change,  /*difference between sum of sales*/
((SUM(a.SubTotal)-SUM(b.SubTotal)) /SUM(b.SubTotal)) * 100 as pct_change  /*percentage*/
/*2018-2017/2017*/
 /*join two same tables to itself and specify dates in tables*/
  
FROM [Sales].[SalesOrderHeader] a left join [Sales].[SalesOrderHeader] b
ON a.SalesPersonID=b.SalesPersonID
/*quarters in both tables are same*/
AND DATEPART(QUARTER,DATEADD(MONTH,6,b.OrderDate))=DATEPART(QUARTER,DATEADD(MONTH,6,a.OrderDate))
/*table b is in 2008 and table a is in 2007*/
AND DATEPART(YEAR,DATEADD(MONTH,6,b.OrderDate))=DATEPART(YEAR,DATEADD(MONTH,6,a.OrderDate))-1
INNER JOIN Person.[Person] c 
ON c.BusinessEntityID=a.SalesPersonID
WHERE DATEPART(YEAR,DATEADD(MONTH,6,a.OrderDate))=2008
GROUP BY 1,2,3,4
```

**Real Example**

```{sql}
SELECT
DATEPART(YEAR,DATEADD(MONTH,6,a.impression_dt)) as FY, /*result is a year*/
DATEPART(QUARTER,DATEADD(MONTH,6,a.impression_dt)) as FQ, /*result is a number of quarter*/
COUNT(a.sudid) as FQSales,  /*sum of sales*/
COUNT(b.sudid) as SameSalesFQLast,  /*sum of sales*/
COUNT(a.sudid)-COUNT(b.sudid) as Change,  /*difference between sum of sales*/
((COUNT(a.sudid)-COUNT(b.sudid))/COUNT(b.sudid))*100 as pct_change  /*percentage*/

FROM fact_impressions a left join fact_impressions b
  ON a.sudid = b.sudid
  AND DATEPART(QUARTER,DATEADD(MONTH,6,b.impression_dt))=DATEPART(QUARTER,DATEADD(MONTH,6,a.impression_dt))
  AND DATEPART(YEAR,DATEADD(MONTH,6,b.impression_dt))=DATEPART(YEAR,DATEADD(MONTH,6,a.impression_dt))-1
WHERE DATEPART(YEAR,DATEADD(MONTH,6,a.impression_dt))=2018
  AND DATEPART(QUARTER, DATEADD(MONTH,6,a.impression_dt)) = 4
GROUP BY 1,2
```

### Scenario 2

2/22 promotion, orders subtotaling at least $\$2,000$ ship for $\$0.22$. The strategy assumes that freight losses will be offset by gains from higher value orders. Orders between $1,700 and $\$2,000$ will likely boost to $\$2,000$ as customers feel compelled to take advantage of bargain freight pricing

- Potential Promotional Effect
- Potential order gains
- Potential freight losses
- Overall net gain/loss

**Test Example**

```{sql}
SELECT 
  a.SalesOrderID, --Order ID
  a.OrderDate, -- Date
  d.Name AS Ship_to_State, -- State
  a.SubTotal AS Historical_Order_Total, -- Prior to discount
  a.Freight AS Historical_Freight, -- Prior to discount
  CASE 
    WHEN a.SubTotal>=1700 and a.SubTotal<2000 THEN 'Increase order to 2000 and pay 22 cents as Freight'
    WHEN a.SubTotal>=2000 THEN 'No Order Change but pay 22 cents as Freight' 
    ELSE 'No order change and pay historical freight' 
    END as Promo_Effect,
  CASE
    WHEN a.SubTotal>=1700 and a.SubTotal<2000 THEN 2000 - a.SubTotal 
    ELSE 0 
    END as Potential_Order_Gain,
  CASE 
    WHEN a.SubTotal>=1700 THEN 0.22 else a.Freight END  - a.Freight as PotentialFreightoss,
  (CASE 
    WHEN a.SubTotal>=1700 and a.SubTotal<2000 then 2000 -a.SubTotal 
    ELSE 0 
    END + 
      CASE 
        WHEN a.SubTotal>=1700 THEN 0.22 ELSE a.Freight 
        END )- 
          a.Freight AS PromoNetGain_Loss
FROM [Sales].[SalesOrderHeader] a
  JOIN Person.BusinessEntityAddress b ON a.ShipToAddressID=b.AddressID
  JOIN Person.[Address] c ON b.AddressID = c.AddressID
  JOIN Person.StateProvince d ON c.StateProvinceID=d.StateProvinceID
WHERE d.Name = 'California' 
  AND DATEPART (YEAR, DATEADD (MONTH, 6,a.OrderDate)) = 2008
```


### Scenario 12

You are asked to provide frequent updates about the product inventory.


**Test Example**

```{sql}
-- final table includes quantity of each product and total of all products
SELECT
  b.name,
  count(distinct a.ProductID) AS ProductCount,
  sum(a.Quantity) as ProductQty
FROM [Production].[ProductInventory] a
JOIN [Production].[Location] b ON a.LocationID=b.LocationID
GROUP BY (b.name)

UNION

SELECT 
  'Total' ,
  count(distinct a.ProductID) AS ProductCount,
  SUM(a.Quantity) as ProductQty
FROM [Production].[ProductInventory] a
```

### Scenario 13

Identify the employee or group of employees with the greatest number of vacation hours. Since many HR files are indexed by National ID Number, include the last four digits with your output.

**Test Example**

```{sql}
WITH MaxVacHrs AS 
	(SELECT MaxVacHrs = MAX (VacationHours) FROM HumanResources.Employee)
	
SELECT 
	RIGHT (N1.NationalIDNumber, 4) AS NationalID,
	N2.FirstName,
	N2.LastName,
	N1.JobTitle,
	N1.VacationHours
FROM HumanResources.Employee N1
  INNER JOIN Person.Person N2 ON N1.BusinessEntityID = N2.BusinessEntityID
  INNER JOIN MaxVacHrs N3 ON N1.VacationHours = N3.MaxVacHrs
```

### Scenario 14

For each product ordered by the Purchasing Department in 2007, indicate the quantity ordered by order date.

```{sql}
SELECT 
  b.ProductID,
  c.Name,
  a.OrderDate,
  sum(b.OrderQty) as QTY
FROM [Purchasing].[PurchaseOrderHeader] a 
  JOIN [Purchasing].[PurchaseOrderDetail] b ON a.PurchaseOrderID=b.PurchaseOrderID 
  JOIN [Production].[Product] c ON b.ProductID=c.ProductID
WHERE DATEPART(YEAR, a.OrderDate)=2007
GROUP BY  1,2,3
ORDER BY sum(b.OrderQty) DESC
```

### Scenario 15

List of all product descriptions written in languages other than English.

```{sql}
SELECT
  a.ProductModelID,
  a.Name,
  c.Description,
  b.CultureID
FROM [Production].[ProductModel] a
  JOIN [Production].[ProductModelProductDescriptionCulture] b ON a.ProductModelID=b.ProductModelID 
  JOIN  [Production].[ProductDescription] c ON b.ProductDescriptionID=c.ProductDescriptionID
WHERE b.CultureID != 'en'
```

### Scenario 20

Provide address data about stores with main offices located in Toronto.

```{sql}
SELECT 
  d.Name AS StoreName,
  a.AddressLine1,
  a.AddressLine2,
  a.City,c.Name,
  a.PostalCode
FROM [Person].[Address] a JOIN [Person].[BusinessEntityAddress] b ON a.AddressID=b.AddressID 
  JOIN [Person].[StateProvince] c  ON a.StateProvinceID=c.StateProvinceID
  JOIN [Sales].[Store] d ON d.BusinessEntityID=b.BusinessEntityID
WHERE b.AddressTypeID=3 -- or d.name = 'Main Office'
  AND a.City='Toronto'
```

### Scenario 21

List of employees who are currently in the Marketing department and were hired prior to 2002 or later than 2004.

```{sql}
SELECT
  d.FirstName,
  d.LastName,
  c.JobTitle,
  c.BirthDate,
  c.MaritalStatus,
  a.StartDate AS Hire_Date
FROM [HumanResources].[EmployeeDepartmentHistory] a 
  JOIN [HumanResources].[Department] b ON a.DepartmentID=b.DepartmentID
  JOIN [HumanResources].[Employee] c ON c.BusinessEntityID=a.BusinessEntityID
  JOIN  [Person].[Person] d ON d.BusinessEntityID=a.BusinessEntityID
WHERE 
  (DATEPART(YEAR, a.StartDate)<2002 
    OR DATEPART(YEAR, a.StartDate)>2004) 
  AND b.Name='Marketing' 
  AND a.EndDate IS NULL
```

### Scenario 22

Who left that review?

```{sql}
SELECT
  a.ProductReviewID,
  a.ProductID,
  b.Name,
  a.ReviewerName,
  a.Rating,
  a.EmailAddress,
  c.BusinessEntityID -- will be returned as 0
FROM [Production].[ProductReview] a 
JOIN [Production].[Product] b ON a.ProductID=b.ProductID
LEFT JOIN [Person].[EmailAddress] c ON a.EmailAddress=c.EmailAddress
```

### Scenario 23

List of customers with phone numbers for all orders for shorts placed online after July 7, 2008.

```{sql}
SELECT
  a.SalesOrderID,
  a.OrderDate,
  c.Name,
  f.FirstName,
  f.LastName,
  g.PhoneNumber
FROM [Sales].[SalesOrderHeader] a 
  INNER JOIN [Sales].[SalesOrderDetail] b ON a.SalesOrderID=b.SalesOrderID
  INNER JOIN [Production].[Product] c ON b.ProductID=c.ProductID
  INNER JOIN [Sales].[Customer] e ON e.CustomerID=a.CustomerID
  INNER JOIN [Person].[Person] f ON e.PersonID=f.BusinessEntityID
  INNER JOIN [Person].[PersonPhone] g ON g.BusinessEntityID=f.BusinessEntityID
WHERE a.OrderDate>'2008-07-07' 
 AND a.OnlineOrderFlag=1 
 AND LOWER(c.Name) LIKE '%shorts%'
ORDER BY a.SalesOrderID
```

### Scenario 19

Display the most common reasons why products were scrapped through the manufacturing process.

```{sql}
WITH Temp AS 
	(SELECT
		ROW_NUMBER () OVER (PARTITION BY N1.ProductID 
												ORDER BY COUNT (N2.Name) DESC) AS rank, --name is the reason why it was scrapped
		N1.ProductID
		ProductName AS N2.Name
		COUNT (N2.Name) AS WorkOrderCount
		N3.Name AS ScrapReason
	FROM Production.WorkOrder N1
		INNER JOIN Production.Product N2 ON N1.ProductID = N2.ProductID
		INNER JOIN Production.ScrapReason N3 ON N1.ScrapReasonID = N3.ScrapReasonID
		GROUP BY N1.ProductID, N2.Name, N3.Name)

SELECT
	ProductID,
	ProductName,
	WorkOrderCount,
	ScrapReason
FROM Temp
WHERE rank = 1
ORDER BY WorkOrderCount DESC
```

### Solution 18

- minimium historical cost
- maximum historical cost
- historical cost variablity - maximum minus minimum
- ranking of all historical cost variablities - rank of 1 reflects the product ID exhibiting the greatetist historical cost variability

```{sql}
-- ROW_NUMBER – Returns the sequential number of a row within a partition of a result set, without any gaps in the ranking. ... The rank of a row is one plus the number of ranks that come before the row in question.

--DENSE_RANK – Returns the rank of rows within the partition of a result set, without any gaps in the ranking

SELECT 
	N1.ProductID
	N2.Name AS ProductName
	N3.Name AS SubCategory
	MIN (N1.StandardCost) AS MinCost
  MAX (N1.StandardCost) AS 	MaxCost
	MAX (N1.StandardCost) - MIN (N1.StandardCost) AS CostVar

	CASE 
			WHEN MAX (N1.StandardCost) - MIN (N1.StandardCost) = 0 THEN 0 
			ELSE DENSE_RANK () OVER (ORDER BY MAX (N1.StandardCost) - MIN     (N1.StandardCost) DESC) 
			END AS CostVarRank -- gives a number to each row based on difference in standard cost	

FROM Production.ProductCostHistory N1
  INNER JOIN Production.Product N2 ON N1.ProductID = N2.ProductID
  INNER JOIN Production.ProductSubcategory N3 ON N2.ProductSubcategoryID = N3.ProductSubcategoryID
GROUP BY N1.ProductID, N2.[Name], N3.[Name]
ORDER BY MAX (N1.StandardCost) - MIN (N1.StandardCost) DESC
```

### Solution 17

Identify stores in which the last order date was at least 12 months ago (today's date is October 7, 2008)

```{sql}
WITH Stores AS

(SELECT
	N3.BusinessEntityID,
	N1.CustomerID,
	N2.StoreID,
	N3.Name AS StoreName
	MAX (N1.OrderDate) AS LastOrderDate
	DATEDIFF (MONTH, MAX (N1.OrderDate), '2008-10-07') AS MonthsSinceLastOrder
FROM Sales.SalesOrderHeader N1
  INNER JOIN Sales.Customer N2 ON N1.CustomerID = N2.CustomerID
  INNER JOIN Sales.Store N3 ON N2.StoreID = N3.BusinessEntityID
GROUP BY 1,2,3,4)

SELECT * 
FROM Stores
WHERE MonthsSinceLastOrder > = 12
ORDER BY MonthsSinceLastOrder DESC
```

### Solution 16

summary table that shows, by territory, the percentage of orders placed online in comparison to orders not placed online

```{sql}
SELECT
	TerritoryID,
	COUNT(*) AS TotalOrders,
	CONVERT(VARCHAR(50), -- to add % after a calculation
	  ROUND( -- to round the calculation to the whole number
	    (CONVERT(FLOAT, -- to calculate with float 
	      SUM(CASE -- calculation itself
	        WHEN OnlineOrderFlag = 1 
	        THEN 1 
	        ELSE 0 
	        END)) /COUNT(*))* 100
	   ,0)
	  ) 
	 + '%' AS PercOnline

	CONVERT(VARCHAR(50),
		ROUND(
			(CONVERT(FLOAT,
				SUM(CASE 
				  WHEN OnlineOrderFlag = 0 
				  THEN 1 ELSE 0 
				  END)) /COUNT(*))* 100
			,0)
			)
	+ '%' AS PercOffline
FROM Sales.SalesOrderHeader
GROUP BY TerritoryID
ORDER BY TerritoryID
```


### Solution 11

max tax rate in each country

```{sql}
SELECT
	N3.Name AS Country,
	MAX (N1.TaxRate) AS MaxTaxRate
FROM Sales.SalesTaxRate N1
  INNER JOIN Person.StateProvince N2 ON N1.StateProvinceID = N2.StateProvinceID
  INNER JOIN Person.CountryRegion N3 ON N2.CountryRegionCode = N3.CountryRegionCode
GROUP BY 1
```

### Solution 4

compare sales from Tuesday to other days of the week; calculate average reveniue per order by day of week in 2008

```{sql}
SELECT 
	DATENAME (WEEKDAY, OrderDate) AS DayCategory,
	SUM (Subtotal) AS Revenue,
	COUNT (*) AS Orders,
	SUM (Subtotal) / COUNT (*) AS RevenuePerOrder
FROM Sales.SalesOrderHeader
WHERE DATEPART(YEAR, OrderDate) = 2008
AND OnlineOrderFlag = 0
GROUP BY 1
ORDER BY RevenuePerOrder DESC
```

### Scenario 24

Email preferences in table Person.Person:

- The value 0 indicates "Contact does not wish to receive e-mail promotions"
- The value 1 indicates "Contact does wish to receive e-mail promotions from Adventure Works"
- The value 2 indicates "Contact does wish to receive e-mail promotions from Adventure Works and selected partners"

Count number of email addresses per email preference

```{sql}
WITH Email AS
(SELECT
	N1.BusinessEntityID,
	N1.EmailAddress,
	CASE 
		WHEN N2.EmailPromotion = 0 THEN 
						'Contact does not wish to receive e-mail promotions'
		WHEN N2.EmailPromotion = 1 THEN 
						'Contact does wish to receive e-mail promotions from AdventureWorks'
		WHEN N2.EmailPromotion = 2 THEN 
						'Contact does wish to receive e-mail promotions from AdventureWorks and selected partners' 
	END AS EmailPreference
FROM Person.EmailAddress N1
LEFT JOIN Person.Person N2 ON N1.BusinessEntityID = N2.BusinessEntityID
WHERE N2.PersonType = 'IN')

SELECT 
	EmailPref, COUNT (*)
FROM Email
GROUP BY EmailPref
ORDER BY COUNT (*) DESC
```

### Scenario 25

Which two sales territories were top performers during fiscal years 2006 and 2007

```{sql}
WITH TerritoryRank AS

(SELECT 
	DATEPART(YEAR, DATEADD (MONTH, 6, N1.OrderDate)) AS FY, -- adjust orderdate to fiscal years, from July to June
	N2.Name AS Territery,
	SUM (N1.SubTotal) AS Revenue,
	DENSE_RANK () OVER (PARTITION BY YEAR (DATEADD (MONTH, 6, N1.OrderDate))   
											ORDER BY SUM (N1.Subtotal) DESC) AS TerritoryRank
FROM Sales.SalesOrderHeader N1
  INNER JOIN Sales.SalesTerritory N2 ON N1.TerritoryID = N2.TerritoryID
GROUP BY 1,2)

SELECT * 
FROM TerritoryRank 
WHERE FY IN (2006, 2007) AND TerritoryRank IN (1, 2) 
ORDER BY FY, Territory$Rank
```

### Scenario 26

Rank commission percentages by sales person
If commission percentages are equal among sales people, rank by Bonus in descending order

```{sql}
SELECT
	BusinessEntityID,
	CommissionPct,
	Bonus,
	DENSE_RANK () OVER (ORDER BY CommissionPct DESC, Bonus DESC) AS Rank
FROM Sales.SalesPerson
ORDER BY CommissionPct DESC
```

### Scenario 27

Part 1:

Report the number of work orders by productID, order the results from the greates number of work orders to the least

Part 2:
Report the number of work orders by product name. Order your results from the greatest number of work orders to the least.

```{sql}
-- Part I
SELECT
	ProductID,
	COUNT (*) AS WorkOrders
FROM Production.WorkOrder
GROUP BY ProductID
ORDER BY COUNT (*) DESC

-- Part II
SELECT
	N2.Name AS ProductName,
	COUNT (*) AS WorkOrders
FROM Production.WorkOrder N1
INNER JOIN Production.Product N2 ON N1.ProductID = N2.ProductID
GROUP BY 1
ORDER BY 2 DESC
```

### Scenario 28

Part 1:

Today is May 24, 2008. Using only revenue information from May 1 through May 23, estimate revenue for the whole month of May.

Output: No of days in month so far,Total Revenue in month so far,Revenue per day for month so far,Monthly Revenue trended for all of May

Part 2:

the actual revenue information
```{sql}
-- declare is not available in redshift, I can use CTE instead
DECLARE @StartDate DATE = '2008-05-01'
DECLARE @EndDate DATE = '2008-05-23'

-- Part I: 
SELECT
	DATEDIFF (day, @StartDate, @EndDate) + 1 AS DaysInMonthSoFar,	-- difference between start and end date in days
	SUM (SubTotal) AS RevenueInMonthSoFar, -- total revenue so far
	SUM (SubTotal / DATEDIFF (day, @StartDate, @EndDate) + 1) AS RevPerDayforMonthSoFar, -- revenue per day
  DAY (EOMONTH (@StartDate)) AS DaysInMonth -- end of month of start date, taking only day from the final format

  SUM (SubTotal) / (DATEDIFF (day, @StartDate, @EndDate) + 1) -- average per day 
								* DAY (EOMONTH (@StartDate)) AS MonthlyRevTrended -- days in month 

FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @StartDate AND @EndDate


-- Part II:
SELECT
	SUM (SubTotal) / DAY (EOMONTH (@StartDate)) AS ActualPerDay
	SUM (Subtotal) AS ActualRev
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @StartDate AND EOMONTH (@EndDate)
```

### Scenario 29

Separate the names from the domains

```{sql}
-- charindex returns the position
-- LoginID (for example, adventure-works\ken0)
-- Domain (for example, adventure-works)
-- Username (for example ken0)
SELECT
	BusinessEntityID,
	LoginID
	LEFT (LoginID, CHARINDEX ('\', LoginID, 1) - 1) AS Domain -- position of \ -1 is length to be taken from left
	RIGHT (LoginID, LEN (LoginID) - CHARINDEX ('\', LoginID, 1)) AS Username
FROM HumanResources.Employee
ORDER BY BusinessEntityID
```

### Scenario 30

Management will review the current distribution of labor by shift within the Production department.

```{sql}
SELECT
	N2.Name AS DepartmentName
	N3.Name AS ShiftName
	COUNT (*) AS Employees
FROM HumanResources.EmployeeDepartmentHistory N1
  INNER JOIN HumanResources.Department N2 ON N1.DepartmentID = N2.DepartmentID
  INNER JOIN HumanResources.[Shift] N3 ON N1.ShiftID = N3.ShiftID
WHERE N2.Name = 'Production'
	AND N1.EndDate IS NULL
GROUP BY 1,2
ORDER BY 1,2
```

### Scenario 31

Labels S => size Small
Labels M => size Medium
Labels L => size Large
Labels XL => size Extra Large

Part 1

Determine if the variety of labels is sufficient to cover all alphasized products. For example, since 2XL labels do not exist, no label could be applied to a 2XL product. If a 2XL product existed, the variety of labels would be insufficient

Part 2

Suppose, 1000 labels are available in each size. Calculate the number of additional labels needed to cover all the relevant products in the inventory.

```{sql}
-- Part I
-- Is there a product without dedicated size?
SELECT DISTINCT Size
FROM Production.Product
WHERE ISNUMERIC (Size) = 0 
	AND Size IS NOT NULL
	

-- The variety of stickers is appropriate for assignment to the company's products. 


-- Part II
SELECT 
	N1.Size
	SUM (N2.Quantity) AS CurrentQuantity,
	CASE
								WHEN SUM (N2.Quantity) - 1000 < 0 
									THEN 0 -- not needed
								ELSE SUM (N2.Quantity) - 1000 END AS AdditLabelsNeeded

FROM Production.Product N1
  INNER JOIN Production.ProductInventory N2 ON N1.ProductID = N2.ProductID
WHERE ISNUMERIC (N1.Size) = 0
	AND N1.Size IS NOT NULL 
GROUP BY N1.Size 
```

### Scenario 52

For each employee, report the latest pay rate and the pay rate prior to the latest rate

- Business ID, Previous rate, Latest rate, percent change

```{sql}
-- need to use self join as I am selecting data from one table
WITH Data AS 
	(SELECT
		BusinessEntityID,
		ROW_NUMBER () OVER (PARTITION BY BusinessEntityID 
												ORDER BY RateChangeDate DESC) AS PayRateNumber,
												-- number 1 will show the latest rate
		RateChangeDate,
		Rate
	FROM HumanResources.EmployeePayHistory)

SELECT
	N1.BusinessEntityID,
	N1.Rate AS LatestRate,
	N2.Rate AS RatePrior,
	CONVERT (VARCHAR (10), 
								(N1.Rate - N2.Rate) / N2.Rate * 100) + '%' AS PercentChange
FROM Data N1
LEFT JOIN Data N2 ON N1.BusinessEntityID = N2.BusinessEntityID 
						AND N2.PayRateNumber = 2 -- I need to filter only index 2 in my second table
WHERE N1.PayRateNumber = 1; -- I need to filter only index 1 in my first table
```

### Scenario 51

Part 1:

Pull a list of the previous excess inventory discounts 

```{sql}
SELECT
	SpecialOfferID,
	Type AS DiscountType,
	Description AS ,DiscountDescr,
	Category,
	StartDate,
	EndDate,
	DiscountPct
FROM Sales.SpecialOffer
WHERE Type = 'Excess Inventory' 
```

Part 2:

Add an additional column to the output from Part 1j. List the number of sales orders in which the discount was utilized.

```{sql}
SELECT a.SpecialOfferID,
  SpecialOfferID,
	Type AS DiscountType,
	Description AS ,DiscountDescr,
	Category,
	StartDate,
	EndDate,
	DiscountPct
  count(distinct b.SalesOrderID) as NumOrdersDiscApplied
FROM Sales.SpecialOffer a 
LEFT JOIN Sales.SalesOrderDetail b
ON a.SpecialOfferID=b.SpecialOfferID
WHERE a.Type='Excess Inventory'
```

### Scenario 50

Create a query about sales order reasons. 

When a sales order has only one reason, categorize as "Exclusive Reason". 

When a sales order has more than one reason, categorize as "Contributing Reason". 

Create a summary count of sales orders by reason name and your newly created ReasonInfluence column (Exclusive Reason and Contributing Reason.)

```{sql}
WITH Reasons AS
	(SELECT
		N1.SalesOrderID
		N2.Name AS ReasonName,
    CASE 
								WHEN COUNT (N3.SalesOrderID) >  1 
									THEN 'Contributing Reason'								
								WHEN COUNT (N3.SalesOrderID) = 1 
									THEN 'Exclusive Reason' END AS ReasonInfluence

		FROM Sales.SalesOrderHeaderSalesReason N1
		INNER JOIN Sales.SalesReason N2 ON N1.SalesReasonID = N2.SalesReasonID
		INNER JOIN Sales.SalesOrderHeaderSalesReason N3 ON N1.SalesOrderID = N3.SalesOrderID
		GROUP BY N1.SalesOrderID, N2.Name)

SELECT 
	ReasonName,
	ReasonInfluence,
	COUNT (*) AS SalesOrderCount
FROM Reasons
GROUP BY ReasonName, ReasonInfluence
ORDER BY ReasonName, SalesOrderCount DESC
```

### Scenario 49

Display by most recent due dates the top 10% of work orders in which the scrap rate was greater than 3%, ordered by most recent due date.

```{sql}
SELECT TOP 10 PERCENT N1.WorkOrderID,
	CAST (N1.DueDate AS DATE) AS DueDate,
	N3.Name AS ProdName,
	N2.Name AS ScrapReason,
	N1.ScrappedQty,
	N1.OrderQty,
	ROUND (N1.ScrappedQty / CONVERT (FLOAT, N1.OrderQty)* 100, 2) AS PercScrapped
FROM Production.WorkOrder N1
INNER JOIN Production.ScrapReason N2 ON N1.ScrapReasonID = N2.ScrapReasonID
INNER JOIN Production.Product N3 ON N1.ProductID = N3.ProductID
WHERE N1.ScrappedQty / CONVERT (FLOAT, N1.OrderQty) >  0.03
ORDER BY N1.DueDate DESC
```

#Scenario 48

Review sales quota changes from 2006 through 2007. Do not include information about sales people whjo were not assigned sales quotas during the start of 2007 or the end of 2007

```{sql}
-- self join
-- self join table on start date and end date to calculate percentage
SELECT DISTINCT
	N1.BusinessEntityID,
	N4.LastName AS SalesRepLastName,
	N2.SalesQuota AS Yr2006StartQuota, -- 2006
	N3.SalesQuota AS Yr2007EndQuota, -- 2007
	(N3.SalesQuota - N2.SalesQuota) / N2.SalesQuota * 100 AS %ChangeQuota
FROM Sales.SalesPersonQuotaHistory N1
INNER JOIN Sales.SalesPersonQuotaHistory N2 ON
	N1.BusinessEntityID = N2.BusinessEntityID 
	AND N2.QuotaDate = (SELECT MIN (QuotaDate) -- start of 2006
						FROM Sales.SalesPersonQuotaHistory 
						WHERE YEAR (QuotaDate) = 2006)
INNER JOIN Sales.SalesPersonQuotaHistory N3 ON -- end of 2007
	N1.BusinessEntityID = N3.BusinessEntityID
	AND N3.QuotaDate = (SELECT MAX (QuotaDate) 
						FROM Sales.SalesPersonQuotaHistory 
						WHERE YEAR (QuotaDate) = 2007)
INNER JOIN Person.Person AS N4 ON N1.BusinessEntityID = N4.BusinessEntityID
```

### Scenario 42

Find 5 most successful sale people to mentor the five least successful sales people, create a list of sales people to match with one another

- Success is measured by 2008 revenue
- Dates are based on OrderDate
- Do not consider tax and freight with revenue
- Ignore orders with no SalesPersonID

```{sql}
-- create two columns with row numbers, one desc and one asc, join data based on row number
WITH SalesGrouping AS
	(SELECT 
		SalesPersonID,
		SUM (SubTotal) AS SalesTotal,
		ROW_NUMBER () OVER (ORDER BY SUM (Subtotal) DESC) AS SalesRankSubTotalDESC,
		ROW_NUMBER () OVER (ORDER BY SUM (Subtotal)) AS SalesRankSubTotalASC,
	FROM Sales.SalesOrderHeader
	WHERE YEAR (OrderDate) = 2008 AND SalesPersonID IS NOT NULL
	GROUP BY SalesPersonID)

SELECT TOP 5
	N1.SalesPersonID AS SuccessSalesPersonID,
	N1.SalesTotal AS SuccessRevenue,
	N2.SalesPersonID AS UnsuccessSalesPersonID,
	N2.SalesTotal AS UnsuccessRevenue
FROM SalesGrouping N1
INNER JOIN SalesGrouping N2 ON N1.SalesRankSubTotalDESC = N2.SalesRankSubTotalASC
ORDER BY N1.SalesRankSubTotalDESC
```

### Scenario 41

Examine the prevalence of adventure-works.com e-mail addresses throught the company's database.

- Number of e-mail addresses containing the adventure-works.com
- Number of e-mail addresses not containing the adventure-works.com domain
- Total number of e-mail addresses

```{sql}
SELECT
	N2.PersonType,
	SUM (CASE WHEN N1.EmailAddress LIKE '%adventure-works%' THEN 1 ELSE 0 END) AS AWEmail,
  SUM (CASE WHEN N1.EmailAddress NOT LIKE '%adventure-works%' THEN 1 ELSE 0 END) AS NotAWEmail,
	COUNT (*) AS Total
FROM Person.EmailAddress N1
INNER JOIN Person.Person N2 ON N1.BusinessEntityID = N2.BusinessEntityID
GROUP BY N2.PersonType
ORDER BY Total DESC
```

### Scenario 40

Calculate the number of sales orders within each of the following revenue ranges:

1. $\$0 - \$100$
2. $\$100 - \$500$
3. $\$500 - \$1000$
4. $\$1000 - \$2500$
5. $\$2500 - \$5000$
6. $\$5000 - \$100008$
7. $\$10000 - \$50000$
8. $\$50000 - \$100000$
9.  $\$100000$

for year 2005
```{sql}
SELECT
	CASE
		WHEN TotalDue < 100 THEN 1
		WHEN TotalDue < 500 THEN 2
		WHEN TotalDue < 1000 THEN 3
    WHEN TotalDue < 2500 THEN 4
    WHEN TotalDue < 5000 THEN 5
    WHEN TotalDue < 10000 THEN 6			
    WHEN TotalDue < 50000 THEN 7
    WHEN TotalDue < 100000 THEN 8
  ELSE 9 
	END AS SortID,
	
	CASE
	  WHEN TotalDue < 100 THEN '0 - 100'
	  WHEN TotalDue < 500 THEN '100 - 500'
	  WHEN TotalDue < 1000 THEN '500 - 1,000'
	  WHEN TotalDue < 2500 THEN '1,000 - 2,500'
	  WHEN TotalDue < 5000 THEN '2,500 - 5,000'
	  WHEN TotalDue < 10000 THEN '5,000 - 10,000'
	  WHEN TotalDue < 50000 THEN '10,000 - 50,000'
	  WHEN TotalDue < 100000 THEN '50,000 - 100,000'   
	 ELSE '> 100,000'
   END AS SalesAmountCategory,
   
COUNT (*) AS Orders
FROM Sales.SalesOrderHeader
WHERE YEAR (OrderDate) = 2005
GROUP BY                                                                              
	CASE
		WHEN TotalDue < 100 THEN 1
		WHEN TotalDue < 500 THEN 2
		WHEN TotalDue < 1000 THEN 3
		WHEN TotalDue < 2500 THEN 4
		WHEN TotalDue < 5000 THEN 5
		WHEN TotalDue < 10000 THEN 6
		WHEN TotalDue < 50000 THEN 7
		WHEN TotalDue < 100000 THEN 8
		ELSE 9
	END
	,CASE
		WHEN TotalDue < 100 THEN '0 - 100'
		WHEN TotalDue < 500 THEN '100 - 500'
		WHEN TotalDue < 1000 THEN '500 - 1,000'
		WHEN TotalDue < 2500 THEN '1,000 - 2,500'
		WHEN TotalDue < 5000 THEN '2,500 - 5,000'
		WHEN TotalDue < 10000 THEN '5,000 - 10,000'
		WHEN TotalDue < 50000 THEN '10,000 - 50,000'
		WHEN TotalDue < 100000 THEN '50,000 - 100,000'
		ELSE '> 100,000'
	END
ORDER BY SortID
```

### Scenario 39

Part 1:

Show the quota, actual sales and percent to quota for each quarter and sales person

```{sql}
WITH SalesQuotaSummary (
SELECT
	N1.BusinessEntityID,
	N1.QuotaDate,
	N1.SalesQuota,
	CONVERT (DECIMAL (10,2), SUM (N2.SubTotal)) AS ActualSales,
	CONVERT (DECIMAL (10,2), CONVERT (FLOAT, SUM (N2.SubTotal)) / N1.SalesQuota) AS PercToQuota
FROM Sales.SalesPersonQuotaHistory N1
LEFT JOIN Sales.SalesOrderHeader N2 ON N1.BusinessEntityID = N2.SalesPersonID
								AND N2.OrderDate >= N1.QuotaDate 
								AND N2.OrderDate < DATEADD (MONTH, 3, N1.QuotaDate)
GROUP BY 1,2,3

SELECT *
FROM SalesQuotaSummary
ORDER BY BusinessEntityID, QuotaDate
```

Part 2:

Summarize results from Part 1 by sales person, by year

```{sql}
WITH SalesQuotaSummary (
SELECT
	N1.BusinessEntityID,
	N1.QuotaDate,
	N1.SalesQuota,
	CONVERT (DECIMAL (10,2), SUM (N2.SubTotal)) AS ActualSales,
	CONVERT (DECIMAL (10,2), CONVERT (FLOAT, SUM (N2.SubTotal)) / N1.SalesQuota) AS PercToQuota
FROM Sales.SalesPersonQuotaHistory N1
LEFT JOIN Sales.SalesOrderHeader N2 ON N1.BusinessEntityID = N2.SalesPersonID
								AND N2.OrderDate >= N1.QuotaDate 
								AND N2.OrderDate < DATEADD (MONTH, 3, N1.QuotaDate)
GROUP BY 1,2,3

SELECT
  BusinessEntityID,
	YEAR (QuotaDate) AS QuotaYear,
	SUM (SalesQuota) AS TotalQuota,
	SUM (ActualSales) AS TotalSales,
	CONVERT (DECIMAL (10,2), CONVERT (FLOAT, SUM (ActualSales)) / SUM (SalesQuota)) AS TotalPercToQuota
	CONVERT (DECIMAL (10,2), AVG (PercToQuota)) AS AvgQrtlyPercToQuota
FROM SalesQuotaSummary
ORDER BY BusinessEntityID, QuotaDate
```

### Scenario 38

Calculate the profit margins of bike models

Profit margin is based on the percent difference between ListPrice and StandardCost
Only consider bike models currently sold

```{sql}
SELECT
	N1.ProductModelID
	N4.Name AS ProductName,
	CONVERT (DECIMAL(10,2),
							CONVERT (FLOAT, (N1.ListPrice - N1.StandardCost)) / N1.StandardCost) AS ProfitMargin
FROM Production.Product N1
INNER JOIN Production.ProductSubcategory N2 ON N1.ProductSubcategoryID = N2.ProductSubcategoryID
INNER JOIN Production.ProductCategory N3 ON N2.ProductCategoryID = N3.ProductCategoryID
INNER JOIN Production.ProductModel N4 ON N1.ProductModelID = N4.ProductModelID
WHERE N3.Name = 'Bikes'
  AND N1.SellEndDate IS NULL
GROUP BY 
	N1.ProductModelID,
	N4.Name,
	CONVERT (DECIMAL (10,2) ,CONVERT (FLOAT, (N1.ListPrice - N1.StandardCost)) / N1.StandardCost)
ORDER BY ProfitMargin DESC
```

### Scenario 36

Part 1

Create a query about sales orders that utilized volume discounts. (Total volume discount - the sum of volume discounts applied to the order)

Part 2

Summarize data from Part 1 by order year

```{sql}
-- part 1
WITH table AS(
SELECT
	N1.SalesOrderID,
	N3.OrderDate,
	SUM (N1.UnitPriceDiscount * N1.UnitPrice * N1.OrderQty) AS TotalVolumeDiscount
FROM Sales.SalesOrderDetail N1
  INNER JOIN Sales.SpecialOffer N2 ON N1.SpecialOfferID = N2.SpecialOfferID
  INNER JOIN Sales.SalesOrderHeader N3 ON N1.SalesOrderID = N3.SalesOrderID
WHERE N2.Type = 'Volume Discount'
GROUP BY N1.SalesOrderID, N3.OrderDate
HAVING SUM (N1.UnitPriceDiscount * N1.UnitPrice * N1.OrderQty) > 0)

SELECT *
FROM table
ORDER BY SalesOrderID

-- part 2
SELECT date_trunc('year', OrderDate), SUM(TotalVolumeDiscount) AS TotalVolumeDiscount
FROM table
GROUP BY Year
```

### Scenario 35

Two emplyees are given free bicycles at the start of each quaterly meeting. The employees are chose at random, with eligibility limited to the least senior positions.

Generate employee names.

```{sql}
SELECT TOP 2
	N2.FirstName,
	N2.LastName,
	N1.JobTitle
FROM HumanResources.Employee N1
  INNER JOIN Person.Person N2 ON N1.BusinessEntityID = N2.BusinessEntityID

WHERE N1.OrganizationLevel IN (SELECT MAX (OrganizationLevel) 
								FROM HumanResources.Employee)
ORDER BY NEWID ()
```

### Scenario 34

Report revenue by state in 2006. Order the data from states with the greatest revenue to states with the least revenue.

```{sql}
SELECT
	N3.Name AS State,
	SUM (N1.TotalDue) AS TotalRevenue
FROM Sales.SalesOrderHeader N1
  INNER JOIN Person.Address N2 ON N1.ShipToAddressID = N2.AddressID
  INNER JOIN Person.StateProvince N3 ON N2.StateProvinceID = N3.StateProvinceID
WHERE date_trun('YEAR', N1.OrderDate = 2006
GROUP BY N3.Name
ORDER BY SUM (N1.TotalDue) DESC
```

### Scenario 32

Part 1:

Provide the following:

- Total number of emplyees throughout the company
- Percentage of employees who are Male
- Percentage of employees who are Female
- Average number of months of emplyment (today is January 1, 2008)


```{sql}
SELECT 
	COUNT (*) AS Employees,
	ROUND (SUM (CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) / CONVERT (FLOAT, COUNT (*)) * 100, 2) AS PercentageMale,
	ROUND (SUM (CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) / CONVERT (FLOAT, COUNT (*)) * 100, 2) AS PercentageFemale,
	AVG (DATEDIFF (MONTH, HireDate, '2008-01-01')) AS AvgMonthsEmp
FROM HumanResources.Employee
```

Part 2:

Divide empoyee data into quartiles based on average number of months of emplyoment

```{sql}
SELECT
	X1.Quartile,
	COUNT (*) AS Employees,
  ROUND (SUM (CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) / CONVERT (FLOAT, COUNT (*)) * 100, 2) AS PercentageMale,
	ROUND (SUM (CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) / CONVERT (FLOAT, COUNT (*)) * 100, 2) AS PercentageFemale,
	AVG (X1.MonthsEmployed) AS AvgMonthsEmp
FROM (SELECT 
		BusinessEntityID,
		NTILE (4) OVER (ORDER BY DATEDIFF (MONTH, HireDate, '2008-01-01')) AS Quartile, -- ntile function
		HireDate,
		DATEDIFF (MONTH, HireDate, '2008-01-01') AS MonthsEmployed,
		Gender
		FROM HumanResources.Employee) X1
GROUP BY X1.Quartile
```

### Scenario 33

Create a query summarizing pay rates and age groups by job title. Assume today is January 1, 2008. Your output should be structured with the following columns:

- Job Title
- Age group in years (< 18; 18-35; 36-50; 51-60; 61+)
- Pay Rate
- Number of employees

```{sql}
SELECT 
	N1.JobTitle,

	CASE 
			WHEN DATEDIFF ('Year', N1.BirthDate, '2008-01-01') < 18 THEN '< 18'
			WHEN DATEDIFF ('Year', N1.BirthDate, '2008-01-01') < 35 THEN '18 - 35'
			WHEN DATEDIFF ('Year', N1.BirthDate, '2008-01-01') < 50 THEN '36 - 50'
			WHEN DATEDIFF ('Year', N1.BirthDate, '2008-01-01') < 60 THEN '51 - 60'
	ELSE '61 +' END AS AgeGroup,

	N2.Rate,
	COUNT (N1.BusinessEntityID) AS Employees
FROM HumanResources.Employee N1
  INNER JOIN HumanResources.EmployeePayHistory N2 ON N1.BusinessEntityID = N2.BusinessEntityID 
  INNER JOIN (SELECT BusinessEntityID, MAX (RateChangeDate) AS  RatechangeDate -- filter in join
			FROM HumanResources.EmployeePayHistory
			GROUP BY BusinessEntityID) N3
	ON N3.BusinessEntityID = N2.BusinessEntityID AND N3.RatechangeDate = N2.RateChangeDate

GROUP BY 
	1,3,2
```

### Scenario 3

For each fiscal year (2007 and 2008) find the first dates when the cumulative running revenue total hit $10 million.

```{sql}
WITH FY2007 AS  
		(SELECT
			 2007 AS FY,
			 CAST (OrderDate AS DATE) AS OrderDate,
			ROW_NUMBER () OVER (ORDER BY SalesOrderID) AS OrderNumber,

			 SUM (SubTotal) OVER (ORDER BY Orderdate 
											ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
			FROM Sales.SalesOrderHeader
			WHERE DATEPART ('YEAR', DATEADD ('MONTH', 6, OrderDate)) = 2007),

	FY2008 AS 
		(SELECT
			2008 AS FY,
			CAST (OrderDate AS DATE) AS OrderDate,
			ROW_NUMBER () OVER (ORDER BY SalesOrderID) AS OrderNumber,

			SUM (SubTotal) OVER (ORDER BY Orderdate 
											ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal -- Running Total Window function
			FROM Sales.SalesOrderHeader
			WHERE DATEPART ('YEAR', DATEADD ('MONTH', 6, OrderDate)) = 2008)

SELECT TOP 1 * FROM FY2007 WHERE RunningTotal > = 10000000
UNION
SELECT TOP 1 * FROM FY2008 WHERE RunningTotal > = 10000000
```

### Scenario 4

Compare sales from Tuesday to other days of the week to see if the intiative is working.

```{sql}
SELECT 
	 DATENAME (WEEKDAY, OrderDate) AS DayCategory, -- datename function
	 SUM (Subtotal) AS Revenue,
	 COUNT (*) AS Orders,
	 SUM (Subtotal) / COUNT (*) AS RevenuePerOrder
FROM Sales.SalesOrderHeader
WHERE date_trunc('year', OrderDate) = 2008 
  AND OnlineOrderFlag = 0
GROUP BY DATENAME (WEEKDAY, OrderDate)
ORDER BY RevenuePerOrder DESC
```