Another Holiday Puzzle - Hanukkah of Data - Part 4

Hanakkuh of Data

A database puzzle

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

** WARNING: SPOILERS AHEAD **

** DAY 4 - THE EARLY BIRD **

The investigator called the phone number you found and left a message, and a man soon called back:

"Wow, that was years ago! It was quite an elegant tapestry.

"It took a lot of patience, but I did manage to get the dirt out of one section, which uncovered a superb owl. I put it up on my wall, and sometimes at night I swear I could hear the owl hooting.

"A few weeks later my bike chain broke on the way home, and I needed to get it fixed before work the next day. Thankfully, this woman I met on Tinder came over at 5am with her bike chain repair kit and some pastries from Noah's. Apparently she liked to get up before dawn and claim the first pastries that came out of the oven.

"I didn't have any money or I would've paid her for her trouble. She really liked the tapestry, though, so I wound up giving it to her.

"I don't remember her name or anything else about her."

Can you find the bicycle fixer's phone number?

This seems to be very light on the clues.

What we know:

1. This is a woman who likes to get the first pastries from Noah's.
2. She brought her Bike Chain Repair Kit over at 5am.

This one will be tough. Let's see what we've got.

    /* Find chain repair kit */
    SELECT *
    FROM products p
    WHERE descr LIKE '%chain%'
    /*  No results found, so look for first orders of the day.  */
    

Well that was a dead end. It looks like the Bike Chain Repair Kit was a big red herring. All Bakery orders have a product.sku that starts with "BKY". I'll also check only orders before 5am.

Let's find the first orders of the day.

    ; WITH firstOrders AS (
        SELECT s1.orderid, ordered, customerid
        FROM (
            SELECT orderid, ordered, customerid
                , ROW_NUMBER() OVER ( PARTITION BY YEAR(ordered), MONTH(ordered), DAY(ordered) ORDER BY ordered ) AS rn
            FROM orders o
            WHERE DATEPART(hour,o.ordered) <=5
        ) s1
        WHERE s1.rn = 1
    )
    , ordersFromBakery AS (
        SELECT fo.orderid, fo.customerid
        FROM orders_items oi
        INNER JOIN firstOrders fo ON oi.orderid = fo.orderid
        WHERE oi.sku LIKE 'BKY%'
    )
    SELECT c.name, count(*) AS cnt
    FROM customers c
    INNER JOIN ordersFromBakery ofb ON c.customerid = ofb.customerid
    GROUP BY c.name

That came back with 59 rows with no standouts. Let's simplify this query.

    SELECT TOP 1 c.name, c.phone, COUNT(oi.sku) AS cnt
    FROM customers c
    INNER JOIN orders o ON c.customerid = o.customerid
    INNER JOIN orders_items oi ON o.orderid = oi.orderid
    WHERE oi.sku LIKE 'BKY%'
        AND DATEPART(hour,o.ordered) < 5
    GROUP BY c.name, c.phone
    ORDER BY cnt DESC

    /*
    name          phone         cnt
    Renee Harmon  607-231-3605  5
    */

That one came back with 177 rows, but sorting by the count leaves only 1 outlier. She wasn't a very big outlier.

Checking her phone number shows that she is the right one. I'll want to look at this one again to see if I missed something.

GO TO DAY 5

BACK TO PART 1
BACK TO PART 2
BACK TO PART 3