1. Plan of attack
I’m looking for:
someone living in Queens Village (looking up on Wikipedia the zip code associated)
has a sweatshirt from Noah’s market, so she is a customer
has cat’s hair, so buys cat food
must be a habit
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
PET seems to be a good first try.
I can try to refine the search parameter to
sku descriptions containing
if that’s not enough.
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;
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.