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

Another Holiday Puzzle - Hanukkah of Data - Part 7

Hanakkuh of Data

A database puzzle

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

** WARNING: SPOILERS AHEAD **

** DAY 7 - THE MEET CUTE **

"Oh that tapestry, with the colorful toucan on it! I'll tell you what happened to it.

"One day, I was at Noah's Market, and I was just about to leave when someone behind me said 'Miss! You dropped something!'

"Well I turned around to see this cute guy holding an item I had bought. He said, 'I got the same thing!' We laughed about it and wound up swapping items because I wanted the color he got. We had a moment when our eyes met and my heart stopped for a second. I asked him to get some food with me and we spent the rest of the day together.

"Before long I moved into his place, but the romance faded quickly, as he wasn't the prince I imagined. I left abruptly one night, forgetting the tapestry on his wall. But by then, it symbolized our love, and I wanted nothing more to do with it. For all I know, he still has it."

Can you figure out her ex-boyfriend's phone number?

This is another one where we'll need info from the previous query. We need to know the customerid of The Bargain Hunter, since the Ex-boyfriend bought the same item at the same time. But the item was a different color, so we'll also need to look at Products with a color description. I think that's all the actual clues in the text.

What we know:

1. The Ex-boyfriend bought an item at Noah's Market the same day as The Bargain Hunter.
2. The Bargain Hunter's customerid is 4167
3. Colored items are designated in product.sku as 'COL%'

So first, let's find the orders that were placed on the same day as The Bargain Hunter.

    SELECT o1.customerid, o1.orderid, o2.customerid, o2.orderid
    FROM orders o1
    INNER JOIN orders o2 ON o1.customerid <> o2.customerid
        AND YEAR(o1.ordered) = YEAR(o2.ordered)
        AND MONTH(o1.ordered) = MONTH(o2.ordered)
        AND DAY(o1.ordered) = DAY(o2.ordered)
    WHERE o1.customerid = 4167

This gives us over 3000 orders. This also shows that we probably need to simplify date searching a little bit. orders.ordered is a datetime datatype. We want to truncate that date to just `YYYY-MM-DD`, and we can do this by simply using CAST(orders.ordered AS date). That will trim off the time part. That way we aren't pulling out each part. We can go back to that if we need to later.

We also need to narrow these orders down to just those items with a color listed. Looking at the data shows us that it's part of products.descr.

    /* Get Sherri's orders */
    SELECT o.ordered, oi.sku, p.descr, CAST(o.ordered AS date) AS truncDate
    FROM orders o
    INNER JOIN orders_items oi ON o.orderid = oi.orderid
    INNER JOIN products p ON oi.sku = p.sku
        AND p.sku LIKE 'COL%'
    WHERE o.customerid = 4167

This drops it down to just 4 orders. That should be easy to work with. Now we just need to find other orders placed on that date.

    /* Find Orders from same time as Sherri's */
    ; WITH sherriOrders AS (
        SELECT o.ordered, oi.sku, p.descr, CAST(o.ordered AS date) AS truncDate
        FROM orders o
        INNER JOIN orders_items oi ON o.orderid = oi.orderid
        INNER JOIN products p ON oi.sku = p.sku
            AND p.sku LIKE 'COL%'
        WHERE o.customerid = 4167
    )
    SELECT c.customerid, c.name, c.phone, o.*, so.*,  ABS(DATEDIFF(hour,o.ordered,so.ordered)) AS diffHrs
    FROM orders o
    INNER JOIN orders_items oi ON o.orderid = oi.orderid
    INNER JOIN products p ON oi.sku = p.sku
        --AND p.sku LIKE 'COL%'
    INNER JOIN sherriOrders so ON oi.sku = so.sku
        AND CAST(o.ordered AS date) = so.truncDate
        AND o.ordered <= so.ordered
        --AND DATEDIFF(hour,o.ordered,so.ordered) < 1 /* Orders less than 1 hour apart. */
    INNER JOIN customers c ON o.customerid = c.customerid
    WHERE o.customerid <> 4167 /* Exclude Sherri */

    /* NO RESULTS */
    

That's not good. That shouldn't have eliminated everything. There should be other orders that happened at the same time.

