Mastering SQL Through Practice: Exploring Sylvia Moestl Vasilik’s Exercises — Part 8
In this blog, we will be solving advanced-level questions 51–57 from the book. You can read the blog and solve the problems as you go. Previously, we solved questions 41- 50, to visit the blog click here.
Contents:
51. Customer grouping - flexible
52. Countries with suppliers or customers
53. Countries with suppliers or customers, version 2
54. Countries with suppliers or customers — version 3
55. First order in each country
56. Customers with multiple orders in 5 day period
57. Customers with multiple orders in 5 day period, version 2
Problem 51: 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.
Description: As a starting point, use the SQL of the first CTE from the problem “Customer grouping with percentage” Select Customers.CustomerID, Customers.CompanyName, TotalOrderAmount = SUM(Quantity * UnitPrice) From Customers join Orders on Orders.CustomerID = Customers.CustomerID joins OrderDetails on Orders.OrderID = OrderDetails.OrderID Where OrderDate >= ‘20160101’ and OrderDate < ‘20170101’ Group By Customers.CustomerID, Customers.CompanyName When thinking about how to use the table CustomerGroupThreshold, note that when joining a table, you don’t need to only use an equi-join (i.e.,“=“ in the join). You can also use other operators, such as between, and greater than/less than (> and <).
Problem 52: 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.
Description: Use the Union statement for this. It’s a good way of putting together a simple resultset from multiple SQL statements. We have simply used the Union between two select statements.
Problem 53: 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 one below, in the Expected Results.
Description: To start would with a list of countries from the Suppliers table, and a list of countries from the Customers table. Use either Distinct or Group to avoid duplicating countries. Sort by country name You should have something like this: Select Distinct Country from Customers Select Distinct Country from Suppliers You can combine these with a CTEs or derived tables.
Problem 54: 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.
Description: You should be able to use the above query, and make a few changes to the CTE source queries to show the total number of Supplier countries and Customer countries. You won’t be able to use the Distinct keyword anymore.
Problem 55: 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.
Description: Your first step will probably be to create a query like this:
Select ShipCountry ,CustomerID ,OrderID ,OrderDate = convert(date, OrderDate) From orders Order by ShipCountry, OrderID
…which shows all the rows in the Order table, sorted first by Country and then by OrderID. Your next step is to create a computed column that shows the row number for each order, partitioned appropriately. There’s a class of functions called Window functions or Ranking functions that you can use for this problem. Specifically, use the Row_Number() function, with the Over and Partition clause, to get the number, per country, of a particular order. You’ll have something like this:
Select ShipCountry,CustomerID, OrderID, OrderDate = convert(date, OrderDate), RowNumberPerCountry = Row_Number() over (Partition by ShipCountry Order by ShipCountry, OrderID) From Orders Because of some limitations with Window functions, you can’t directly filter the computed column created above. So we have used CTE to solve the problem.
Problem 56: There are some customers for whom freight is a major expense when ordering from Northwind.
However, by batching up their orders, and making one larger order instead of multiple smaller orders in a short period of time, they could reduce their freight costs significantly.
Show those customers who have made more than 1 order in a 5-day period. The salespeople will use this to help customers reduce their costs.
Note: There is more than one way of solving this kind of problem. For this problem, we will not be using Windows functions.
Description: This is a good start. You will need to filter on additional fields in the join clause between InitialOrder and NextOrder because as it is, this returns far too many orders. It has what’s called a cartesian product between the 2 instances of the Orders table. This means that for the total number of orders for a particular customer in Orders, you’ll have that number, squared, in the output.
Look at some of the OrderID and OrderDate values in InitialOrder and the next order. Some of them definitely disqualify a row based on our criteria. Create a new field called DaysBetween that calculates the number of days between the initial order OrderDate and the NextOrder OrderDate. Use the DateDiff function.
Problem 57: There’s another way of solving the problem above, using Window functions. We would like to see the following results.
Description: You should have something like this: Select CustomerID, OrderDate = convert(date, OrderDate), NextOrderDate = convert( date, Lead(OrderDate,1) OVER (Partition by CustomerID order by CustomerID, OrderDate) )
From Orders Order by CustomerID,OrderID] Now, take the output of this, and using a CTE and the DateDiff function, filter for rows which match our criteria.
