Last week we presented the 2nd puzzle of our contest, Puzzle of the Week. Today we publish correct answers for that puzzle:
Thanks to all who accepted the challenge!
Dish washing schedule puzzle:

Four roommate students, Anna, Betty, Carla, and Daniela decided to make a “Dish washing schedule”. Every day one of the girls should do all the dishes. The challenge is to make a schedule for the next month that will spread the responsibilities among the girls as evenly as possible. At the same time the schedule should be completely random. 
Solution #1: Traditional approach for mimicking pivoted report.
WITH x AS (
SELECT FLOOR((LEVEL1)/4) id,
LEVEL AS d,
RANK()OVER(PARTITION BY FLOOR((LEVEL1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual
CONNECT BY LEVEL<=32
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM x
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id
Sample output #1:
Anna Betty Carla Daniela
   
2 1 4 3
6 5 8 7
11 9 10 12
14 15 16 13
20 19 18 17
24 21 22 23
28 27 26 25
31 30 29
Sample output #2 (after rerunning the same query):
Anna Betty Carla Daniela
   
3 4 2 1
7 5 6 8
12 9 11 10
16 14 13 15
20 19 18 17
23 24 21 22
25 27 26 28
31 29 30
Solution #2: Using Recursive WITH clause for range generation:
WITH x(d) AS (
SELECT 1 AS d
FROM dual
UNION ALL
SELECT d+1
FROM x
WHERE d<32
), y AS (
SELECT FLOOR((d1)/4) id,
CASE WHEN d<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN d END d,
RANK()OVER(PARTITION BY FLOOR((d1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM x
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM y
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id
Sample output #1:
Anna Betty Carla Daniela
   
1 4 2 3
8 7 6 5
11 10 9 12
15 14 13 16
18 19 20 17
24 22 21 23
25 27 28 26
29 30 31
Sample output #2 (after rerunning the same query):
Anna Betty Carla Daniela
   
1 4 3 2
5 6 7 8
11 12 10 9
13 15 16 14
20 19 17 18
22 23 24 21
28 26 27 25
29 30 31
Solution #3: Using PIVOT clause:
SELECT "1" AS "Anna","2" AS "Betty", "3" AS "Carla", "4" AS "Daniela"
FROM (
SELECT FLOOR((LEVEL1)/4) id,
CASE WHEN LEVEL<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN LEVEL END AS d,
RANK()OVER(PARTITION BY FLOOR((LEVEL1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual
CONNECT BY LEVEL<=4*CEIL(31/4)
)
PIVOT
(
MAX(d)
FOR rk IN (1,2,3,4)
)
ORDER BY id;
Sample output #1:
Anna Betty Carla Daniela
   
4 1 3 2
7 6 8 5
10 9 11 12
16 14 13 15
20 19 17 18
23 22 21 24
26 25 27 28
31 29 30
Sample output #2 (after rerunning the same query):
Anna Betty Carla Daniela
   
4 1 3 2
8 7 5 6
11 10 12 9
16 13 14 15
20 19 17 18
21 22 23 24
28 26 27 25
31 29 30
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.