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:
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:
/**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 NULLUNION ALLSELECT
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.