Medium Challenge 4: Using LISTAGG() And LEVEL() Function – Part 1

365 Days of Daily Coding: Day 5

Today was an extremely awry day as I was not able to crack two HackerRank challenges that were ranked medium. The first one, I could not comprehend the question and so I had to skip. The second one, neither was I able to answer nor was I able to understand the solution that was provided by some of the successful submitters.

The challenge was to write a query to print all the prime numbers less than or equal to 1000 and it had to be printed on a single line, and use the ampersand () character as a separator (instead of a space).

Sample: For prime numbers less than 10, the expected output was as below:

2&3&5&7

Some of the successful submission were as follows:

Solution 1:

 SELECT LISTAGG(L1,'&') WITHIN GROUP (ORDER BY L1) 
 FROM (
     SELECT L1 FROM (
         SELECT LEVEL L1 
         FROM DUAL CONNECT BY LEVEL<=1000) 
     WHERE L1 <> 1 MINUS 
     SELECT L1 FROM (SELECT LEVEL L1 FROM DUAL 
     CONNECT BY  LEVEL<=1000) A, 
     (SELECT LEVEL L2 FROM DUAL CONNECT BY LEVEL<=1000) B 
     WHERE L2<=L1 and MOD(L1,L2)=0 AND L1<>L2 AND L2<>1);

Solution 2:

 WITH numSel As (
     SELECT LEVEL num FROM dual CONNECT BY LEVEL <= 1000
 ),
 primes AS (
     SELECT a.num p
      FROM numSel a, numSel b
      WHERE b.num <= a.num
      GROUP BY a.num
      HAVING COUNT(CASE a.num/b.num WHEN Trunc(a.num/b.num) THEN 'Y' END) = 2
 )
 SELECT LISTAGG(p, '&') WITHIN GROUP (ORDER BY p)
   FROM primes;

Solution 3:

WITH t AS (SELECT LEVEL+1 n FROM DUAL CONNECT BY LEVEL < 1000) SELECT LISTAGG(t1.n,'&') WITHIN GROUP (ORDER BY t1.n) FROM t t1 WHERE NOT EXISTS  (SELECT null FROM t t2 WHERE t1.n > t2.n AND MOD(t1.n, t2.n) = 0 AND ROWNUM = 1);

Solution 4:

SELECT LISTAGG(Numerator, '&') WITHIN GROUP (ORDER BY Numerator)
   FROM (
       SELECT A.Numerator
       FROM (SELECT LEVEL AS Numerator 
             FROM DUAL 
             CONNECT BY LEVEL <= 1000) A,             
             (SELECT LEVEL AS Denominator               
             FROM DUAL WHERE LEVEL > 1 
             CONNECT BY LEVEL <= 1000) B       
             WHERE A.Numerator >= B.Denominator
       AND MOD(A.Numerator , B.Denominator) = 0
       GROUP BY A.Numerator
       HAVING COUNT(*) <= 1);

I was trying to understand the first solution by trying to break down the query like I always do but had no luck.

I familiarised myself with the functions that were being used in the solution such as ‘LISTAGG’ and ‘LEVEL’.

LISTAGG function:

From link, the Oracle/PLSQL LISTAGG function concatenates values of the measure_column for each GROUP based on the order_by_clause. The syntax for the LISTAGG function in Oracle/PLSQL is:

LISTAGG (measure_column [, 'delimiter'])
  WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]

LEVEL function:

The LEVEL function is a pseudocolumn that returns the hierarchy level in numeric format. For root row being the lowest level in a tree-like structure, it return 1 and so on. When used with ‘SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=1000’ displays number from 1 to 1000.

I will probably continue to study this query as my Day 6 and at the same time revise some of the materials I received when I had my SQL training.

Karma

Leave a comment