SQL Relational Division: Find Customers Who Bought Every Required Product

A customer buys three products. Your campaign requires three products. Does that customer qualify? Only if those are the right three products. SQL relational division is a set-coverage problem, and a query that compares raw purchase counts can pass an ordinary example while accepting the wrong customers.

The reliable starting point is: return each customer for whom no required product is missing. This guide develops that statement into two SQL solutions, then tests duplicates, unrelated purchases, empty requirements, and NULL identifiers. The examples are original interview exercises. They are not attributed to an employer or candidate report. Official PostgreSQL documentation supports the SQL semantics; the query designs and interview advice are editorial reasoning.

For a related scope check, use Reason About Composite Join Keys and Predicate Placement alongside the worked example below.

Define what “every required product” means

Assume three relations: customers(customer_id), required_products(product_id), and purchases(customer_id, product_id). The requested output is one row per qualifying customer, drawn from customers. Required products and customer identifiers are non-NULL. Purchases may contain repeated customer–product pairs because a customer can buy an item multiple times.

For this exercise, a qualifying customer must have bought at least every required product. Buying additional products is allowed. A purchase represents a qualifying completed purchase; cancelled orders and refunds have already been resolved upstream. That assumption matters: merely finding an order line would answer a different question if the business excludes returned purchases.

Write the contract before writing a query:

• The customer universe comes from customers, including customers with no purchases.
• Required products form a set, even if the input relation contains duplicate rows.
• A product is covered when at least one qualifying purchase exists.
• Additional products do not disqualify a customer.
• An empty required set qualifies every customer under the chosen mathematical rule.

The last rule is a decision, not a business inevitability. A campaign might instead require at least one configured product before anyone qualifies. State which interpretation you implement. Otherwise, two apparently correct solutions can disagree precisely when configuration is empty.

Use a fixture that catches count-based mistakes

Let the required input contain A, B, B: the distinct required set is {A, B}. Customers are numbered 1 through 6.

Customer	Qualifying purchase rows	Should qualify?	Reason
1	A, A, B	Yes	Repetition does not remove coverage
2	A, C	No	Same distinct count as the requirement, but missing B
3	A, B, C	Yes	Extra C is allowed
4	No purchases	No	Missing both required products
5	B, B	No	Buying B twice does not cover A
6	A, B, NULL	Yes	A malformed extra row does not establish any coverage

The expected result is customer IDs 1, 3, and 6. Customer 2 defeats a comparison of distinct purchases against distinct required products without first restricting purchases to the required set. Customer 5 defeats a comparison of raw row counts. Customer 4 distinguishes the customer universe from the purchase population.

For the NULL purchase row, this exercise treats an unknown product as providing no coverage. A production ingestion policy might reject or quarantine it. We do not silently apply that policy to NULL requirements: an invalid requirement can change who qualifies, so it requires explicit handling.

Solution one: no required product is missing

Translate the sentence from the outside inward. Start with a customer. Look for a required product. For that customer and product, check whether no purchase exists. Finally, reject customers for whom such a missing requirement can be found.

SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM required_products AS r
    WHERE NOT EXISTS (
        SELECT 1
        FROM purchases AS p
        WHERE p.customer_id = c.customer_id
          AND p.product_id = r.product_id
    )
)
ORDER BY c.customer_id;

Official semantics: PostgreSQL documents EXISTS as testing whether a subquery returns at least one row; the projected value usually does not matter. That is why SELECT 1 is useful notation here, not a claim of a special performance shortcut. See the subquery expressions reference.

For customer 2, requirement A finds a purchase, but B does not. The inner NOT EXISTS is true for B, the middle query returns a row, and the outer NOT EXISTS rejects customer 2. For customer 3, both required products find a match. There is no missing requirement, so customer 3 passes even though C was also purchased.

Repeated purchases do not change existence. Repeated requirement B also does not change the result: asking the same coverage question twice still produces the same Boolean answer. This robustness is one reason the formulation is easy to defend verbally.

Solution two: count covered requirements, not all purchases

A count solution can express the same contract if it preserves the customer universe and compares sets with the same meaning.

WITH required AS (
    SELECT DISTINCT product_id
    FROM required_products
), covered AS (
    SELECT DISTINCT p.customer_id, p.product_id
    FROM purchases AS p
    JOIN required AS r
      ON r.product_id = p.product_id
)
SELECT c.customer_id
FROM customers AS c
LEFT JOIN covered AS v
  ON v.customer_id = c.customer_id
GROUP BY c.customer_id
HAVING COUNT(v.product_id) = (
    SELECT COUNT(*) FROM required
)
ORDER BY c.customer_id;

The covered relation contains only required products and only one row per covered customer–product pair. Consequently, its count cannot increase when someone buys unrelated C or purchases A again. The left join keeps customer 4 in the calculation even when no covered row exists.

Official semantics: PostgreSQL distinguishes counting input rows with COUNT(*) from counting non-NULL expression values with COUNT(expression). That distinction makes COUNT(v.product_id) essential after this left join: the unmatched placeholder must contribute zero covered products. See aggregate functions.

Under the declared non-NULL requirement contract, the right-hand count is the number of unique requirements. The left-hand count is the number of those requirements covered by the customer. Because coverage is already restricted to the requirement set, equal cardinality establishes containment. Without that restriction, equal counts prove very little.

