Another Holiday Puzzle - Hanukkah of Data - Part 6

Hanakkuh of Data

A database puzzle

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

** WARNING: SPOILERS AHEAD **

** DAY 6 - THE BARGAIN HUNTER **

"Why yes, I did have that rug for a little while in my living room! My cats can't see a thing but they sure chased after the squirrel on it like it was dancing in front of their noses.

"It was a nice rug and they were surely going to ruin it, so I gave it to my cousin, who was moving into a new place that had wood floors.

"She refused to buy a new rug for herself - she said they were way too expensive. She's always been very frugal, and she clips every coupon and shops every sale at Noah's Market. In fact I like to tease her that Noah actually loses money whenever she comes in the store.

"I think she's been taking it too far lately though. Once the subway fare increased, she stopped coming to visit me. And she's really slow to respond to my texts. I hope she remembers to invite me to the family reunion next year."

Can you find her cousin's phone number?

Hmmm.... seems like there's some misdirection language in here. The Cat Lady talks about her cousin with wood floors. That's not really tracked in our data, so that can't really be a clue. The only thing she really says though is that her cousin is very cheap and shops only sales at Noah's Market. I think this one took sparse clues to a whole new level.

What we know:

1. Cousin is very cheap.
2. Cousin shops every sale at Noah's Market. She mentions Noah loses money when she comes in, but is that a clue?

These "clues" are getting very vague. They require a significant amount of interpretation of what the text even says. I'm not even sure exactly what clues this one is giving me.

The only clue I really see is that this Cousin shops all sales at Noah's. There doesn't seem to be any direct indicator of what a sale is, but there is a product.wholesale_cost column and an orders_items.unit_price columns. Hopefully, if I compare these numbers and something will pop out.

    /* Look for sales orders that were less than normal Product price. */
    SELECT *
    FROM orders_items oi
    INNER JOIN products p ON oi.sku = p.sku
    WHERE oi.unit_price < p.wholesale_cost

Good. I got back over 2000 rows, so this should be a way I can figure out what was on sale.

    /* Who made those Orders */
    ; WITH salesOrders AS (
        SELECT TOP 1 o.customerid, count(*) AS cnt
        FROM orders_items oi
        INNER JOIN products p ON oi.sku = p.sku
        INNER JOIN orders o ON oi.orderid = o.orderid
        WHERE oi.unit_price < p.wholesale_cost
        GROUP BY o.customerid
        ORDER BY cnt DESC
    )
    SELECT c.customerid, c.name, c.phone
    FROM customers c
    INNER JOIN salesOrders so ON c.customerid = so.customerid

    /*
    customerid  name         phone
    4167        Sherri Long  585-838-9161
    */
    

After GROUPing, JOINing and ORDERing those records to find who had the most orders on sale, I came back with only one record. Entering her phone number shows her as the correct answer again.

This one was mind-bogglingly difficult just to get what the clue was. It required a little bit of looking at the words in the problem and then checking the data to realize there were differences in pricing between sale and wholesale. These clues make you think about more than just data.

GO TO DAY 7

BACK TO PART 1
BACK TO PART 2
BACK TO PART 3
BACK TO PART 4
BACK TO PART 5