SQL Practice Problems - Advanced

Solutions to advanced problems from the book - SQL Practice Problems
SQL
Published

December 9, 2019

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
Back to top