Thursday, January 4, 2024

Another Holiday Puzzle - Hanukkah of Data - Part 2

Hanakkuh of Data

A database puzzle


** 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

Another Holiday Puzzle - Hanukkah of Data - Part 1

Hanakkuh of Data

A database puzzle


Over the last several years, I've played along with Advent of Code - a series of coding puzzles doled out over the 25 days of December, leading up to Christmas. And while Advent of Code is more programming-based, there is also a Hanukkah-themed set of puzzles that are database-related, Hanukkah of Data.

I don't remember how I came across this one, but it's a fun companion to AoC, and it's much more conducive to the way I've been working to solve these puzzles - with SQL.

Hanukkah of Data was created by Saul Pwanson and the folks with devottys. It's a series of puzzles over the 8 days of Hanukkah, that cover a story that requires you to search through a database to solve a series of problems.


** GETTING STARTED **

The original database is in SQLite, but there were a couple of other options, like .csv and .json. I intended to do this in SQLite, so that's the db I grabbed. The .zip files are password-protected, and the password is the very first challenge. See Day 0 for details.

I downloaded SQLite Browser to play with the db, but after getting it up and running, I realized that I don't really know much about querying with SQLite. I decided to export to a SQL script so I can use this in SQL Server. I'll likely play with some SQLite over the coming year.

*** MOVING TO T-SQL ***

After I exported the SQL file, I needed to modify it a bit to work with SQL Server. First thing was replacing all instances of double quotes ("), then I had to replace the things in SQLite that didn't cross over to SQL Server, like array datatypes and changing text to varchar and integer to int.

Then I created a database for this project in SQL Server and ran the sql script to create and populate the tables. It took a bit of tweaking to the queries to make them T-SQL compatible, but I got it.

After realizing what a mess this data was, I decided that a few of the columns needed to be fixed so they were easier to work with. The most obvious one was the citystatezip column in the customers table. I split that one into 3 separate columns. I tested the database to make sure I can query it. Now it's time to get to work.

Just a quick note: These puzzles require more external sleuthing than the Advent of Code puzzles. Some of these clues require you to do a little bit of Googling.

** GETTING TO WORK **

According to the website's About page, the story for this year is Noah's Rug.

About

Noah's Rug is a set of short data challenges, first released in 2022 as Hanukkah of Data. The same fictional dataset is used for all puzzles. Explore this dataset with the tools of your choice to figure out the answers, light the Hanukkah candles, and find Noah's missing rug.

How to Play

When you first come to the puzzle page, there is only one candle: the shamash. Click on its wick to read the introduction and read the question. When you submit the correct answer, the shamash will be lit, and the first candle will appear. Solve this puzzle to light its candle too.

Each day, there will be another candle and therefore another puzzle, for 8 days. Each candle will illuminate a part of the tapestry, until the whole tapestry is lit.

** WARNING: SPOILERS AHEAD **

** DAY 0 - NOAH'S MARKET **

The first challenge is to “Light the shamash”. This puzzle is fairly straightforward. Unless you are familiar with the Hebrew Calendar, this is just a simple Google search to find the year in the Hebrew calendar that corresponds to 2017. Google tells me this is 5777. I use this number to open the .zip files, but I also enter it into the Check Answer field, and with the right answer, I go back to the candle for my first example of the ASCII art.

** DAY 1 - THE INVESTIGATOR **

Sarah brought a cashier over. She said, "Joe here says that one of our customers is a skilled private investigator."

Joe nodded, "They showed me their business card, and that's what it said. Skilled Private Investigator. And their phone number was their last name spelled out. I didn't know what that meant, but apparently before there were smartphones, people had to remember phone numbers or write them down. If you wanted a phone number that was easy-to-remember, you could get a number that spelled something using the letters printed on the phone buttons: like 2 has "ABC", and 3 "DEF", etc. And I guess this person had done that, so if you dialed the numbers corresponding to the letters in their name, it would call their phone number!

"I thought that was pretty cool. But I don't remember their name, or anything else about them for that matter. I couldn't even tell you if they were male or female."

Sarah said, "This person seems like they are skilled at investigation. I need them to find Noah's rug before the Hanukkah dinner. I don't know how to contact them, but apparently they shop here at Noah's Market."

She nodded at the USB drive in your hand.

"Can you find this investigator's phone number?"

So my first task is to find a phone number that is the same digits as the person's last name on a phone pad. Time for the first bit of Googling: find the digits that relate to a phone pad.

Now that we've got the mappings for numbers to letters, let's look at the last names.

    /* Find the last name */
    SELECT *
    FROM customers

This shows me that the name field isn't just simple first/last name content. I won't be able to split them by just finding the first space character. It'll take a little more digging than that.

There are some entries that have simple name suffixes, like "Jr." or "III", but then there are other names that are three part names, like "Hannah Kari Hartman".

Let's normalize this data a bit. To do this, I'll need two more columns in the customers table: lastName and strippedPhone.

    /* Strip suffix from lastName and digitize phone number and convert city/state/zip */
    UPDATE customers
    SET strippedPhone =
        REPLACE(phone,'-','')
            , lastName =
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                SUBSTRING(name
                                    , CHARINDEX(' ',name)+1
                                    , LEN(name))
                                , ' Jr.', '')
                            , ' II','')
                        , ' III','')
                , ' IV','')
        , ' V','')

    /* Strip middle name from LastName */
    UPDATE customers
    SET lastName = SUBSTRING(lastName, CHARINDEX(' ',lastName)+1,LEN(lastName))

