Mastering SQL Through Practice: Exploring Sylvia Moestl Vasilik’s Exercises — Part 7
In this blog, we will be solving advanced-level questions 41–50 from the book. You can read the blog and solve the problems as you go. Previous Blog where we solved questions 32–40, the link is here.
Contents:
41. Late orders
42. Late orders - which employees?
43. Late orders vs. total orders
44. Late orders vs. total orders - missing employee
45. Late orders vs. total orders - fix null
46. Late orders vs. total orders - percentage
47. Late orders vs. total orders - fix decimal
48. Customer grouping
49. Customer grouping - fix null
50. Customer grouping with percentage
Problem 41: Some customers are complaining about their orders arriving late. Which orders are late?
Description: To determine which orders are late, you can use a combination of the RequiredDate and ShippedDate. It’s not exact, but if ShippedDate is actually AFTER RequiredDate, you can be sure it’s late.
Problem 42: 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?
Description: You’ll need to join the Employee table to get the last name, and also add Count to show the total late orders.
Problem 43: 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. Return results like the following:
Description: You can use more than one CTE in a query. That would be a straightforward way of solving this problem. Here we have taken the two select statements and then used the Union.
Problem 44: There’s an employee missing in the answer to the problem above. Fix the SQL to show all employees who have taken orders.
Problem 45: Continuing on the answer for the above query, let’s fix the results for row 5 — Buchanan. He should have a 0 instead of a Null in LateOrders.
Problem 46: Now we want to get the percentage of late orders over total orders.
Description: By dividing late orders by total orders, you should be able to get the percentage of orders that are late. Here we have solved this problem using CTE. We have declared the LateOrders and all orders and then in select statement, we have added the calculations of percentage.
Problem 47: So now for the PercentageLateOrders, we get a decimal value as we should. But to make the output easier to read, let's cut the PercentLateOrders off at 2 digits to the right of the decimal point.
Description: One straightforward way of doing this would be to explicitly convert PercentageLateOrders to a specific Decimal data type. With the Decimal datatype, you can specify how many digits you want to the right of the decimal point. For that we have used the convert() function in that the syntax is convert(need to convert decimal(10 decimals that we want after )).
Problem 48: 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.
Description: This is the SQL from the problem “High-value customers - total orders”, but without the filter for order totals over 10,000.
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 OrderDate >= ‘20160101’ and OrderDate < ‘20170101’
Group By Customers.CustomerID.
Here we have used the above SQL in a CTE (common table expression) and then build on it, using a Case statement on the TotalOrderAmount.
Problem 49: There's a bug with the answer to the previous question. The CustomerGroup value for one of the rows is null.
Fix the SQL so that there are no nulls in the CustomerGroup field.
Description: This problem is the same as the 48 only the change is to fix the null that we have seen earlier. Using the Convert() function you can do so.
Problem 50: Based on the above query, show all the defined customer groups, and the percentage in each. Sort by the total in each group, in descending order.
Description: We no longer need to show the CustomerID and CompanyName in the final output. However, we need to count how many customers are in each CustomerGrouping. You can create another CTE level in order to get the counts in each CustomerGrouping for the final output.
