Data Analyst Interview
Menu
Browse in your language. All mock interviews, preparation sessions and feedback are in English only.

Find Customers Who Bought Every Required Product in SQL

To find customers who bought every required product, keep customers for whom no required product is missing. A double NOT EXISTS query expresses that rule directly and avoids counting duplicate purchases as additional coverage. Define what an empty requirements list should mean before using the result.

The customer coverage question

A fictional learning-product retailer serving US, UK, and German customers requires purchases of both SQL and Python products. US-A has SQL twice and Python once. UK-B has SQL and Excel. DE-C has no purchases. The correct qualifying customer is US-A. These are invented product labels and customer records for interview practice.

UK-B has two different products, but not the two required products. US-A’s repeated SQL purchase adds no new coverage. Counting transactions or comparing the number of all distinct purchased products with the number of requirements therefore answers the wrong question.

Run the complete SQLite query

WITH required(product) AS (VALUES ('SQL'), ('Python')),
customers(customer) AS (VALUES ('US-A'), ('UK-B'), ('DE-C')),
purchases(customer, product) AS (
 VALUES ('US-A','SQL'), ('US-A','SQL'),
        ('US-A','Python'), ('UK-B','SQL'),
        ('UK-B','Excel')
)
SELECT c.customer
FROM customers c
WHERE NOT EXISTS (
 SELECT 1 FROM required r
 WHERE NOT EXISTS (
  SELECT 1 FROM purchases p
  WHERE p.customer = c.customer
    AND p.product = r.product
 )
)
ORDER BY c.customer;

Read the two NOT EXISTS clauses

Start with a customer from the roster. The inner query looks for a matching purchase for one required product. If no purchase exists, that requirement is missing. The outer NOT EXISTS accepts the customer only when there are no missing requirements. The tested output is one row: US-A.

The SQLite EXISTS documentation explains that existence depends on whether a subquery returns rows. The selected constant 1 is only a conventional placeholder. A matching purchase is enough; extra matching rows do not make the condition more true.

Why include a customer roster?

The roster defines who is eligible for evaluation, including customers with no transactions. With the two requirements shown, DE-C fails because both are missing. If you instead start from purchases, customers with no purchases disappear before you can assess or explain their status.

Use a unique, nonmissing customer key in the roster. If customer IDs are reused across regional systems, correlate on both market and customer ID. The prefixed IDs in this exercise are globally unique. See the regional order-key example for why incomplete identity matching can mix records.

What if the requirements table is empty?

The query returns every customer in the roster when required contains no rows: there is no missing requirement to disqualify anyone. This behavior was tested by replacing the requirements VALUES clause with SELECT ‘SQL’ WHERE 0. All three customers qualified.

That may be correct for a mathematical all-items rule but wrong for a business workflow whose configuration is missing. If an empty list must produce no customers, add AND EXISTS (SELECT 1 FROM required) after the outer NOT EXISTS condition and before ORDER BY. If an empty list signals a configuration error, stop the report and surface that error instead.

Which source records count as purchases?

This fixture assumes every listed purchase qualifies. Real reports must define completed versus canceled orders, refunds, product substitutions, and the reporting period. Filter the purchase relation to that documented scope before testing coverage. Buying a product last year may not satisfy a requirement limited to this month’s campaign.

Validate required product identifiers as nonmissing. A NULL requirement will not match a normal equality predicate, potentially disqualifying every customer. Do not silently drop it and declare the configuration complete. Audit missing purchase keys separately using the missing customer-ID checks.

How can you explain failed customers?

For an audit view, pair each customer with each required product and retain combinations for which no purchase exists. In this fixture, UK-B is missing Python, while DE-C is missing SQL and Python. This is more actionable than a single failed flag and helps distinguish missing coverage from duplicate transactions.

Test a fully covered customer, a customer with only unrelated products, duplicate qualifying purchases, a customer with no purchases, and an empty requirements set. For the simpler one-condition absence problem, continue with customers with no orders using NOT EXISTS. Neither query measures daily active usage; it measures coverage of the specified purchase conditions.

Leave a Comment

✉ WhatsApp