Skip to content
Home » Blog » SQL Week 4 Practice Problems

SQL Week 4 Practice Problems

Practice Topics

  • Common Table Expressions (CTEs)
  • Top N
  • Final Comprehensive Query Practice

Common Table Expressions (CTEs)

Overview

In this Week 3 post, you were introduced to subqueries. Subqueries are really ugly when included in a complex query. They are hard to read, especially when other things are going on around them.

The good news is that common table expressions (CTEs) exist. They are exactly the same thing as a subquery, but you just define them at the top of a query using a WITH statement. Once defined, you can treat them like a table throughout the remainder of your query. Multiple CTEs are separated with a comma. This has multiple benefits:

  • Easy to read: When they are defined at the top of a query, they are much easier to read since they aren’t intermingled with everything else.
  • Unlimited CTEs allowed: You aren’t restricted on the number of CTEs that you can use in a single query. This is amazing in longer queries because they can be chunked up in bite-sized segments.
  • Sequential processing: CTEs are processed from the top down. This means that you can actually reference one CTE in another CTE as you move through the query.
  • Usable everywhere: A CTE is usable everywhere later in the query (including in SELECT, FROM, WHERE, and of course in other CTEs).
  • Treat them like a table: As mentioned earlier, once you have defined a CTE, it acts just like a table. Instead of listing DatabaseName.SchemaName.TableName in the FROM statement, list the name assigned to a CTE later in the query where you want to reference it.

Example: Using the AdventureWorks2019.Sales.SalesOrderDetail table, calculate the % of Total Sales for each ProductID per SalesOrderID. Total sales can be determined by using aggregation within a CTE. In the output, include the Name from the AdventureWorks2019.Production.Product table and give it an alias name of ProductName. You haven’t yet learned how to do math in SQL. To determine the % of Total Sales, you can divide the LineTotal by the TotalSales that you will derive in your CTE. Division works exactly the same as it does in Excel; use /.

SQL Code Highlighting 
WITH TotalSalesCTE AS (
    SELECT
        SalesOrderID,
        SUM(LineTotal) AS TotalSales
    FROM
        AdventureWorks2019.Sales.SalesOrderDetail
    GROUP BY
        SalesOrderID
)
SELECT
    a.SalesOrderID,
    a.ProductID,
    b.Name AS ProductName,
    c.TotalSales,
    a.LineTotal / c.TotalSales AS "Percent of Total Sales"
FROM
    AdventureWorks2019.Sales.SalesOrderDetail a
INNER JOIN
    AdventureWorks2019.Production.Product b
    ON a.ProductID = b.ProductID
INNER JOIN
    TotalSalesCTE c
    ON a.SalesOrderID = c.SalesOrderID;

Example: Now, add on to the prior example and include a metric that shows the OrderQty and % of Total Quantity for each ProductID per SalesOrderID using the AdventureWorks2019.Sales.SalesOrderDetail table.

When you divide a whole number by a whole number, the result is a whole number (either 1 or 0 depending on rounding). You avoid this by converting either the numerator or denominator to a decimal. I typically handle this by converting the denominator to a float. A float is the same thing as a decimal. This will ensure you get a decimal as intended. For example: a.OrderQty / cast(d.TotalQty as float)

SQL Code Highlighting 
WITH TotalSalesCTE AS (
    SELECT
        SalesOrderID,
        SUM(LineTotal) AS TotalSales
    FROM
        AdventureWorks2019.Sales.SalesOrderDetail
    GROUP BY
        SalesOrderID
),
TotalQtyCTE AS (
    SELECT
        SalesOrderID,
        SUM(OrderQty) AS TotalQty
    FROM
        AdventureWorks2019.Sales.SalesOrderDetail
    GROUP BY
        SalesOrderID
)
SELECT
    a.SalesOrderID,
    a.ProductID,
    b.Name AS ProductName,
    c.TotalSales,
    a.LineTotal / c.TotalSales AS "Percent of Total Sales",
    d.TotalQty,
    a.OrderQty / cast(d.TotalQty as float) AS "Percent of Total Quantity"
FROM
    AdventureWorks2019.Sales.SalesOrderDetail a
INNER JOIN
    AdventureWorks2019.Production.Product b
    ON a.ProductID = b.ProductID
INNER JOIN
    TotalSalesCTE c
    ON a.SalesOrderID = c.SalesOrderID
INNER JOIN
    TotalQtyCTE d
    ON a.SalesOrderID = d.SalesOrderID;

Self Practice

  • Write a query to identify all the names of employees by selecting FirstName and LastName from the AdventureWorks2019.Person.Person table where BusinessEntityID is in a common table expression (CTE) that selects BusinessEntityID from the AdventureWorks2019.HumanResources.Employee table.
  • Write a CTE to calculate the total sales for each customer from the AdventureWorks2019.Sales.SalesOrderHeader table, then select the top 5 customers from your CTE based on total sales amount.
SQL Code Highlighting 
WITH EmployeeCTE AS (
    SELECT
        BusinessEntityID
    FROM
        AdventureWorks2019.HumanResources.Employee
)
SELECT
    p.FirstName,
    p.LastName
FROM
    AdventureWorks2019.Person.Person p
INNER JOIN
    EmployeeCTE
    ON p.BusinessEntityID = EmployeeCTE.BusinessEntityID;
