MUBA Software
SQL Table of contents: Software Skills Assessment

SQL skills assessment – CTEs (Common Table Expressions)

This is a sample skills assessment for assessing the candidate’s ability to write advanced SQL queries involving multiple levels of data aggregation and the use of Common Table Expressions (CTEs).

How To Use This Assessment

This assessment can be used by recruiters as a way to filter candidates and improve the quality of candidates being sent to the hiring manager. This assessment can also be used by technical hiring managers during the interview process to assess a candidate’s skill level in specific SQL concepts: CTEs and complex data aggregations using JOINs, and aggregate functions.

Instructions for recruiters:

Do your usual resume screening to filter out candidates who do not meet the criteria for the position. Once you have a shortlist of candidates, send them the assessment instructions and questions via email. The candidates need to follow the instructions and answer the questions at home using all the tools available to them. They need to send the results to you via email. You (not the hiring manager) can then check the assessments for correctness. It is possible to do this without needing to know SQL. I provide assistance with this below. If the candidate has answered correctly, it means that they have a good understanding of the concepts being tested. You can then pass the candidate to the technical hiring manager for an interview. You should also pass along the assessment questions, model answers, the candidate’s assessment responses and the discussion questions listed below.

Instructions for technical hiring managers:

You will have received the candidate’s answers to the assessment questions, the model answers as well as some discussion questions. You can use the discussion questions in your interview to assess the candidate’s skills level. The discussion questions are designed to prompt the candidate to explain the approach that they took to find the solution and why certain techniques and approaches are better than others. This discussion will also quickly identify candidates who cheated and did not complete the assessments by themselves.

Assessment Instructions and Questions

Instructions:

Create the following tables and insert the provided data:


CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    Region VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

CREATE TABLE OrderLines (
    OrderLineID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10, 2)
);

INSERT INTO Customers (CustomerID, CustomerName, Region) VALUES
(1, 'John Doe', 'North'),
(2, 'Jane Smith', 'South'),
(3, 'Sam Brown', 'North');

INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(1, 1, '2023-01-01'),
(2, 2, '2023-01-02'),
(3, 1, '2023-01-03'),
(4, 3, '2023-01-04');

INSERT INTO OrderLines (OrderLineID, OrderID, ProductID, Quantity, Price) VALUES
(1, 1, 1, 2, 50.00),
(2, 1, 2, 1, 30.00),
(3, 2, 1, 1, 50.00),
(4, 3, 3, 3, 20.00),
(5, 4, 2, 2, 30.00),
(6, 4, 3, 1, 20.00);

Question:

Use CTEs and write a single SQL query to calculate the following for each region:

  • The total revenue generated by each region.
  • The percentage contribution of each customer’s total order value to the region’s total revenue.

Model Answer

Question: Calculate Total Revenue and Percentage Contribution for Each Region

This question assesses the candidate’s ability to write advanced SQL queries involving multiple levels of data aggregation and the use of Common Table Expressions (CTEs). It also tests their capability to handle sophisticated data relationships and to calculate intricate metrics within the database. The use of CTEs tests the candidate’s ability to break down complex problems into smaller, more manageable parts. This technique improves query readability, modularity, and maintenance.

There are many possible answers. One possible answer is this:


WITH RegionalRevenue AS (
    SELECT 
        c.Region, 
        SUM(ol.Quantity * ol.Price) AS TotalRevenue
    FROM 
        Orders o
    JOIN 
        Customers c ON o.CustomerID = c.CustomerID
    JOIN 
        OrderLines ol ON o.OrderID = ol.OrderID
    GROUP BY 
        c.Region
),
CustomerRevenue AS (
    SELECT 
        c.CustomerID,
        c.CustomerName,
        c.Region, 
        SUM(ol.Quantity * ol.Price) AS CustomerTotalRevenue
    FROM 
        Orders o
    JOIN 
        Customers c ON o.CustomerID = c.CustomerID
    JOIN 
        OrderLines ol ON o.OrderID = ol.OrderID
    GROUP BY 
        c.CustomerID, c.Region
)
SELECT 
    cr.Region,
    rr.TotalRevenue,
    cr.CustomerName,
    cr.CustomerTotalRevenue,
    (cr.CustomerTotalRevenue / rr.TotalRevenue) * 100 AS ContributionPercentage
