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

November 4, 2009

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.