Problem 32: High-value customers
We want to send all of our high-value customers a special VIP gift. We’re defining high-value customers as those who’ve made at least 1 order with a total value (not including the discount) equal to $10,000 or more. We only want to consider orders made in the year 2016.
Solution
Select Customers.Customerid,
Customers.Companyname,
Orders.Orderid,
Totalorderamount = Sum(Quantity * Unitprice)
From Customers
Join Orders
On Orders.Customerid = Customers.Customerid
Join Orderdetails
On Orders.Orderid = Orderdetails.Orderid
Where
Year(Orders.Orderdate) = 2016
Group By Customers.Customerid, Customers.Companyname, Orders.Orderid
Having Sum(Quantity * Unitprice) > 10000
Order By Totalorderamount Desc;
Problem 33: High-value customers _ total orders
The manager has changed his mind. Instead of requiring that customers have at least one individual orders totaling $10,000 or more, he wants to define high-value customers as those who have orders totaling $15,000 or more in 2016. How would you change the answer to the problem above?
Solution
Select Customers.CustomerID, Customers.CompanyName,
TotalOrderAmount = SUM(Quantity * UnitPrice)
From Customers
join Orders
on Orders.CustomerID = Customers.CustomerID
join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Where
YEAR(Orders.OrderDate) = 2016
group by Customers.CustomerID, Customers.CompanyName
having SUM(Quantity * UnitPrice) > 15000
order by TotalOrderAmount desc;
Problem 34: High-value customers _ with discount
Change the above query to use the discount when calculating high-value customers. Order by the total amount which includes the discount.
Solution
Select Customers.Customerid, Customers.Companyname,
Totalwithoutdiscount = Sum(Quantity * Unitprice),
Totalwithdiscount = Sum(Quantity * Unitprice * (1-Discount))
From Customers
Join Orders
On Orders.Customerid = Customers.Customerid
Join Orderdetails
On Orders.Orderid = Orderdetails.Orderid
Where
Year(Orders.Orderdate) = 2016
Group By Customers.Customerid, Customers.Companyname
Having Sum(Quantity * Unitprice * (1-Discount)) > 10000
Order By Totalwithdiscount Desc;
Solution (using window functions)
Select Customerid, Companyname, Totalswithoutdiscount, Totalswithdiscount
From
(Select Distinct Customers.Customerid, Companyname,
Sum(Quantity * Unitprice)
Over(Partition By Customers.Customerid) As Totalswithoutdiscount,
Sum(Quantity * Unitprice * (1-Discount))
Over(Partition By Customers.Customerid) As Totalswithdiscount
From Customers
Join Orders
On Orders.Customerid = Customers.Customerid
Join Orderdetails
On Orders.Orderid = Orderdetails.Orderid
Where
Year(Orders.Orderdate) = 2016) As T
Where Totalswithdiscount > 10000
Problem 35: Month-end orders
At the end of the month, salespeople are likely to try much harder to get orders, to meet their month-end quotas. Show all orders made on the last day of the month. Order by EmployeeID and OrderID.
Solution
Select Employeeid, Orderid, Orderdate
From Orders
Where Eomonth(Orderdate) = Convert(Date, Orderdate)
Order By Employeeid, Orderid
Problem 36: Orders with many line items
The Northwind mobile app developers are testing an app that customers will use to show orders. In order to make sure that even the largest orders will show up correctly on the app, they’d like some samples of orders that have lots of individual line items. Show the 10 orders with the most line items, in order of total line items.
Solution (with window functions)
Select Top 10 Orderid, Totalorderdetails
From
(Select Distinct Orderid,
Count(Orderid) Over(Partition By Orderid) As Totalorderdetails
From Orderdetails) As T
Order By Totalorderdetails Desc
Solution
Select Top 10 Orderid, Count(Orderid) As Totalorderdetails
From Orderdetails
Group By Orderid
Order By Totalorderdetails Desc
Problem 37: Orders _ random assortment
The Northwind mobile app developers would now like to just get a random assortment of orders for beta testing on their app. Show a random set of 2% of all orders.
Solution
Select Top 2 Percent Orderid
From Orders
Order By Newid()
Problem 38: Orders _ accidental double-entry
Janet Leverling, one of the salespeople, has come to you with a request. She thinks that she accidentally double-entered a line item on an order, with a different ProductID, but the same quantity. She remembers that the quantity was 60 or more. Show all the OrderIDs with line items that match this, in order of OrderID.
Solution (with window functions)
select distinct OrderID
from
(select OrderID,
count(ProductID) over(partition by orderid, quantity) as nl
from OrderDetails
where Quantity >= 60) As T
where nl > 1
order by OrderID
Problem 39: Orders _ accidental double-entry details
Based on the previous question, we now want to show details of the order, for orders that match the above criteria.
Solution (with window functions)
With DE_Orders(OrderID) As
(select distinct OrderID
from
(select OrderID,
count(ProductID) over(partition by orderid, quantity) as nl
from OrderDetails
where Quantity >= 60) As T
where nl > 1)
select OrderID, ProductID, UnitPrice, QUantity, Discount
from OrderDetails
where OrderID in (select OrderID from DE_Orders)
Problem 41: Late orders
Some customers are complaining about their orders arriving late. Which orders are late?
Solution
select OrderID, OrderDate, RequiredDate, ShippedDate
from Orders
where ShippedDate >= RequiredDate
Problem 42: Late orders _ which employees?
Some salespeople have more orders arriving late than others. Maybe they’re not following up on the order process, and need more training. Which salespeople have the most orders arriving late?
Solution
select Orders.EmployeeID, Employees.LastName,
count(OrderID) As TotalLateOrders
from Orders
join Employees on Orders.EmployeeID = Employees.EmployeeID
where ShippedDate >= RequiredDate
group by Orders.EmployeeID, Employees.LastName
order by TotalLateOrders desc
Problem 43: Late orders vs. total orders
Andrew, the VP of sales, has been doing some more thinking some more about the problem of late orders. He realizes that just looking at the number of orders arriving late for each salesperson isn’t a good idea. It needs to be compared against the total number of orders per salesperson.
Solution (with window functions)
select EmployeeID, LastName, TotalOrders, TotalLateOrders
from
(select distinct O.EmployeeID, E.LastName,
sum(case when ShippedDate >= RequiredDate then 1 else 0 end)
over (partition by O.EmployeeID) As TotalLateOrders,
count(O.OrderID) over (partition by O.EmployeeID) As TotalOrders
from Orders AS O
join Employees AS E
on O.EmployeeID = E.EmployeeID) AS T
where TotalLateOrders > 0
Problem 46: Late orders vs. total orders _ percentage
Now we want to get the percentage of late orders over total orders.
Solution (with window functions)
select EmployeeID, LastName, TotalOrders, TotalLateOrders,
(TotalLateOrders*100.0/TotalOrders) AS PercentLateOrders
from
(select distinct O.EmployeeID, E.LastName,
sum(case when ShippedDate >= RequiredDate then 1 else 0 end)
over (partition by O.EmployeeID) As TotalLateOrders,
count(O.OrderID) over (partition by O.EmployeeID) As TotalOrders
from Orders AS O
join Employees AS E
on O.EmployeeID = E.EmployeeID) AS T
where TotalLateOrders > 0
Problem 47: Late orders vs. total orders _ fix decimal
Solution (with window functions)
select EmployeeID, LastName, TotalOrders, TotalLateOrders,
CONVERT(decimal(10,2),(TotalLateOrders*100.0/TotalOrders))
AS PercentLateOrders
from
(select distinct O.EmployeeID, E.LastName,
sum(case when ShippedDate >= RequiredDate then 1 else 0 end)
over (partition by O.EmployeeID) As TotalLateOrders,
count(O.OrderID) over (partition by O.EmployeeID) As TotalOrders
from Orders AS O
join Employees AS E
on O.EmployeeID = E.EmployeeID) AS T
where TotalLateOrders > 0
Problem 48: Customer grouping
Andrew Fuller, the VP of sales at Northwind, would like to do a sales campaign for existing customers. He’d like to categorize customers into groups, based on how much they ordered in 2016. Then, depending on which group the customer is in, he will target the customer with different sales materials. The customer grouping categories are 0 to 1,000, 1,000 to 5,000, 5,000 to 10,000, and over 10,000. A good starting point for this query is the answer from the problem “High-value customers - total orders. We don’t want to show customers who don’t have any orders in 2016. Order the results by CustomerID.
Solution (with CTE)
With CustomerTotalOrder_2016(CustomerID, CompanyName, TotalOrderAmount) AS
(Select Customers.CustomerID, Customers.CompanyName,
TotalOrderAmount = SUM(Quantity * UnitPrice)
From Customers
join Orders
on Orders.CustomerID = Customers.CustomerID
join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Where
YEAR(Orders.OrderDate) = 2016
group by Customers.CustomerID, Customers.CompanyName)
select CustomerID, CompanyName, TotalOrderAmount,
case
when 0 <= TotalOrderAmount AND TotalOrderAmount <= 1000 Then 'Low'
when 1000 < TotalOrderAmount AND TotalOrderAmount <= 5000 Then 'Medium'
when 5000 < TotalOrderAmount AND TotalOrderAmount <= 10000 Then 'High'
when TotalOrderAmount > 10000 Then 'Very High'
end as CustomerGroup
from CustomerTotalOrder_2016
order by CustomerID
Problem 50: Customer grouping with percentage
Based on the above query, show all the defined CustomerGroups, and the percentage in each. Sort by the total in each group, in descending order.
Solution
With CustomerTotalOrder_2016(CustomerID, CompanyName, TotalOrderAmount) AS
(Select Customers.CustomerID, Customers.CompanyName,
TotalOrderAmount = SUM(Quantity * UnitPrice)
From Customers
join Orders
on Orders.CustomerID = Customers.CustomerID
join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Where
YEAR(Orders.OrderDate) = 2016
group by Customers.CustomerID, Customers.CompanyName)
select distinct
CustomerGroup,
count(CustomerGroup) over(partition by CustomerGroup) as TotalInGroup,
(count(CustomerGroup) over(partition by CustomerGroup))*100.0/(select count(*)
from CustomerTotalOrder_2016) as PercentageInGroup
from
(select
case
when 0 <= TotalOrderAmount AND TotalOrderAmount <= 1000 Then 'Low'
when 1000 < TotalOrderAmount AND TotalOrderAmount <= 5000 Then 'Medium'
when 5000 < TotalOrderAmount AND TotalOrderAmount <= 10000 Then 'High'
when TotalOrderAmount > 10000 Then 'Very High'
end as CustomerGroup
from CustomerTotalOrder_2016) AS T
order by TotalInGroup desc
Problem 51: Customer grouping _ flexible
Andrew, the VP of Sales is still thinking about how best to group customers, and define low, medium, high, and very high value customers. He now wants complete flexibility in grouping the customers, based on the dollar amount they’ve ordered. He doesn’t want to have to edit SQL in order to change the boundaries of the customer groups. How would you write the SQL? There’s a table called CustomerGroupThreshold that you will need to use. Use only orders from 2016.
Solution
With CustomerTotalOrder_2016(CustomerID, CompanyName, TotalOrderAmount) AS
(Select Customers.CustomerID, Customers.CompanyName,
TotalOrderAmount = SUM(Quantity * UnitPrice)
From Customers
join Orders
on Orders.CustomerID = Customers.CustomerID
join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Where
YEAR(Orders.OrderDate) = 2016
group by Customers.CustomerID, Customers.CompanyName)
select CustomerID, CompanyName, TotalOrderAmount, CustomerGroupName
from CustomerTotalOrder_2016
join CustomerGroupThresholds
on (RangeBottom < TotalOrderAmount)
and (RangeTop > TotalOrderAmount)
order by CustomerID
Problem 52: Countries with suppliers or customers
Some Northwind employees are planning a business trip, and would like to visit as many suppliers and customers as possible. For their planning, they’d like to see a list of all countries where suppliers and/or customers are based.
Solution
select Country from Suppliers
Union
select Country from Customers
Problem 53: Countries with suppliers or customers version 2
The employees going on the business trip don’t want just a raw list of countries, they want more details. We’d like to see output like the below, in the Expected Results.
Solution
select S.Country as SupplierCountry, C.Country as CustomerCountry
from Suppliers as S
left outer join Customers AS C
on S.Country = C.Country
Union
select S.Country as SupplierCountry, C.Country as CustomerCountry
from Customers as C
left outer join Suppliers AS S
on S.Country = C.Country
Problem 54: Countries with suppliers or customers version 3
The output of the above is improved, but it’s still not ideal What we’d really like to see is the country name, the total suppliers, and the total customers.
Solution
With All_Countries(Country) AS
(select Country from Suppliers
Union
select Country from Customers)
select CC.Country, count(S.SupplierID) AS TotalSuppliers, TotalCustomers
from
(select AC.Country, count(C.CustomerID) AS TotalCustomers
from All_Countries as AC
left outer join Customers as C on AC.Country = C.Country
group by AC.Country) AS CC
left outer join Suppliers as S
on CC.Country = S.Country
group by CC.Country, TotalCustomers
Problem 55: First order in each country
Looking at the Orders table—we’d like to show details for each order that was the first in that particular country, ordered by OrderID. So, we need one row per ShipCountry, and CustomerID, OrderID, and OrderDate should be of the first order from that country.
Solution
select O.ShipCountry, O.OrderID, O.CustomerID, FirstOrderDate
from Orders as O
join
(select ShipCountry, Min(OrderDate) as FirstOrderDate
from Orders
group by ShipCountry) AS SCFOD
on O.ShipCountry = SCFOD.ShipCountry
and O.OrderDate = SCFOD.FirstOrderDate
Solution (with window functions)
select ShipCountry,CustomerID, OrderID, OrderDate
from
(select CustomerID, ShipCountry, OrderID, OrderDate,
(rank() over(partition by ShipCountry order by OrderDate)) as rnk
from Orders) AS T
where rnk = 1
group by CustomerID, ShipCountry, OrderID, OrderDate