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. the cousin of the previous person, so maybe with the same family name (note: this was a red herring)

  2. using all coupons she can, so must be a regular customer

  3. someone who must often buy the product cheaper than it cost the shop

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
join products p on
  p.sku = oi.sku
where
  oi.unit_price < p.wholesale_cost
group by
  name,
  phone
order by
  orders desc;

3. Result

This finds all the people that have ever bought something on sale, so it’s not very specific.

However, the top person has ordered 3 times more product that anybody else under those conditions.

It’s the right result.

Time to solve: 13 minutes.