Another Holiday Puzzle - Hanukkah of Data - Part 5

Hanakkuh of Data

A database puzzle

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

** WARNING: SPOILERS AHEAD **

** DAY 5 - THE CAT LADY **

"Yes, I did have that tapestry for a little bit. I even cleaned a blotchy section that turned out to be a friendly koala.

"But it was still really dirty, so when I was going through a Marie Kondo phase, I decided it wasn't sparking joy anymore.

"I listed it on Freecycle, and a woman in Staten Island came to pick it up. She was wearing a "Noah's Market" sweatshirt, and it was just covered in cat hair. When I suggested that a clowder of cats might ruin such a fine tapestry, she looked at me funny. She said "I only have ten or eleven cats, and anyway they are getting quite old now, so I doubt they'd care about some old rug."

"It took her 20 minutes to stuff the tapestry into some plastic bags she brought because it was raining. I spent the evening cleaning my apartment."

What's the phone number of the woman from Freecycle?

Each of these puzzles did a good job of tying these people back to Noah's Market. Little clues like a "Noah's Market sweatshirt" were good.

This one seems extremely sparse on clues.

What we know:

1. This woman lives in Staten Island
2. She has a lot of older cats - 10-11

First, let's look for people from Staten Island.

    SELECT *
    FROM customers c
    WHERE city = 'Staten Island'

This comes back with over 300 rows.

To narrow this down, it's probably a fair assumption that she buys cat supplies from Noah's Market. But querying shows there are over 700 cat products. She also has older cats, and if it was mentioned in the text, it's probably a clue. Looking at the data, we need to look for items for "Senior Cats. That brings us down to 257 products. Let's build a query to see if we can identify somebody from "Staten Island" with a lot of orders for "senior cat" products.

    SELECT c.customerid, c.name, c.phone, count(o.orderid) AS cnt
    FROM customers c
    INNER JOIN orders o ON c.customerid = o.customerid
    INNER JOIN (
        SELECT  oi.orderid
        FROM orders_items oi
        INNER JOIN products p ON oi.sku = p.sku
            AND p.descr LIKE '%cat%'
            AND p.descr LIKE '%senior%'
    ) s1 ON o.orderid = s1.orderid
    WHERE c.city = 'Staten Island'
    GROUP BY c.customerid, c.name, c.phone
    ORDER BY cnt DESC

This gives us 76 rows, but there is an outlier. Entering her phone number shows she's the right answer. We found the Cat Lady.

GO TO DAY 6

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