Those queries split out the name to isolate everything after the first name part. After that, it gets rid of any suffix name that's after the last name. The second update gets rid of any middle name that may have been in the data, so that we are only left with a true last name.

I am making some assumptions here. Primarily that a space character doesn't make up any part of the last name. It did take a little bit of data investigation to check which suffixes were used in the data. There are other edge cases that could have been in this data, but don't seem to be.

Anyway, now that's done, it should be a lot easier to check out the last name.

I do know that the last name must be 10 digits, since it has to match a phone number, but I need to figure out how to get to those 10 characters. I also need to translate those characters into their number representations. The easiest way will be to just create two helper functions.

    /* Digitize last name with FUNCTION */
    CREATE OR ALTER FUNCTION digitizeName(@inp varchar(100))
    RETURNS varchar(10)
    AS
    BEGIN
        IF LEN(@inp) <> 10
        BEGIN
            RETURN NULL
        END
        DECLARE @c1 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,1,1))
        DECLARE @c2 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,2,1))
        DECLARE @c3 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,3,1))
        DECLARE @c4 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,4,1))
        DECLARE @c5 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,5,1))
        DECLARE @c6 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,6,1))
        DECLARE @c7 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,7,1))
        DECLARE @c8 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,8,1))
        DECLARE @c9 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,9,1))
        DECLARE @c10 varchar(1) = dbo.checkKeypad(SUBSTRING(@inp,10,1))

        RETURN CONCAT(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10)
    END
    /* Function to check against keypad values. */
    CREATE OR ALTER FUNCTION checkKeypad(@inp varchar(1))
    RETURNS int
    AS
    BEGIN
        RETURN
            CASE
                WHEN @inp IN ('A','B','C') THEN 2
                WHEN @inp IN ('D','E','F') THEN 3
                WHEN @inp IN ('G','H','I') THEN 4
                WHEN @inp IN ('J','K','L') THEN 5
                WHEN @inp IN ('M','N','O') THEN 6
                WHEN @inp IN ('P','Q','R','S') THEN 7
                WHEN @inp IN ('T','U','V') THEN 8
                WHEN @inp IN ('W','X','Y','Z') THEN 9
                ELSE NULL
            END
    END

