Three Solutions to Puzzle of the Week #18

Puzzle of the Week #18:

There are gaps in values of empno column in emp table. The challenge is to find all the gaps within the range of employee numbers already in use. All numbers should be grouped in ranges (see expected result section below). A single SELECT statement against emp table is expected.

Expected Result:

Avail. Emp Numbers
------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933

Solutions

#1: Using GROUP BY Over ROWNUM expression

WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT MIN(empno) || ' - ' || MAX(empno) "Avail. Emp Numbers"
FROM y
GROUP BY empno-ROWNUM
ORDER BY empno-ROWNUM;

Avail. Emp Numbers
--------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933

#2: Using MATCH_RECOGNIZE (Oracle 12c and up;  credit to Zohar Elkayam)

WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT firstemp || ' - ' || lastemp "Avail. Emp Numbers"
FROM y
MATCH_RECOGNIZE (
  ORDER BY empno
  MEASURES
   A.empno firstemp,
   LAST(empno) lastemp
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B*)
  DEFINE B AS empno = PREV(empno)+1
);

#3: Using LEAD Analytic function (credit to Krishna Jamal)

WITH x AS
(
SELECT empno, LEAD(empno,1) OVER(ORDER BY empno) lead_empno
FROM emp
)
SELECT (empno+1) || ' - ' || (lead_empno-1) "Avail. Emp Numbers"
FROM x
WHERE empno+1!=lead_empno;

Also, see a very similar Puzzle of the Week #15 for more workarounds.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Round-Robin Schedule SQL Puzzle

Puzzle: With a single SELECT statement create a schedule of play for a round-robin tournament. The query should work for odd or even number of players. For odd number of players, the player with bye should be listed last for that round.

You can read about round-robin tournament definition here: https://en.wikipedia.org/wiki/Round-robin_tournament

Expected Results:

4 players:

 ROUND PAIRING
------ --------
     1 1 vs 2
     1 3 vs 4
     2 1 vs 4
     2 2 vs 3
     3 3 vs 1
     3 4 vs 2

OR

ROUND PAIRING
----- --------
    1 1 vs 2
      3 vs 4

    2 1 vs 4
      2 vs 3

    3 3 vs 1
      4 vs 2

5 players:

  ROUND PAIRING
------ --------
     1 5 vs 1
     1 4 vs 2
     1 3 - bye
     2 1 vs 2
     2 5 vs 3
     2 4 - bye
     3 1 vs 4
     3 2 vs 3
     3 5 - bye
     4 2 vs 5
     4 3 vs 4
     4 1 - bye
     5 3 vs 1
     5 4 vs 5
     5 2 - bye

OR

ROUND PAIRING
----- -------
    1 5 vs 1
      4 vs 2
      3 - bye

    2 1 vs 2
      5 vs 3
      4 - bye

    3 1 vs 4
      2 vs 3
      5 - bye

    4 2 vs 5
      3 vs 4
      1 - bye

    5 3 vs 1
      4 vs 5
      2 - bye

6 players:

ROUND PAIRING
----- --------
    1 5 vs 1
    1 4 vs 2
    1 3 vs 6
    2 1 vs 2
    2 5 vs 3
    2 6 vs 4
    3 1 vs 4
    3 2 vs 3
    3 5 vs 6
    4 1 vs 6
    4 2 vs 5
    4 3 vs 4
    5 3 vs 1
    5 6 vs 2
    5 4 vs 5

OR

ROUND PAIRING
----- -------
    1 5 vs 1
      4 vs 2
      3 vs 6

    2 1 vs 2
      5 vs 3
      6 vs 4

    3 1 vs 4
      2 vs 3
      5 vs 6

    4 1 vs 6
      2 vs 5
      3 vs 4

    5 3 vs 1
      6 vs 2
      4 vs 5

 Solution (SQL*Plus script):

accept players prompt "Enter the number of players: "
set pagesize 100
break on "ROUND" skip 1
col pairing for a10