FROM 
    CustomerRevenue cr
JOIN 
    RegionalRevenue rr ON cr.Region = rr.Region
ORDER BY 
    cr.Region, cr.CustomerID;

The first CTE (RegionalRevenue) calculates the total revenue generated by each region.

The second CTE (CustomerRevenue) calculates the total revenue contributed by each customer within their region.

The final query joins these two CTEs to calculate the percentage contribution of each customer’s revenue to the total regional revenue.

How To Assess The Candidate Responses

If you are a client of MUBA Software, go to the online SQL Fiddler tool that you have been provided with. If you are not a client find a suitable online SQL Fiddler.

Question: Calculate Total Revenue and Percentage Contribution for Each Region

Copy and paste the SQL code from the instructions. Copy and past the candidate’s answer below that.

Click Run.

You should get this response: 


+--------+--------------+----------------+------------------------+------------------------+
| Region | TotalRevenue | CustomerName   | CustomerTotalRevenue   | ContributionPercentage |
+--------+--------------+----------------+------------------------+------------------------+
| North  | 270.00       | John Doe       | 190.00                 |  70.37%                |
| North  | 270.00       | Sam Brown      |  80.00                 |  29.62%                |
| South  |  50.00       | Jane Smith     |  50.00                 | 100.00%                |
+--------+--------------+----------------+------------------------+------------------------+

If there is no response or you get an error it means that the candidate has not successfully completed the assessment.

Each row represents a customer in a specific region. Look at the total revenue column. Also look at the contribution percentage column. The numbers should match what is listed above. If the numbers are different it means that the candidate has made a mistake in their SQL code. There might be other columns that the candidate has included. Those can be ignored.

Discussion Questions

The discussion questions are meant to be used by the hiring manager during the interview process. If you are the hiring manager, the recruiter would have shared the assessment questions, the model answers, the candidate’s answers and the discussion questions listed below. You can use the discussion questions to probe the candidate’s understanding of SQL and to help assess their SQL skills.

1. Discussion question: Ask why Common Table Expressions (CTEs) are better in this example instead of performing all calculations in a single query.

Expected Answer: The candidate should explain that CTEs improve the readability and structure of the query. By breaking down the problem into two separate CTEs, it becomes easier to understand and maintain. CTEs also allow the same logic to be reused in the main query without recalculating results, which can improve performance. If the candidate cannot explain the benefits of CTEs or suggests that they were used simply because they saw it done that way elsewhere, this could indicate a lack of understanding of SQL best practices

2. Discussion question: Ask them to explain how the JOIN between different CTEs work. In the model answer it is the JOIN between the CustomerRevenue and RegionalRevenue CTEs. Their CTEs will obviously be named differently but there should be two similar CTEs. Ask them what would happen if they used a different type of join?

Expected Answer: The candidate should explain that the JOIN between CustomerRevenue and RegionalRevenue links customer-specific revenue to the total regional revenue based on the Region column. This allows for the calculation of the contribution percentage for each customer. If a different type of join (e.g., LEFT JOIN or CROSS JOIN) was used, it could lead to incorrect or incomplete results. For example, a LEFT JOIN might include customers with no orders, skewing the contribution percentage calculation.

Custom assessments

If you are interested in a custom version of the above assessment with model answers and discussion questions that are private and not publicly visible, please get in touch. The assessment can even be tailored to the kinds of business data that your business normally deals with.

GET A CUSTOM SQL SKILLS ASSESSMENT