SQL Table of contents: Software Skills Assessment

SQL skills assessment – JOINs & Aggregations

This is a sample skills assessment for testing SQL knowledge of JOINs and aggregate functions.

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: 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:

Write a SQL query to calculate the average order value by region. The order value is calculated as the sum of Quantity * Price for all order lines.

Model Answers

Question: Calculate the Average Order Value by Region

This questions assesses the candidate’s ability to write complex SQL queries that join multiple tables and use subqueries to achieve a specific result.

It also tests the understanding of SQL syntax, especially the use of SELECT, JOIN, GROUP BY, and aggregate functions like SUM and AVG.

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


SELECT 
    c.Region, 
    AVG(ol.TotalOrderValue) AS AvgOrderValue
FROM 
    Orders o
JOIN 
    Customers c ON o.CustomerID = c.CustomerID
JOIN 
    (
        SELECT 
            OrderID, 
            SUM(Quantity * Price) AS TotalOrderValue
        FROM 
            OrderLines
        GROUP BY 
            OrderID
    ) ol ON o.OrderID = ol.OrderID
GROUP BY 
    c.Region;

The query calculates the total value of each order by summing Quantity * Price for each OrderID. It then joins the result with Orders and Customers to group by Region and calculates the average order value for each region.

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 the Average Order Value by 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 | AverageOrderValue |
+--------+-------------------+
| North  | 90.00             |
| South  | 50.00             |
+--------+-------------------+

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

If the numbers are different it means that the candidate has made a mistake in their SQL code.

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: Look at their answer. If they used a subquery, show them their own answer. If they did not use a subquery, show them the model answer since it uses a subquery. Ask them why it is better to calculate the total order value in a subquery rather than in the main query.

Expected Answer: The candidate should explain that calculating the total order value in a subquery helps simplify the main query by isolating the logic for computing TotalOrderValue for each order. This separation makes the main query easier to read and maintain. Additionally, it avoids recalculating the SUM(Quantity * Price) multiple times if it were needed in multiple places in the main query.

2. Discussion question: Ask them to explain why the GROUP BY clause is essential in this query and what would happen if it was omitted.

Expected Answer: The candidate should explain that the GROUP BY clause is essential because it groups the results by Region, allowing the AVG() function to calculate the average order value per region. Without the GROUP BY, the query would fail or return incorrect results because the aggregate function would not know how to group the data. If they can’t clearly explain the purpose of GROUP BY or are unsure about the consequences of omitting it, this indicates gaps in their knowledge of SQL aggregation.

3. Discussion question: Ask them what alternative methods could be used to calculate the average order value by region, and why which approach would be more efficient or clearer.

Expected Answer: The candidate might suggest alternative methods like using a window function (OVER(PARTITION BY Region)), which could be more efficient in certain cases. However, they should justify why they chose their approach, likely because it is straightforward and easy to understand for this specific problem. They might also mention that window functions are more complex and could be overkill for such a task. Candidates should be able to suggest valid alternatives and compare them. They should acknowledge trade-offs in complexity and performance.

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