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 living in Queens Village (looking up on Wikipedia the zip code associated)

  2. has a sweatshirt from Noah’s market, so she is a customer

  3. has cat’s hair, so buys cat food

  4. must be a habit

  5. owns 10 or 11 cats, so she must buy a lot of cat food

Looking at all the products, cat food do not seem to have a unique sku prefix, but PET seems to be a good first try.

I can try to refine the search parameter to sku descriptions containing cat if that’s not enough.

2. Implementation

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

select
  name,
  phone,
  count(oi.orderid) as orders
from
  customers c
join orders o on
  o.customerid = c.customerid
join orders_items oi on
  oi.orderid = o.orderid
where
  (c.citystatezip like '%11427%'
  or c.citystatezip like '%11428%'
  or c.citystatezip like '%11429%')
  and oi.sku like 'PET%'
group by
  name,
  phone
order by
  orders desc;

3. Result

7 results in 94 ms are returned, but the top one ordered 6 times more than anybody else. It also is a feminine name.

It’s the right result.

Time to solve: 4 minutes.