The checkKeypad function maps a character to its keypad number, and the digitizeName function uses the checkKeypad function to convert the letters in the last name. Since I know that a phone number is 10 digits, I only have to worry about rows with a 10-character lastName.

    /* Find the investigator's phone number? */
    SELECT customerid, name, strippedPhone, phone
    FROM customers
    WHERE dbo.digitizeName(lastName) = strippedPhone

    /*
    customerid  name            strippedPhone  phone
    1208        Sam Tannenbaum	8266362286     826-636-2286
    */




GO TO DAY 2

Monday, January 1, 2024

I'll Be Back Again! Into The Box 2024


I have once again been invited to speak at Into The Box. This time the conference will be in Washington, D.C. instead of down in Houston, so it'll be a little bit different. 

My topic for this year is Schrödinger’s Backup: Is Your Backup Really a Backup?, where I'll talk about the importance of how a good backup strategy doesn't only include taking the backups, but also making sure those backups can be restored. 

If you'd like to attend, you can register HERE.

See you all in D.C.!

Happy New Years 2024!


2024-01-01 00:00:00.00



Tuesday, December 26, 2023

Advent of Code 2023 Has Come To A Close

Another year of Advent of Code is in the books. The puzzles seemed much harder this year than they have in past years, but it was still a load of fun. 

Once again, I didn't make it as far as I wanted to. I think I got somewhere around 7 1/2 Days completed, with some work done on the first part of a couple of more days. And once again, I'm reminded that some things probably shouldn't be done. SQL is a really bad language for most of these puzzles. But the ASCII art is fun to reveal. :-)

This year started with over 227K participants, which is a pretty good jump over their first year (2015) with around 87K people. The stories are always entertaining and the puzzles are always fun and challenging. It's been an amusing exercise, although the holiday time begins to get in the way towards the end. 

Around the middle of the season, I was also made aware of another set of puzzles; this one more amenable to SQLish solutions: Hanukkah of Data. This one was a bit different, and a lot shorter, than Advent of Code, and I only completed a few days of the puzzles, but it's worth taking a look at. I'll try to complete it over the year, and probably play along again next year. 

As far as Advent of Code, I'm not sure what language I'll try it again next year. I'd like to keep solving these with SQL, but they aren't really teaching me anything except how to not use SQL to solve procedural problems. It's been pretty amazing that I have been able to actually come to some of these answers, but my code is a whole different level of ugly. We'll see what next year brings. 

To wrap this up for another year, I'd like to thank my fellow participants from the Working Code Podcast.  Give them a listen and join the Discord. And next year, join with us for Advent of Code 2024. 




Thursday, November 30, 2023

Advent of Code 2023 Has Begun!

 How did December get here so fast??? 

It's time for another iteration of Advent of Code.


I'll likely try these in SQL again. I may do multiple languages. We'll see

Tuesday, July 12, 2022

I Am Speaking At PASS Data Community Summit 2022!

 I have been accepted to speak at PASS Data Community Summit on November 15-18, 2022. They are going for a hybrid conference this year, with the main conference being held live in Seattle with an online component! 



I'll be presenting You've Been Hit By A Bus about preparing for that horrible, but inevitable,  moment when you won't be able to handle your geek belongings. 

I'll be one of the On-Demand recordings, but I'm going to try to make it to the live conference, too. I've never been, and I've been looking forward to going.

You should come out, too. There are heaps of great sessions going on. Mostly on much less depressing  topics than mine. But come join the #SQLFamily!


From my abstract:

If you’re like me, you’re probably IT Support for your home. Your business probably has a Continuity Plan. Shouldn’t your home have one, too? 

What happens to our digital selves when our physical selves are gone? Most of us probably haven’t given much thought about our digital footprints and how they will far outlive us. Or, more importantly, about how our passing can leave our loved ones in a tight spot. Our modern lives have changed to include a significant amount of digital stuff that could easily disappear if that bus comes around. Will the people you leave behind be able to continue to function without you? We need to have a plan.

Come out to The Summit!