After digging at the data, I realized that I was eliminating the data that I actually needed. I was comparing Product SKUs from both orders, but the text says that these items were different colors. They'll have different SKUs, so I need to compare the item name without the colors.

    /* Find Orders from same time as Sherri's */
    ; WITH sherriOrders AS (
        SELECT o.ordered, oi.sku, p.descr
            , SUBSTRING(p.descr,1,CHARINDEX('(',p.descr)-2) AS prodName
            , CAST(o.ordered AS date) AS truncDate
        FROM orders o
        INNER JOIN orders_items oi ON o.orderid = oi.orderid
        INNER JOIN products p ON oi.sku = p.sku
            AND p.sku LIKE 'COL%'
        WHERE o.customerid = 4167 /* Sherri */
    )
    SELECT c.customerid, c.name, c.phone --,  o.*, oi.*,p.*, so.*
    FROM orders o
    INNER JOIN customers c ON o.customerid = c.customerid
    INNER JOIN orders_items oi ON o.orderid = oi.orderid
    INNER JOIN products p ON oi.sku = p.sku
        AND p.sku LIKE 'COL%'
    INNER JOIN sherriOrders so ON
        SUBSTRING(p.descr,1, CASE WHEN CHARINDEX('(',p.descr) IS NULL THEN LEN(p.descr) ELSE CHARINDEX('(',p.descr)-2 END ) = so.prodName /* THIS IS AN INSANE SPLITTING */
        AND CAST(o.ordered AS date) = so.truncDate
        AND ABS(DATEDIFF( minute, o.ordered,so.ordered )) < 1 /* USE ABS TO ELIMINATE NEGATIVES */
    WHERE o.customerid <> 4167

    /*
    customerid  name          phone
    5783        Carlos Myers  838-335-7157
    */
    

That one gets us down to just one result. Entering that phone number gives us another right answer.

GO TO DAY 8

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

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

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

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

Another Holiday Puzzle - Hanukkah of Data - Part 3

Hanakkuh of Data

A database puzzle

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

** WARNING: SPOILERS AHEAD **

** DAY 3 - THE NEIGHBOR **

Sarah and the investigator were very impressed with your data skills, as you were able to figure out the phone number of the contractor. They called up the cleaning contractor straight away and asked about the rug.

"Oh, yeah, I did some special projects for them a few years ago. I remember that rug unfortunately. I managed to clean one section, which revealed a giant spider that startled me whenever I tried to work on it.

"I already had a fear of spiders before this, but this spider was so realistic that I had a hard time making any more progress. I kept expecting the cleaners would call for the rug, but they never did. I felt so bad about it, I couldn't face them, and of course they never gave me another project.

"At last I couldn't deal with the rug taking up my whole bathtub, so I gave it to this guy who lived in my neighborhood. He said that he was naturally intuitive because he was a Cancer born in the year of the Rabbit, so maybe he was able to clean it.

"I don't remember his name. Last time I saw him, he was leaving the subway and carrying a bag from Noah's. I swore I saw a spider on his hat."

Can you find the phone number of the person that the contractor gave the rug to?

For this one, we'll need to use a piece of data from our previous query. We need to know what neighborhood the Contractor lived in. We also need to figure out this person's astrological birthdate. Time for more Google-fu.

What we know:

1. Lived in the Neighborhood
2. Is a Cancer born in the Year of the Rabbit
3. The Contractor's address is:"100-75 148th St, Jamaica, NY 11435"

Cancer born in Year of the Rabbit.

• June 21 - July 22 (https://www.google.com/search?q=cancer+dates+zodiac)
• The Year of the Rabbit occurs every 12 years in the Chinese zodiac, and the next one will be in 2023.
  • Other recent Years of the Rabbit include 1915, 1927, 1939, 1951, 1963, 1975, 1987, and 1999.
  • ( https://en.wikipedia.org/wiki/Rabbit_(zodiac) )

So then we just combine these filters:

    SELECT customerid, name, address, citystatezip, birthdate, phone
    FROM customers c
    WHERE YEAR(birthdate) IN (1915,1927,1939,1951,1963,1975,1987,1999,2011)
        AND (
            ( MONTH(birthdate) = 6 AND DAY(birthdate) >= 21 )
            OR
            ( MONTH(birthdate) = 7 AND DAY(birthdate) <= 22 )
        )
        AND zip = 11435

    /*
    customerid  name           address          citystatezip       birthdate   phone
    2550        Robert Morton  145-51 107th Ave Jamaica, NY 11435  1999-07-08  917-288-9635
    */

This one wasn't that difficult, but there are probably prettier ways to get at this birthdate.

GO TO DAY 4

BACK TO PART 1
BACK TO PART 2

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