Another Holiday Puzzle - Hanukkah of Data - Part 2

Hanakkuh of Data

A database puzzle

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

** WARNING: SPOILERS AHEAD **

** DAY 2 - THE CONTRACTOR **

Thanks to your help, Sarah called the investigator that afternoon. The investigator went directly to the cleaners to see if they could get any more information about the unclaimed rug.

While they were out, Sarah said, "I tried cleaning the rug myself, but there was this snail on it that always seemed to leave a trail of slime behind it. I spent a few hours cleaning it, and the next day the slime trail was back."

When the investigator returned, they said, "Apparently, this cleaner had a special projects program, where they outsourced challenging cleaning projects to industrious contractors. As they're right across the street from Noah's, they usually talked about the project over coffee and bagels at Noah's before handing off the item to be cleaned. The contractors would pick up the tab and expense it, along with their cleaning supplies.

"So this rug was apparently one of those special projects. The claim ticket said ‘2017 JP'. ‘2017' is the year the item was brought in, and ‘JP' is the initials of the contractor.

"But they stopped outsourcing a few years ago, and don't have contact information for any of these workers anymore."

Sarah first seemed hopeless, and then glanced at the USB drive you had just put back in her hand. She said, "I know it's a long shot, but is there any chance you could find their phone number?"

These puzzles are getting a little more challenging. You need to read the comments very carefully to figure out what exactly is being asked. The Investigator took the rug to a Cleaner, but that Cleaner had to hire a Contractor to work on the rug. We need to find the Contractor next.

Here, we learn that:

1. The claim ticket was in 2017
2. The claim ticked shows the Contractor for the special project to have the initials "JP"
3. That the Cleaner met with the Contractor at Noah's Bakery and bought "coffee and bagels" plus "cleaning supplies". The Contractor would be on Noah's order list.

It was a good thing we split out the last name. That will make it easier to get the initials. We'll also bring in Noah's order tables to check who bought the bakery items.

After some data investigation, we end up with:

    /* Find the contractor. */
    SELECT DISTINCT c.customerid, c.name, c.address, c.citystatezip, c.phone
    FROM customers c
    INNER JOIN orders o ON c.customerid = o.customerid
        AND YEAR(ordered) = 2017
    INNER JOIN orders_items oi ON o.orderid = oi.orderid
    INNER JOIN products p ON oi.sku = p.sku
        AND ( p.descr LIKE '%coffee%' OR descr LIKE '%bagel%'  OR descr = 'Rug Cleaner')
    WHERE LEFT(c.name,1) = 'J'
        AND LEFT(c.lastName,1) = 'P'

    /*
    customerid  name            address         citystatezip       phone
    1475        Joshua Peterson 100-75 148th St Jamaica, NY 11435  332-274-4185
    */

ON TO DAY 3

BACK TO PART 1