Hacker Rank - SQL Print all the prime numbers less than 1000 on one line separated by an ampersand

Hacker Rank has tasked the budding Data Engineer or Analyst with printing all the prime numbers less than 1000 utilising SQL.

There are plenty of algorithms for working out if a number is a prime number. However, most of these algorithms operate with loops or work better with object-oriented languages rather than a set-based language such as SQL.

So, is it possible to solve the following HackerRank problem in a set-based fashion?

Absolutely!

Here is the problem from HackerRank.

Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator (instead of a space).

For example, the output for all prime numbers less than 10 would be:

The first thing we need to do is populate the set with all numbers that are less than or equal to 1.

We initialize a common table expression called Numbers and populate it with the numbers from 1-1000 in the following way:

Hint, use excel to assist you with this part.

The actual code to complete this question is below:

Dealing with the innermost query first:

There is a self-join occurring on the numbers table - Numbers N1 is being joined to Numbers N2 where the N1 Number is less than the N2 number.

We are excluding the number 1 from n1 as it will always be divisible by the other number.

We are taking the minimum modulus of n2 % n1 and grouping by the number n2. If the minimum modulus does not equal zero then this means that the n2 number when divided by all numbers less than it excluding 1 always had a remainder and is therefore a prime number.

This result set is placed in a derived table, a string aggregation function is used on all numbers where the min_modulus != 0 and the edge case of number 2 is accounted for.

There we have it, printing all prime numbers less than 1000 using set based logic.

Try it out yourself!

Code is below for reference:

WITH Numbers as (

SELECT 1 as number UNION ALL

SELECT 2 as number UNION ALL

SELECT 3 as number UNION ALL

SELECT 4 as number ...

)

SELECT CONCAT('2&',string_agg(number,'&'))

FROM (

SELECT

n2.number,

min(n2.number % n1.number) as min_modulus

from numbers as N1 INNER JOIN numbers as N2

on N1.number < N2.number

and n1.number != 1

GROUP BY n2.number

--ORDER BY n2.number

) as X

WHERE min_modulus != 0