Similar to the Advent of Code, the Hanukkah of Data (archive) was released in December 2022.

I don’t have time to do that during the holidays, so January 2023 it is for me.

1. Plan of attack

I’m looking for:

  1. someone who likes to collect stuff

  2. someone who owns an entire set of Noah’s collectibles

  3. collections usually require people to buy more than 1 time the full set

  4. someone with a male name

Looking at all products whose description starts with Noah’s, I can see that most of them start with COL and some start with TOY.

I assume COL stands for collection, and I’ll limit myself to those.

2. Implementation

This time, everything should be doable in SQL right away via DBeaver.

  count(oi.sku) as sku
  customers c
join orders o on
  o.customerid = c.customerid
join orders_items oi on
  oi.orderid = o.orderid
  and oi.sku = p.sku
join products p on
  p.sku = oi.sku
  p.sku like 'COL%'
  and p."desc" like 'Noah%'
group by
order by
  sku desc;

3. Result

This finds all the people who ordered sku as part of a collection, so it’s not very specific.

However, the person having bought the most of those items did so 9 times more than the rest of customers. He has a male name.

It’s the right result.

Time to solve: 9 minutes.