SQL Code Highlighting 
WITH CustomerSales AS (
    SELECT
        CustomerID,
        SUM(TotalDue) AS TotalSales
    FROM
        AdventureWorks2019.Sales.SalesOrderHeader
    GROUP BY
        CustomerID
)
SELECT TOP 5
    CustomerID,
    TotalSales
FROM
    CustomerSales
ORDER BY
    TotalSales DESC;

Top N

Overview

Sometimes, you need to display the Top N records from a query result. For example, if you are asked to produce the top 10 customers by total sales, you are going to get thousands of records unless you specifically request the top 10 records. To return the top 10, you need to do two things:

  1. Add TOP 10 right after the SELECT: SELECT TOP 10
  2. Add an ORDER BY on the total sales descending: ORDER BY TotalSales DESC

Example: Identify the top 10 CustomerID values from the AdventureWorks2019.Sales.SalesOrderHeader table in order of TotalDue descending.

SQL Code Highlighting 
SELECT TOP 10
    a.CustomerID,
    SUM(a.TotalDue) AS TotalSales
FROM
    AdventureWorks2019.Sales.SalesOrderHeader a
GROUP BY
    a.CustomerID
ORDER BY
    TotalSales DESC;

Self Practice

  • Write a SQL query to retrieve the top 5 products with the highest cumulative (LineTotal) from the AdventureWorks2019.Sales.SalesOrderDetail table. Include the ProductID, sum of LineTotal, and Name of each product from the AdventureWorks2019.Production.Product table in the output.
SQL Code Highlighting 
SELECT TOP 5
    p.ProductID,
    p.Name,
    SUM(sod.LineTotal) AS TotalSales
FROM
    AdventureWorks2019.Sales.SalesOrderDetail sod
INNER JOIN
    AdventureWorks2019.Production.Product p
    ON sod.ProductID = p.ProductID
GROUP BY
    p.ProductID,
    p.Name
ORDER BY
    TotalSales DESC;

Final Comprehensive Query Practice

  • Write a query to find the top 5 products by OrderQty in the AdventureWorks2019.Sales.SalesOrderDetail table, joined with the AdventureWorks2019.Production.Product table to get the product Name.
  • Write a query to find all customers from the AdventureWorks2019.Sales.Customer table who did not make a purchase in 2013 in the AdventureWorks2019.Sales.SalesOrderHeader table. Hint, use a negative join.
  • Write a query to calculate the total sales for each SalesPersonID for the year 2014 from the AdventureWorks2019.Sales.SalesOrderHeader table, including their FirstName and LastName from the AdventureWorks2019.Person.Person table. Use appropriate joins and date functions.
  • Write a query to list all CustomerID values who have placed orders from the AdventureWorks2019.Sales.SalesOrderHeader table, in both 2013 and 2014. Use subqueries or joins as needed.
SQL Code Highlighting 
SELECT TOP 5
    sod.ProductID,
    p.Name,
    SUM(sod.OrderQty) AS TotalOrderQty
FROM
    AdventureWorks2019.Sales.SalesOrderDetail sod
INNER JOIN
    AdventureWorks2019.Production.Product p
    ON sod.ProductID = p.ProductID
GROUP BY
    sod.ProductID,
    p.Name
ORDER BY
    TotalOrderQty DESC;
SQL Code Highlighting 
SELECT
    c.CustomerID,
    c.PersonID,
    c.StoreID,
    c.TerritoryID,
    c.AccountNumber
FROM
    AdventureWorks2019.Sales.Customer c
LEFT JOIN
    AdventureWorks2019.Sales.SalesOrderHeader soh
    ON c.CustomerID = soh.CustomerID
    AND YEAR(soh.OrderDate) = 2013
WHERE
    soh.SalesOrderID IS NULL;
SQL Code Highlighting 
SELECT
    soh.SalesPersonID AS SalesPersonID,
    p.FirstName,
    p.LastName,
    SUM(soh.TotalDue) AS TotalSales
FROM
    AdventureWorks2019.Sales.SalesOrderHeader soh
INNER JOIN
    AdventureWorks2019.Person.Person p 
    ON soh.SalesPersonID = p.BusinessEntityID
WHERE
    YEAR(soh.OrderDate) = 2014
GROUP BY
    soh.SalesPersonID,
    p.FirstName,
    p.LastName;
SELECT
    c.CustomerID
FROM
    AdventureWorks2019.Sales.Customer c
WHERE
    c.CustomerID IN (
        SELECT
            soh2013.CustomerID
        FROM
            AdventureWorks2019.Sales.SalesOrderHeader soh2013
        WHERE 
            YEAR(soh2013.OrderDate) = 2013
    )
    AND
    c.CustomerID IN (
        SELECT
            soh2014.CustomerID
        FROM
            AdventureWorks2019.Sales.SalesOrderHeader soh2014
        WHERE 
            YEAR(soh2014.OrderDate) = 2014
    );

Conclusion

As we wrap up this week’s SQL homework assignments, we’ve delved into very advanced aspects of SQL, including common table expressions (CTEs), top N, and tying all four weeks together. Once you are familiar with these concepts, you will have a very strong foundation of knowledge. All crazy queries are just variations of these smaller topics so don’t get overwhelmed.

Remember, consistent practice is the key to mastering SQL. If you find yourself needing extra help or wanting to deepen your understanding, don’t hesitate to sign up for our private or group training sessions. These sessions offer personalized guidance and a collaborative learning environment to further enhance your SQL skills. Stay tuned for more articles, and keep pushing yourself to learn more!

Leave a Reply

Your email address will not be published. Required fields are marked *