Interview Question Bank · Relational division · HAVING COUNT(DISTINCT)
2. Customers who own the whole Security suite
MediumCloud softwareQuestion #259
Concept: Relational division · HAVING COUNT(DISTINCT)show
'Customers who bought every X' is relational division. Filter to the set you care about, group by customer, and keep the groups whose number of distinct items equals the size of the set: HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM catalog WHERE ...). Take the set size from the table that defines it, so the query keeps working when a product is added.
Example (different data):
SELECT s.name FROM students s JOIN completions c ON c.student_id = s.id JOIN modules m ON m.id = c.module_id WHERE m.track = 'Data' GROUP BY s.id, s.name HAVING COUNT(DISTINCT c.module_id) = (SELECT COUNT(*) FROM modules WHERE track = 'Data');
Your task
Nimbusworks sells a Security suite of several products. Sales wants a reference list of customers who own all of it. A refunded order doesn't count as owning the product.
For every customer with a completed order for every product in the 'Security' category of bt_catalog, return customer (name) and security_orders (their completed order lines for Security products, repeat purchases included). Sort by customer.
Starting Postgres in your browser…
Checking your access…