Do not claim this query is always faster than double NOT EXISTS. Both describe a logical result; the optimizer, engine, indexes, data distribution, and requirement-set size influence the physical work.

The empty-set case exposes hidden assumptions

Now delete all rows from required_products. Keep all six customers and their purchases unchanged. Both complete queries above return all six customers.

For the first query, there is no required product that can be missing. For the second, covered is empty; the left join retains each customer; the count is zero; and the requirement count is also zero. That is the intended empty-set behavior for this exercise.

A common shorter solution starts directly from purchases, joins requirements, groups by customer, and compares counts. With no requirements, that inner join produces no groups. It returns nobody, including customers who should qualify under our contract. Adding COALESCE to the aggregate does not recreate groups that never existed.

If the business rule says an empty configuration should qualify nobody, add an explicit EXISTS (SELECT 1 FROM required_products) guard to the customer filter. Keep that policy visible. Do not rely on an accidental side effect of an inner join, because later refactoring could change it unnoticed.

NULL requirements are a contract problem

Suppose a NULL product ID appears in the requirement relation. What does it mean to buy that product? Equality does not identify an ordinary matching product for an unknown identifier. Do not repair the query by reflexively dropping NULL requirements: that would silently weaken the campaign's requirements.

A defensible implementation rejects invalid requirement keys before evaluation, with a data-quality check or a non-NULL constraint. In an interview, state that choice and ask whether NULL has a special business meaning. If the requirement genuinely represents an “unknown” category that must match another unknown category, you need a deliberate comparison rule and corresponding tests.

Also resist replacing the inner missing-product test with NOT IN without examining NULLs. PostgreSQL's official subquery reference describes how a NULL in the compared subquery can make a nonmatching NOT IN result unknown rather than true. A customer with a NULL purchase row can therefore expose a materially different condition.

The existence solution is not magic protection against every NULL policy. It simply makes the relationship being tested explicit. Its correctness still depends on declared keys and the meaning assigned to invalid data.

“Every” and “exactly these” are different questions

Our current answer includes customer 3, who bought A, B, and C. If the interviewer changes the requirement to “bought exactly the required products and nothing else,” coverage is necessary but insufficient.

You now need two directions: every requirement is purchased, and every qualifying purchased product is required. Add a second anti-existence check for purchases outside the required set. Decide whether NULL purchase keys cause rejection, are quarantined, or are excluded before applying that exact-set rule. The earlier decision that NULL provides no coverage does not fully answer whether it counts as an unwanted extra.

Official semantics: PostgreSQL's EXCEPT returns rows from one query absent from another and removes duplicates unless ALL is specified. This provides another way to reason about missing requirements: required products minus purchased products should be empty. See combining queries.

Use the set-difference idea to explain correctness even if you choose double NOT EXISTS for the final SQL. It helps expose the distinction between containment and equality without relying on memorized syntax.

Extend the query without losing its grain

A campaign-specific version might use required_products(campaign_id, product_id). The output grain becomes customer–campaign, and both the requirement lookup and coverage comparison must use the campaign's scope. Comparing against the total number of requirements across every campaign is a different question.

A time-limited version needs a single definition of eligible purchase time, such as a half-open interval from campaign start through, but excluding, campaign end. Apply that predicate wherever qualifying purchases are defined. Do not count all-time purchases in one approach while checking campaign-window purchases in the other.

For quantities, set coverage no longer suffices. Requiring three units of A and one of B means comparing per-product eligible quantities against per-product thresholds. Duplicate purchase events, returns, and units of measure become part of the input contract. Summing arbitrary rows and then applying a distinct product count cannot prove quantity sufficiency.

For production performance, consider access paths on customer–product keys, restrict the population when the task allows it, and inspect the engine's execution plan. A small requirement list and a large purchase history can favor different plans from a broad product catalog. Measure representative cases rather than treating SQL nesting depth as runtime complexity.

Verify the logic before explaining performance

The two complete queries in this article were executed in DuckDB 1.5.2 against the six-customer fixture. Both returned 1, 3, and 6; both returned all customers for empty requirements. An independent Python set-containment oracle checked every combination of requirement and purchase subsets over a small three-product universe, including duplicated inputs. These are correctness checks, not PostgreSQL performance measurements.

Useful interview tests change one assumption at a time: repeat A, add unrelated C, remove B, add a customer without purchases, empty the requirement relation, and introduce an invalid requirement key. Explain which changes should preserve membership and which should alter it. That is more persuasive than saying the query “handles edge cases.”

Practice related SQL reasoning

These exercises reinforce join scope, output grain, and query explanation; they are not presented as five versions of relational division.

Question	What to practice
Reason About Composite Join Keys and Predicate Placement	Preserve campaign and customer scope
Debug row loss after SQL joins	Explain why inner joins lose empty groups
Write SQL revenue and anomaly queries	Define qualifying inputs before aggregation
Solve SQL and Python coding tasks	Compare SQL results with a small independent oracle
Write SQL window functions for D7 retention	Separate population definition from matching evidence

Start with Reason About Composite Join Keys and Predicate Placement, then return to this fixture and explain why customer 2 fails despite having two distinct purchases. If that explanation is precise, the SQL becomes much easier to defend.

Sources and Further Reading

• PostgreSQL: Subquery expressions, EXISTS, and NOT IN
• PostgreSQL: Aggregate functions and COUNT
• PostgreSQL: UNION, INTERSECT, and EXCEPT