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.

select
  name,
  phone,
  count(oi.sku) as sku
from
  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
where
  p.sku like 'COL%'
  and p."desc" like 'Noah%'
group by
  name,
  phone
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.