Another Holiday Puzzle - Hanukkah of Data - Part 8

Hanakkuh of Data

A database puzzle

Hanukkah of Data 2023 - Noah's Rug (5784)

** WARNING: SPOILERS AHEAD **

** DAY 8 - THE COLLECTOR **

"Oh that damned woman! She moved in, clogged my bathtub, left her coupons all over the kitchen, and then just vanished one night without leaving so much as a note.

Except she did leave behind that nasty carpet. I spent months cleaning one corner, only to discover a snake hiding in the branches! I knew then that she was never coming back, and I had to get it out of my sight.

"Well, I don't have any storage here, and it didn't seem right to sell it, so I gave it to my sister. She wound up getting a newer and more expensive carpet, so she gave it to an acquaintance of hers who collects all sorts of junk. Apparently he owns an entire set of Noah's collectibles! He probably still has the carpet, even.

"My sister is away for the holidays, but I can have her call you in a few weeks."

The family dinner is tonight! Can you find the collector's phone number in time?

This is the final day, and it seems way too easy. The only things that look like clues are the statement about the acquaintance who collects all sorts of junk and that he owns an "entire set of Noah's collectibles". This seems like I'm looking for someone who purchased a lot of the items that are labeled 'Noah%'.

What we know:

1. The Collector has an entire set of Noah's collectibles.

So first, I need to look at the products to verify which ones are Noah's Collectibles.

        SELECT * FROM products WHERE descr LIKE 'Noah%' OR sku LIKE 'COL%'
    

This shows that I won't be able to use products.sku, because some of the items are listed under 'COL%' and some under 'TOY%'. All of the 'COL' items are Noah's Collectibles, but the 'TOY' items aren't. But it does look like I can grab all of the collectibles if I just use product.descr LIKE 'Noah%`. Of course, this is another assumption on my part. Are the 'TOY's also Collectibles, or is it just items listed with 'COL'?

    SELECT c.customerid, c.name, c.phone
    FROM customers c
    INNER JOIN (
        SELECT TOP 1 o.customerid, count(*) AS cnt
        FROM orders o
        INNER JOIN orders_items oi ON o.orderid = oi.orderid
        INNER JOIN products p ON oi.sku = p.sku
            AND p.descr LIKE 'Noah%'
        GROUP BY o.customerid
        ORDER BY cnt DESC
    ) s1 ON c.customerid = s1.customerid

Entering the phone number shows this is correct, too.

All days have been completed.

---

"Oh yes, that magnificent Persian carpet! An absolute masterpiece, with a variety of interesting animals congregating around a Tree of Life. As a collector, I couldn't believe when it fell into my lap.

"A friend of mine had taken it off her brother's hands, and she didn't know what to do with it. I saw her one day, and she was about to put an old rug out at the curb. It looked like it had been through a lot, but it was remarkably not that dirty. It still took quite a bit of effort and no small amount of rug cleaner, but ultimately, I managed to get the last bits of grime out of it.

"I actually live right down the street from Noah's Market - I'm a huge fan and I shop there all the time! I even have a one-of-a-kind scale model of Noah's Ark that makes a complete set of Noah's collectibles.

"I would love for Noah to have his rug once again to enjoy."

** CONCLUSION **

These were interesting puzzles. The querying part wasn't that difficult, but just figuring out what was being asked was extremely difficult for some of these. I'm not sure that this really tested my SQL skills as much as it tested my ability to determine what it was that the customer was actually asking for.

Overall, it was pretty fun. Now that we've completed the first batch, I think I'll try to see what the speed run is like.

BACK TO PART 1
BACK TO PART 2
BACK TO PART 3
BACK TO PART 4
BACK TO PART 5
BACK TO PART 6
BACK TO PART 7
BACK TO PART 8