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
No comments:
Post a Comment