WITH prompt AS (
   SELECT &players AS oplayers
   FROM dual
), x AS (
SELECT LEVEL n, oplayers,  oplayers + MOD(oplayers,2) AS players
FROM dual, prompt
CONNECT BY LEVEL<=oplayers + MOD(oplayers,2)-1
), w AS (
SELECT a.n AS rnd, 1+MOD(a.n+b.n,a.players-1) AS p,
       CASE WHEN ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY 1)<=a.players/2 THEN ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY a.n, b.n )-1
            ELSE a.players-ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY 1)
       END AS match_id, a.oplayers  
FROM x a, x b 
UNION ALL
SELECT n, players AS p, 0 AS match_id, oplayers
FROM x
)
SELECT rnd AS "ROUND", 
       CASE WHEN MAX(p)>oplayers THEN  MIN(p) || ' - bye ' 
            WHEN MOD(MAX(p)-MIN(p),2)=1 THEN MIN(p) || ' vs ' || MAX(p)
            ELSE MAX(p) || ' vs ' || MIN(p) 
       END AS pairing
FROM w
GROUP BY rnd, match_id, oplayers
ORDER BY 1, CASE WHEN MAX(p)>oplayers THEN oplayers ELSE MIN(p) END;

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Build Department Size Bar Chart Report with a Single SELECT statement

Build Department Size Bar Chart Report with a Single SELECT statement.

Recently, when I was working on a completely different problem, I realized that in some cases, SQL plus will allow us generating simple bar graphs on selected data. I challenged myself to build a “graph” that will look like this:

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
   X  X
   X  X
      X

Each column represents a “bar” and its “height” represents the number of employees working in a corresponding department.

Below, you will find several strategies for building such “graphs” as well as generating “reverse graphs”:

  N 10 20 30
--- -- -- --
  6       X
  5    X  X
  4    X  X
  3 X  X  X
  2 X  X  X
  1 X  X  X

Method/Workaround 1: Pivot simulation

