Hanukkah of Data 2022 - Puzzle 8 and Wrap-Up
Tags: hanukkahofdata, datasette, python, pandas
Published: 2022-12-30 21:58:21

---
title: Hanukkah of Data 2022 - Puzzle 8 and Wrap-Up
published: true
description: Talk through (and spoil!) the eighth and final puzzle from Hanukkah of Data 2022.
tags: hanukkahofdata, datasette, python, pandas
series: Hanukkah of Data 2022
---

_[Hanukkah of Data](https://hanukkah.bluebird.sh/) is a series of data-themed puzzles, where you solve puzzles to move your way through a holiday-themed story using a fictional dataset. See the [introductory post](https://www.visidata.org/blog/2022/hanukkah-of-data/) for a bit more detail, but the pitch in my head is "[Advent of Code](https://adventofcode.com/) meets [SQL Murder Mystery](https://mystery.knightlab.com/)". This post walks through my approach to the eighth puzzle._

- [Thinking](#thinking)
- [Doing (Datasette)](#doing-datasette)
- [Doing (Pandas)](#doing-pandas)
- [Wrap-Up](#wrap-up)

**Warning:** This post unavoidably contains spoilers. If you'd rather do the puzzles on your own first, please close this browser tab and run away :).

## Thinking

For the last puzzle, we're looking for the person who still probably has the rug! Turns out he's a bit of a pack rat:

> She wound up getting a newer and more expensive rug, 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 rug, even.

The plan here will be find what counts as a Noah's collectible and see who has the most of them.

## Doing (Datasette)

Based on a peek at the products table, it looks like collectibles all have a 'COL' sku prefix. So we should be able to find customers who have ordered the most distinct items with that prefix:

```sql
select
  name,
  phone,
  count(distinct sku) as colcount
from
  noahs_order_detail
where
  sku like 'COL%'
group by
  name,
  phone
order by
  colcount desc
```

And yes, Travis has more than 2x the collectible count of any other customer!

## Doing (Pandas)

A pretty straight SQL-->pandas translation seems to work fine here:

```python
df[
  df.sku.str.contains('^COL')
].groupby(
  ['name','phone']
).agg(
  {'sku':'nunique'}
).sort_values(by='sku').tail()
```

## Wrap-Up

This was a lot of fun! Some specific takeaways:

- Huge thanks to [The Devottys](https://bluebird.sh/about/#) for putting this challenge together
  - Saul & Anja pour a lot of heart and smarts into VisiData and the data/terminal-loving community
  - I'm consistently impressed by Dwimmer's artwork, he's magic at the terminal
- There's something dangerous or funky about using `category` dtypes in pandas with `groupby()`
  - I got various errors or saw operations take 40x longer, though this is at least partly my inexperience / ignorance
  - Issues like https://github.com/pandas-dev/pandas/issues/36698 suggest I'm not alone!
- Working through problems with multiple tools can sometimes help you rethink your approach in all of them
- You don't have to [be Simon Willison](https://til.simonwillison.net/sqlite) to get a lot out of SQLite

I should also point out the official wrap-up post [here](https://www.visidata.org/blog/2022/hanukkah-of-data-wrap-up/), which links to other community posts.