Acing SQL Code Assessments

- Recursive CTEs

This article is the first of 3 series of publications on acing SQL code assessments.

Introduction

The practice of sending code assessments to job seekers in technical roles has become the norm. Potential employers want to not only know if you can get it done but if you can do it optimally by writing optimal codes.

This series identifies key SQL concepts with which employers use SQL code assessments to evaluate.

The Use of CTEs for recursive queries

The use of CTEs for recursive queries is one of the identified SQL concepts employers/recruiters use SQL code assessments to evaluate. In this article, emphasis was laid on the use of recursive CTE with the assumption that readers have prior knowledge of CTEs and CTEs with non-recursive queries. For readers who do not have a prior understanding of CTEs please refer to this detailed article on CTEs by

Nadia Selas Noversega

. I would also recommend Tihomir Babic’s article on hierarchical data and how to query it in SQL as it gives detailed insight on how to store hierarchical data in a relational database and how to use recursive CTEs to query this data.

A recursive CTE consists of 3 parts, they are the anchor member, the recursive member, and the invocation which is the more often at times a select statement that succeeds the recursive member. The presence of a union all informs the database engine that the CTE is a recursive one. Therefore there is no need to explicitly state the keyword RECURSIVE while writing the query.

The options function (OPTION (MAXRECURSION <..>)) can be used after the invocation statement of a recursive CTE in order to prevent it from looping indefinitely.

Examples of recursive CTE readily available on the internet are for either manipulation of dynamic dates or for querying hierarchical data across various levels, this was why this unique use case from a recent SQL assessment was selected.

Syntax Recursive CTE:

The syntax for Recursive CTE

Use Case

In the dataset below, the most current transaction is defined by a null value in the parent transaction column and a bill is defined by the presence of a date in the billdate column.

Question: How many bills have been sent for each current transaction?

Exercise: Please create a SQL query that will generate the expected results

Expected Results:

Expected Results From Assessors

/**SQL assessment dataset**/
DECLARE @Trans TABLE (TransactionId int, BillDate date, ParentTransaction int)
INSERT @Trans(TransactionId, BillDate, ParentTransaction)
SELECT 50, null, null
UNION
SELECT 45, '2011-10-01', 50
UNION
SELECT 40, '2011-09-05', 45
UNION
SELECT 35, '2011-01-07', 40
UNION
SELECT 100, '2011-01-24', null
UNION
SELECT 95, null, 100
UNION
SELECT 90, null, 95
UNION
SELECT 70, null, null;
select * from @Trans

Solution

DECLARE @Trans TABLE (TransactionId int, BillDate date, ParentTransaction int)
INSERT @Trans(TransactionId, BillDate, ParentTransaction)
SELECT 50, null, null
UNION
SELECT 45, '2011-10-01', 50
UNION
SELECT 40, '2011-09-05', 45
UNION
SELECT 35, '2011-01-07', 40
UNION
SELECT 100, '2011-01-24', null
UNION
SELECT 95, null, 100
UNION
SELECT 90, null, 95
UNION
SELECT 70, null, null;
WITH transaction_hierarchy AS (
SELECT 
    TransactionId, 
    BillDate, 
    ParentTransaction, 
    TransactionId as topmostparent
FROM 
    @Trans 
WHERE 
    ParentTransaction IS NULL
UNION ALL
SELECT
    v.TransactionId,
    v.BillDate,
    v.ParentTransaction,
    transaction_hierarchy.topmostparent
FROM 
    @Trans v, transaction_hierarchy
WHERE 
    v.ParentTransaction = transaction_hierarchy.TransactionId
)
SELECT
    a.TransactionID as currentTransactionID, 
    count(b.BillDate) as AggregateBillCount 
FROM
    (
        SELECT
            TransactionId 
        FROM 
            @Trans d 
        WHERE 
            d.ParentTransaction is null
    )a 
LEFT JOIN
    (
        SELECT * FROM transaction_hierarchy 
    )b
ON 
    b.topmostparent = a.TransactionId and b.BillDate IS NOT NULL
GROUP BY 
    a.TransactionID 
ORDER BY 
    AggregateBillCount DESC ;

I hope this article sheds more light on the concept of recursive CTEs and readers can now utilize them and ace those SQL code assessments.