Hanukkah of Data 2022: Puzzle 8
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:
someone who likes to collect stuff
someone who owns an entire set of Noah’s collectibles
collections usually require people to buy more than 1 time the full set
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
COL stands for
collection, and I’ll limit myself to those.
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;
This finds all the people who ordered
sku as part of a collection, so it’s not
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.