Puzzle of the week #2

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.

You need to write a single SELECT statement to generate the schedule for the current calendar month.

The output (for Feb-2016) should look like this:

   Anna      Betty      Carla    Daniela
------- ---------- ---------- ----------
      4          2          1          3
      8          6          5          7
     12         10          9         11
     16         14         13         15
     18         20         19         17
     22         24         23         21
     26         28         27         25
                                      29

or this:

 Anna      Betty      Carla    Daniela
----- ---------- ---------- ----------
    3          1          4          2
    8          6          5          7
   11         10          9         12
   16         13         14         15
   18         19         20         17
   21         23         24         22
   26         28         25         27
              29

Remember, the output is random, which means that subsequent execution of the same query should produce different results.

 

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

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.


					
Advertisements

7 thoughts on “Puzzle of the week #2

  1. Anurag Singh February 26, 2016 / 1:26 pm

    set columnsize 20;

    select
    ListAgg(anna,’,’) within group (order by anna) Anna,
    ListAgg(Betty,’,’) within group (order by Betty) Betty,
    ListAgg(Carla,’,’) within group (order by Carla) Carla,
    ListAgg(Daneila,’,’) within group (order by Daneila) Daneila
    from
    (
    select case
    when ename=’Anna’ then rn
    end Anna,
    case
    when ename=’Betty’ then rn
    end Betty,
    case
    when ename=’Carla’ then rn
    end Carla,
    case
    when ename=’Daneila’ then rn
    end Daneila,
    1 false_col
    from
    (
    select rn,ename from
    (
    select case
    when rrn>=1 and rrn7 and rrn14 and rrn21 and rrn<=30 then 'Daneila'
    end ename,rn
    from
    (
    select rownum rrn,rn from (
    select rownum rn from all_objects where rownum<=30 order by dbms_random.value))
    )
    order by rn
    )
    )
    group by false_col;

    • Zahar Hilkevich February 26, 2016 / 3:13 pm

      Please correct your query, it misses some > and < operators. In any case, your output should have 4 columns with 7-8 rows.

  2. Anurag Singh February 27, 2016 / 5:12 am

    With T as (
    select
    ListAgg(anna,’,’) within group (order by anna) Anna,
    ListAgg(Betty,’,’) within group (order by Betty) Betty,
    ListAgg(Carla,’,’) within group (order by Carla) Carla,
    ListAgg(Daneila,’,’) within group (order by Daneila) Daneila
    from
    (
    select case
    when ename=’Anna’ then rn
    end Anna,
    case
    when ename=’Betty’ then rn
    end Betty,
    case
    when ename=’Carla’ then rn
    end Carla,
    case
    when ename=’Daneila’ then rn
    end Daneila,
    1 false_col
    from
    (
    select rn,rrn,ename from
    (
    select case

    when rrn>=1 and rrn7 and rrn14 and rrn21 and rrn<=28 then 'Daneila'
    when rrn=29 and rand25 and rand50 and rand75 and rand<=100 then 'Daneila'
    end ename,rn,rrn
    from
    (
    select rn,rrn,substr((avg (ran) over ( partition by ff order by rrn rows between unbounded preceding and unbounded following)),3,2) rand ,ff from
    (
    select rownum rrn,rn,trunc(dbms_random.value(1,5)) ran,1 ff from
    (
    select rownum rn from all_objects where rownum<=29 order by dbms_random.value
    )
    )
    )
    )
    order by rn

    )
    )
    group by false_col
    )
    select replace(substr(anna,instr(','||anna||',',',',1,level) ,instr(','||anna||',',',',1,(level+1))-instr(','||anna,',',1,level)),',') ANNA,
    replace(substr(Betty,instr(','||Betty||',',',',1,level) ,instr(','||Betty||',',',',1,(level+1))-instr(','||Betty,',',1,level)),',') BETTY,
    replace(substr(CARLA,instr(','||CARLA||',',',',1,level) ,instr(','||CARLA||',',',',1,(level+1))-instr(','||CARLA,',',1,level)),',') CARLA,
    replace(substr(DANEILA,instr(','||DANEILA||',',',',1,level) ,instr(','||DANEILA||',',',',1,(level+1))-instr(','||DANEILA,',',1,level)),',') DANEILA
    from T connect by level<9;

    • Zahar Hilkevich March 4, 2016 / 2:47 pm

      Some of the greater/less operators got lost due to HTML conversion, so the syntax wise the query does not work. The idea is clear though it is overly complex.

  3. chetan March 1, 2016 / 4:42 am

    select sum(ALLEN) as ALLEN,sum(WARD) as WARD,sum(JONES) as JONES,sum(SMITH) as SMITH
    from
    (
    select grp,
    case when name=’ALLEN’ then dt else null end as ALLEN,
    case when name=’WARD’ then dt else null end as WARD ,
    case when name=’JONES’ then dt else null end as JONES ,
    case when name=’SMITH’ then dt else null end as SMITH

    from
    (select level as dt, mod(level,4) as jn,floor(level/4.1) as grp from dual connect by level<=(SELECT TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,+1)), 'DD')) FROM DUAL))tb1 left outer join
    (
    select rownum-1 as jn,name
    from
    (
    select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) as name from dual
    connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null
    order by dbms_random.value
    )
    )tb2 on tb1.jn=tb2.jn
    order by dt
    )
    group by grp
    order by grp

  4. Zahar Hilkevich March 4, 2016 / 2:49 pm

    It is almost correct. March-31st is lost for some reason. See my next post for more concise solutions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s