SQL Challenge: Draw The Triangle 1

(Easy) HackerRank Challenge

(Easy) HackerRank Challenge

Challenge: P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):

* * * * *
* * * *
* * *
* *
*

Write a query to print the pattern P(20).

In this article, I try explain two solutions in MS SQL using WHILE statement approach and recursive CTE approach. There are some other hacky solutions for this challenge but I decided to skip them as I couldn’t perceive any educational value for them.

Solution 1:

The first solution uses WHILE statement. WHILE is a control-flow statement that allows you to execute a block code (starts with BEGIN and finishes with END) repeatedly as long as the specified condition is TRUE.

• First DECLARE a variable and give it an initial value. A variable stores some information temporarily during the execution of code.

• Then start the WHILE statement with the condition @counter > 0. The code block is to PRINT a replication of '* '. As you can see, in each repetition, we decrease the counter value by 1.

DECLARE @counter INT = 20
WHILE (@counter > 0) 
BEGIN
   PRINT REPLICATE('* ', @counter) 
   SET @counter = @counter - 1
END

Solution 2 (In MS SQL):

Here, I solve this problem with recursive CTE, which references itself. In recursive CTE, it repeatedly executes and returns subsets of data until it returns the complete result set.

Here is the final solution.

WITH Recursive_CTE AS (
    SELECT 20 AS counter
    UNION ALL
    SELECT counter - 1
    FROM Recursive_CTE
    WHERE counter > 0
)
SELECT REPLICATE('* ', counter)
FROM Recursive_CTE

Now, let’s dig more into the recursive CTE code. It can be broken down into three parts:

Part 1: Initial query to set the base result. The base result is also known as anchor member. In this recursive CTE, the anchor member is 20.

SELECT 20 AS counter

Part 2: A recursive query that calls the CTE and perform a recursive action using the anchor member. Keep in mind that this recursive query must be UNION ALL-ed with the anchor member.

UNION ALL
    SELECT counter - 1
    FROM Recursive_CTE

Part 3: defining a termination condition for the recursive query. In this problem, the termination condition is when the counter becomes zero.

WHERE counter > 0

As you can see the whole goal of recursive CTE in this solution is to create a list of 20 numbers.

If you found this article helpful, share it with your friends and colleagues. If you have any other questions, you can find me on Linkedin or send me an email smohajer85@gmail.com.