SQL Quiz: Win two e-books (Essbase & BPEL)

Uli Bethke Books, Oracle, SQL for Analysis

We'll do something completely different today and have a quiz. And you can even win something. Thanks to the guys at Packt Publishing I have two e-books to giveaway.

Oracle Essbase 9 Implementation Guide

and

Business Process Execution Language for Web Services

PacktLogo

When you visit their website make sure to have a look at their free section. They are always looking for bloggers to review books.

In medias res.

Here comes the question:

In retail sales analytics, business folks often need to know on which day a customer bought the item with the highest amount sold. How can we achieve this with pure SQL on the Oracle database? In Oracle there are at least three different ways to answer this question. Two of the solutions use analytic functions. The third one uses a self-join. In the resultset I would like to see this on a customer by customer basis. For our sample data below I would like to get the date on which the amount_sold was largest together with the cust_id. This should return two records on for cust_id 2 and one for cust_id 4.

And here comes the data.

The first two readers who post me two correct answers will win one of the books. Please let me know which book you prefer and I'll try to accomodate this.

Tip: Two steps will get you to the right answer. First you need to find the highest amount sold for a customer and from there, get the date on which this transaction occured.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.