1. Plan of attack
I’m looking for:
the cousin of the previous person, so maybe with the same family name (note: this was a red herring)
using all coupons she can, so must be a regular customer
someone who must often buy the product cheaper than it cost the shop
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;
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.