Special announcement: Invite your friends and co-workers to the spring conference on May 21 and register them as your guests. If your guests attend the conference, you and your guests will each receive a complimentary copy of How to Win Friends and Influence People, the timeless book by Dale Carnegie which has sold more than 15 million copies.
The Fourth International NoCOUG SQL Challenge has been published in the May issue of the NoCOUG Journal available at http://www.nocoug.org/Journal/NoCOUG_Journal_201505.pdf. Shakespeare’s story of Romeo and Juliet plays out in an American setting. In the NoCOUG version, Romeo sings American songs to Juliet and gives her gourmet dipped swizzled strawberries for her birthday. The bard of Avon must be rolling in his grave laughing.
Entries should be attached as a comment to this announcement. Please use the code tag to delimit SQL code. Judging criteria will include correctness, originality, efficiency, portability, readability, and order of receipt. The winner will receive an Apple Watch Sport or gift card of equal value. The August Order of the Wooden Pretzel will also be conferred on the winner, in keeping with the celebrated comment by Steven Feuerstein in the August 2006 issue of the NoCOUG Journal: “Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.” The current knights of the August Order of the Wooden Pretzel—Alberto Dell’Era (Italy), Andre Araujo (Australia), Rob van Wijk (Netherlands), Ilya Chuhnakov (Russia), and Lukasz Pluta (Poland)—are not eligible to participate. Additional prizes may be awarded at the discretion of NoCOUG. The contest will close at a time of NoCOUG’s choosing. NoCOUG and the judges reserve the right to publish and comment on any of the submissions with due credit to the originators. NoCOUG reserves the complete right to clarify, interpret, or modify the contest rules at any time. NoCOUG’s decisions are final.
About the NoCOUG SQL challenges
In an interview for the NoCOUG Journal, Steven Feuerstein was asked: “SQL is a set-oriented non-procedural language; i.e., it works on sets and does not specify access paths. PL/SQL on the other hand is a record-oriented procedural language, as is very clear from the name. What is the place of a record-oriented procedural language in the relational world?”
Steven replied: “Its place is proven: SQL is not a complete language. Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. We need more than SQL to build our applications, whether it is the implementation of business rules or application logic. PL/SQL remains the fastest and easiest way to access and manipulate data in an Oracle RDBMS, and I am certain it is going to stay that way for decades.”
To prove Steven correct, NoCOUG held three international SQL challenges. The winning entries were mind-boggling indeed.
First International NoCOUG SQL Challenge
An ancient 20-sided die was discovered in the secret chamber of mystery at Hogwash School of Es-Cue-El. A mysterious symbol was inscribed on each face of the die. The great Wizard of Odds discovered that each symbol represents a number. The great wizard discovered that the die was biased and invited all practitioners of the ancient arts of Es-Cue-El to create an Es-Cue-El spell to display the probabilities of obtaining various sums when the die was thrown “N” times in succession in a game of chance. Nine solutions were found by participants in seven countries and three continents but the winner, Alberto Dell’Era from Italy, rose above the competition by implementing Discrete Fourier Transforms and becoming the first knight of the August Order of the Wooden Pretzel. You can read an explanation of his wonderful solution here. Alberto also implemented Fast Fourier Transforms but we won’t even go there.
Second International NoCOUG SQL Challenge
An ancient manuscript titled “Love Your Data” was discovered in the secret chamber of mystery at Hogwash School of Es-Cue-El. The manuscript was covered with mysterious words and the great Wizard of Odds implored contestants to create an Es-Cue-El spell that revealed the secret message. Andre Araujo (Australia), Rob van Wijk (Netherlands), and Ilya Chuhnakov (Russia) submitted solutions and became the second, third, and fourth knights of the August Order of the Wooden Pretzel. Ilya submitted two solutions: one using the MODEL clause and one using recursive common table expressions. You can read their wonderful solutions in the 100th issue of the NoCOUG Journal.
Third International NoCOUG SQL Challenge
The Wicked Witch of the West had invited six friends to the Third Annual Witching & Wizarding Ball at Pythian Academy of Es-Cue-El & No-Es-Cue-El. Here friends made their attendance contingent upon the attendance ofothers. For examle, Burdock Muldoon, Elfrida Clagg, and Falco Aesalon all said they would come if Carlotta Pinkstone and Daisy Dodderidge both came. The Wicked Witch of the West needed an Es-Cue-El or No-Es-Cue-El spell to determine whom she needed to persuade to attend the wizarding ball in order to ensure that all her invitees attend. Master sorcerer Lukasz Plata of Poland not only solved the problem with a single SQL statement but provided a proof that his solution was correct. He became the fifth knight of the August Order of the Wooden Pretzel. You can read his wonderful solution here.
Brendan Furey
cmartin2
eaviles94
emmanuelhumblot
jimmybrock
Kim Berg Hansen
nocoug
Rafu
vbarun
Northern California Oracle Users Group 
Dataset IV should read as follows:
The reason for the two additional values is to trigger Rule 1a below:
Rule 1a: Romeo says “I don’t know when Juliet’s birthday is” (September is eliminated)
Rule 1b: Romeo says “Count Paris doesn’t know either” (May and June are eliminated)
Rule 2: Count Paris says “Now I do!” (Only July 31, August 15, and August 17 are left in contention)
Rule 3: Romeo says “Now I do too!” (Only July 31 is left in contention)
LikeLike
What if DATE’2015-05-19′ was not in that list of Dataset IV?
LikeLike
All four rules are still needed. Consider the original dataset in http://en.wikipedia.org/wiki/Cheryl%27s_Birthday (similar to Dataset II except that July 16 is used instead of July 31).
Stage 1:
Stage 2:
All days in May and June are eliminated through the application of Rule 1b.
Stage 3:
July 14 and August 14 are eliminated through the use of Rule 2. Only July 16, August 15, and August 17 are left in contention.
Stage 4
August 15 and August 17 are eliminated through the use of Rule 3. Only July 16 is left in contention.
Now remove May 19 from the original dataset in http://en.wikipedia.org/wiki/Cheryl%27s_Birthday.
Stage 1:
Stage 2:
All days in June are eliminated through the application of Rule 1b.
Stage 3:
All dates except August 17 are eliminated through the use of Rule 2. Only August 17 is left in contention.
LikeLike
One question on the 2nd scenario stag 2. How exactly Paris determines the month in a way that public could be aware. Since he couldnt rule out May but only June there are now two candidates: 13/05 (as Paris knows from 1a that it couldnt be September) and 17/08. Personally he knows which one to take and Romeo therefore too. But the public? IMHO there are two solutions possible in this case:
LikeLike
Explanation of the foregoing solution as requested here https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge/comment-page-2/#comment-407
LikeLike
Ilustration
LikeLike
I think we could explore some new possibilities. All solutions so far delivered a SELECT statement. I feel that the phrase “SQL QUERY” could also be interpreted as any kind /series of SQL. See also: http://simple.wikipedia.org/wiki/Structured_Query_Language for support of this interpretation.
So here is my try using some dml/ddl statements.
create table d as select unique to_char(DateOfBirth,’MM’) m, to_char(DateOfBirth,’DD’) t from dates;
delete from d where m in (select min(m) from d group by t having sum(1)=1);
delete from d where t in (select t from d group by t having sum(1)>1);
select * from d where m not in (select m from d group by m having sum(1)>1);
It small but is still a considerable amount larger that the best SELECT solutions yet, But I feel there is a little room for improvement left.
LikeLike
[…] Update on the Fourth International NoCOUG SQL Challenge in progress at https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge. […]
LikeLike
[…] Update on the Fourth International NoCOUG SQL Challenge in progress at https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge. […]
LikeLike
[…] Update on the Fourth International NoCOUG SQL Challenge in progress at https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge. […]
LikeLike
@James Thank you for your contributions to the competition. This is another teachable moment which demonstrates that, in situations like this, only the implementation (SQL, relational calculus, or relational algebra) is unambiguous but the plain-language functional specification is subject to interpretation. The ideal specification would be a relational calculus or relational algebra specification but there would be no further need for implementation in that case.
When Romeo says “Count Paris doesn’t know either,” he is putting himself in Count Paris’s shoes. Depending on the data set, Count Paris may or may not know whether Romeo knows Juliet’s birthday. If he knows that Romeo does not know Juliet’s birthday, we may apply Rule 1b on top or Rule 1a because then Rule 1a is redundant 🙂 But, if Count Paris does not know (just by looking at the choices) whether Romeo knows Juliet’s birthday, we must apply Rule 1a and Rule 1b separately.
In the case of the original data set in the Cheryl’s Birthday problem, it is obvious that Romeo does not know Juliet’s birthday just by looking at the choices. But in the following data set, it is uncertain whether Romeo knows Juliet’s birthday (just by looking at the choices).
The expected answer in this case is April 13.
Romeo knows that month is April but there are two choices in April so he does not know the date.
Romeo further knows that every choice in April is duplicate in another month, so Count Paris does not know the date.
Count Paris knows that the day is 13 and that it occurs in April and September. Since September is eliminated (Rule 1a), Juliet’s birthday must be on April 13. We on the other hand, do not yet know when Cheryl’s birthday is. All we can tell at this point, is that it is one of April 13, August 15, and August 17 (July 31 is not in contention because the value 31 occurs both in July as well as in August).
Romeo knows that the month is April but he does not know whether the day is 13 or 14. Since Romeo has figured out Juliet’s birthday, he realizes that Juliet’s birthday is on April 13.
Once Romeo tells us that he has figured out Juliet’s birthday, we can figure out Juliet’s birthday.
The following solution applies Rule 1b on top of Rule 1a and therefore returns an incorrect result (no rows).
select m, d, count(*) over (partition by m) as m_count from ( select m, d, count(*) over (partition by d) as d_count from ( select m, d, min(d_count) over (partition by m) as min_d_count from ( select m, d, count(*) over (partition by d) as d_count from ( select m, d, count(*) over (partition by m) as m_count, count(*) over (partition by d) as d_count from ( select distinct extract(month from dateofbirth) as m, extract(day from dateofbirth) as d from dates ) ) where m_count > 1 -- Rule 1a ) ) where min_d_count > 1 -- Rule 1b ) where d_count = 1 -- Rule 2 ) where m_count = 1 -- Rule 3 ;The following solution applies Rule 1a and Rule 1b simultaneously and therefore returns the correct solution (April 13).
select m, d from ( select m, d, count(*) over (partition by m) as m_count from ( select m, d, count(*) over (partition by d) as d_count from ( select m, d, m_count, min(d_count) over (partition by m) as min_d_count from ( select m, d, count(*) over (partition by m) as m_count, count(*) over (partition by d) as d_count from ( select distinct extract(month from dateofbirth) as m, extract(day from dateofbirth) as d from dates ) ) ) where m_count > 1 and min_d_count > 1 -- Rule 1a and Rule 1b ) where d_count = 1 -- Rule 2 ) where m_count = 1 -- Rule 3 ;Chris Goerg’s original solution returns an incorrect result (no rows)
The MODEL-clause solution posted by Chris at https://community.oracle.com/message/13090606#13090606 returns the correct result. It’s quite cryptic so perhaps Chris will explain for the benefit of those who are not familiar with the MODEL clause.
LikeLike
Can I draw a conclusion that 1a and 1b should always be applied simultaneously? The only exception is when 1a becomes “completely redundant”, but in this case the “simultaneous” solution still works.
Do you still consider this test case (which I posted on May25) an invalid one:
truncate table juliet_dates;
INSERT INTO juliet_dates VALUES (DATE ‘2000-6-17’);
INSERT INTO juliet_dates VALUES (DATE ‘2000-7-14’);
INSERT INTO juliet_dates VALUES (DATE ‘2000-7-15’);
INSERT INTO juliet_dates VALUES (DATE ‘2000-8-14’);
INSERT INTO juliet_dates VALUES (DATE ‘2000-8-15’);
INSERT INTO juliet_dates VALUES (DATE ‘2000-8-17’);
I think the answer is Aug 17 and that’s what my SQL returns.
BTW if a test case does not match the story(no answer or more than one answers), it’s still a valid one in which case a correct SQL solution should return no row or more than one rows.
LikeLike
@James The six values you chose fit the following abbreviated scenario:
Romeo declared dejectedly: “I don’t know when Juliet’s birthday is but, thank heavens, Count Paris doesn’t know either.” Count Paris exclaimed excitedly: “Now I do!” What is Juliet’s birthday?
LikeLike
The following seven data sets can be used for testing:
Data Set I (11 rows)
The expected answer is July 31
Data Set II (10 rows)
The expected answer is July 31
Data Set III (15 rows)
The expected answer is July 31
Data Set IV (12 rows)
The expected answer is July 31
Data Set V (13 rows)
The expected answers are July 31 and September 20 (ambiguous scenario)
Data Set VI (12 rows)
The expected answer is July 31
Data Set VII (14 rows)
The expected answer is April 13
LikeLike
The contest is now closed. The winner will be announced in the August issue of the NoCOUG Journal tentatively scheduled for release on Monday, August 10.
LikeLike
The August issue is available at http://www.nocoug.org/Journal/NoCOUG_Journal_201508.pdf. Comments are welcome.
LikeLike
[…] What’s that got to do with problem solving? I think that the first rule of problem solving is “If at first you don’t succeed, try, try, again.” Chris Goerg demonstrated this rule very well in the fourth International NoCOUG SQL challenge. His attempts to solve the challenge are on display for all to see at https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge/. […]
LikeLike