1. Plan of attack
I’m looking for:
a person living in NY
ordering early in the morning (before 5am)
made a habit out of it (so multiple times)
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:
I could try to match on all of them, but their
sku all start with
BKY for bakery,
so that must be it.
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;
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.