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. a person living in NY

  2. ordering early in the morning (before 5am)

  3. made a habit out of it (so multiple times)

  4. buys pastry

But nothing is named "pastry" in the list of products, so I decide to look at all of them (there are only about 1000 of them), and see if can notice a pattern.

I find the following ones:

  • blintz

  • puff

  • knish

  • babka

  • hamentash

  • cookie

  • rugelach

  • bialy

  • twist

I could try to match on all of them, but their sku all start with BKY for bakery, so that must be it.

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
  strftime('%H', o.ordered) in ('01', '02', '03', '04', '05')
  and c.citystatezip like '%NY%'
  and oi.sku like 'BKY%'
group by
  name,
  phone
order by
  orders desc;

3. Result

194 results in 108 ms are returned, but the top one ordered 10 times, while the other ones only 3 or fewer times.

It’s the right person.

Time to solve: 29 minutes.