WITH x AS (SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
), y AS (
SELECT LEVEL n, 'X' as c
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
SELECT MAX(CASE WHEN x.deptno=10 THEN y.c END) "10",
       MAX(CASE WHEN x.deptno=20 THEN y.c END) "20",
       MAX(CASE WHEN x.deptno=30 THEN y.c END) "30"
FROM y JOIN x ON y.n<=x.cnt
GROUP BY y.n
ORDER BY y.n
/

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
   X  X
   X  X
      X

Method/Workaround 2: Pivot

SELECT "10","20","30"
FROM (
  WITH x AS (
  SELECT deptno, COUNT(*) cnt
  FROM emp
  GROUP BY deptno
  )
  SELECT LEVEL n, deptno, 'X' as c
  FROM dual, x 
  WHERE LEVEL<=x.cnt
  CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
PIVOT (
   MAX(c)
   FOR deptno IN (10 "10",20 "20",30 "30")
)
ORDER BY N
/

Method/Workaround 3: Leverage Department/Employee Roll Puzzle:

SELECT "10","20","30"
FROM (
  SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn, 
         deptno, 'X' c
  FROM emp
)
PIVOT
(
  MAX(c)
  FOR deptno IN (10,20,30)
)
ORDER BY rn;

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
   X  X
   X  X
      X

Method/Workaround 4:

WITH x AS (
SELECT CASE WHEN deptno=10 THEN 'X' END "10",
       CASE WHEN deptno=20 THEN 'X' END "20",
       CASE WHEN deptno=30 THEN 'X' END "30",
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn
FROM emp
)
SELECT MAX("10") AS "10",
       MAX("20") AS "20",
       MAX("30") AS "30"
FROM x
GROUP BY rn
ORDER BY rn;

Reverse Bar Chart Report:

Method/Workaround 1:

WITH x AS (SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
), y AS (
SELECT LEVEL n, 'X' as c
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
SELECT n,
       MAX(CASE WHEN x.deptno=10 THEN y.c END) "10",
       MAX(CASE WHEN x.deptno=20 THEN y.c END) "20",
       MAX(CASE WHEN x.deptno=30 THEN y.c END) "30"
FROM y JOIN x ON y.n<=x.cnt
GROUP BY y.n
ORDER BY y.n DESC;

  N 10 20 30
--- -- -- --
  6       X
  5    X  X
  4    X  X
  3 X  X  X
  2 X  X  X
  1 X  X  X

Method/Workaround 2:

SELECT *
FROM (
  WITH x AS (
  SELECT deptno, COUNT(*) cnt
  FROM emp
  GROUP BY deptno
  )
  SELECT LEVEL n, deptno, 'X' as c
  FROM dual, x 
  WHERE LEVEL<=x.cnt
  CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
PIVOT (
   MAX(c)
   FOR deptno IN (10 "10",20 "20",30 "30")
)
ORDER BY N DESC;

Method/Workaround 3: Leverage Department/Employee Roll Puzzle:

SELECT *
FROM (
  SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) n, 
         deptno, 'X' c
  FROM emp
)
PIVOT
(
  MAX(c)
  FOR deptno IN (10,20,30)
)
ORDER BY n DESC;

Method/Workaround 4:

WITH x AS (
SELECT CASE WHEN deptno=10 THEN 'X' END "10",
       CASE WHEN deptno=20 THEN 'X' END "20",
       CASE WHEN deptno=30 THEN 'X' END "30",
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) n
FROM emp
)
SELECT n, 
       MAX("10") AS "10",
       MAX("20") AS "20",
       MAX("30") AS "30"
FROM x
GROUP BY n
ORDER BY n DESC; 

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the week #4 Solution

Round 1 Playoff Schedule

N teams (N between 1 and 32) just finished the season and are all qualified for the playoff. If the number of teams were 2, 4, 8, 16, or 32 (powers of 2), the playoff schedule would be trivial: 1st team plays vs last team, 2nd – vs 2nd from the last, etc. However, there is no guarantee that the number of teams would be a power of 2. The challenge is to write a single SELECT statement that accepts the number of teams as a parameter and generates the round 1 pairings.

There should be 1, 2, 4, 6, or 16 teams (power of 2) in the 2nd round.

Solution

WITH x AS (
  SELECT &teams p
  FROM dual
), y AS (
SELECT ROWNUM home, POWER(2,CEIL(LOG(2,p)))-ROWNUM+1 away, 
       POWER(2,CEIL(LOG(2,p))) maxp
FROM dual, x
CONNECT BY LEVEL<=POWER(2,CEIL(LOG(2,p))-1)
)
SELECT CASE WHEN away<=p AND p>1 THEN ROWNUM+p-maxp END AS "Game #",
       CASE WHEN away>p AND p>1 THEN 'Team-' || home || ' advances to Round 2'
            WHEN p=1 THEN 'Team-1 is a Champion!'
            ELSE 'Team-' || home || ' vs Team-' || away
       END AS "Playoff Round 1 Pairings"
FROM y, x
ORDER BY 1 NULLS LAST, home

Explanation

The trick here was to figure out which teams should be playing and which simply advance to the Round 2. Suppose that we have a power of 2 number of teams. Then top 1st team plays against the bottom 1st, top 2ng vs bottom 2nd, etc. If there are N teams, then we will have N/2 games. This is the simplest case. What if we have 6 teams? We should add 2 fake teams to “round up” the number of teams to the nearest power of 2 that is greater or equal to N. For 6 teams, we round up to 8. Those 2 fake teams should be paired against top 2 teams, and this gives us an answer which teams should advance to the Round 2 without playing. General rule, if we have to add K “faked teams” to “round up” to the nearest power of 2 number, this means that top K teams advance to the next round without playing.

We used CEIL, LOG, and POWER functions to get the next power of 2 for any whole N:

POWER(2,CEIL(LOG(2,p)))

 

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Use TRUNC function to generate various date ranges

How to use TRUNC function to generate a date range? As it will be demonstrated below, it is very straightforward and simple to grasp. Let’s start from the very beginning

Step 1. Generate Numeric Range

First, you need to understand how to generate a numeric range. Let say, you need to generate a range of integers from 1 to 10. There are 2-3 traditional ways to do it.

Method 1: Use Connect By clause:

SQL> SELECT LEVEL, ROWNUM
  2  FROM dual
  3  CONNECT BY LEVEL<=10;

     LEVEL     ROWNUM
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10

Likewise, you can use ROWNUM in the CONNECT BY:

SQL> SELECT LEVEL, ROWNUM
  2  FROM dual
  3  CONNECT BY ROWNUM<=10;

     LEVEL     ROWNUM
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10

Method 2: Using some data dictionary table that is always available:

SQL> SELECT ROWNUM
  2  FROM all_objects
  3  WHERE ROWNUM<=10;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

If the table does not have enough rows, you can use a Cartesian Product (emp table only has 14 rows):

SQL> SELECT ROWNUM
  2  FROM emp, emp
  3  WHERE ROWNUM<=16;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16

The above method involves disk I/O which makes it fairly inefficient compare to the CONNECT BY method.

Method 3: Using Recursive WITH clause:

SQL> WITH x(rnum) AS (
  2  SELECT 1 AS rnum
  3  FROM dual
  4  UNION ALL
  5  SELECT rnum+1
  6  FROM x
  7  WHERE rnum

This method first became available in Oracle 11.2 when Oracle introduced support to the Recursive WITH clause. The good thing about this method is that it is often available in other RDBMS (SQL Server, Teradata, etc.) that don’t have a support for CONNECT BY.

Step 2. Convert the Numeric range from Step 1 into a Date Range.
This step is very simple since we know that we can easily add days to a specific date value. For simplicity, we will stick with CONNECT BY method of numeric range generation:

SQL> SELECT SYSDATE + LEVEL - 1 AS day
  2  FROM dual
  3  CONNECT BY LEVEL<=10;

DAY
---------
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16
13-MAR-16
14-MAR-16
15-MAR-16
16-MAR-16
17-MAR-16

Now, we will want to generate very specific data ranges.

Problem: Generate the date range for current week from Sunday to Saturday.

All we need to know is how to get the first day of the week. We have explained this in details in a previous post:

SQL> SELECT TRUNC(SYSDATE, 'DAY') week_start
  2  FROM dual;

WEEK_STAR
---------
06-MAR-16

Now, we will generate the range for the week knowing that the week has 7 days:

SQL> SELECT TRUNC(SYSDATE, 'DAY')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY LEVEL<=7;

DAY
---------
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16

Do we really need to know how many days our desired date range has? The answer is NO. All we need is to ensure that every subsequent day remains in the same date interval (same week – in our case). How can we identify the week – by its first day!

SQL> SELECT TRUNC(SYSDATE, 'DAY')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY TRUNC(TRUNC(SYSDATE, 'DAY')+LEVEL-1, 'DAY')=TRUNC(SYSDATE, 'DAY')
  4  /

DAY
---------
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16

As long as subsequent day’s first day of the week remains the same as the current day’s first day of the week, we can continue the recursion!

Using this idea, generation of the month’s date range is even simpler as we can use either TRUNC function to get the first day of the month, or TO_CHAR(…, ‘MM’) – to extract the month:

SQL> SELECT TRUNC(SYSDATE, 'MON')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'MON')+LEVEL-1, 'MM')=TO_CHAR(SYSDATE, 'MM')
  4  /

DAY
---------
01-MAR-16
02-MAR-16
03-MAR-16
...
30-MAR-16
31-MAR-16

How about getting the date range for the current Quarter?

SQL> SELECT TRUNC(SYSDATE, 'Q')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'Q')+LEVEL-1, 'Q')=TO_CHAR(SYSDATE, 'Q')
  4  /

DAY
---------
01-JAN-16
02-JAN-16
03-JAN-16
...
30-MAR-16
31-MAR-16

A Year?

SQL> SELECT TRUNC(SYSDATE, 'YY')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'YY')+LEVEL-1, 'YY')=TO_CHAR(SYSDATE, 'YY')
  4  /

DAY
---------
01-JAN-16
02-JAN-16
03-JAN-16
...
30-DEC-16
31-DEC-16

As you can see, the solution is very simple!

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.