Find customers who visited a store but never made a transaction, and count how many such visits each customer had.
Problem
You are given two tables: one records customer visits to a store, and the other records transactions made during those visits. Some visits may have no matching transaction.
For each customer, determine how many of their visits did not result in any transaction.
Return the customer IDs and the number of non-transaction visits, ordered by customer ID.
Notes
- A visit is identified by
visit_id. - A transaction, if present, belongs to exactly one visit.
- A visit can appear in the visits table even when no transaction exists for it.
- A customer may have multiple visits.
Goal
Count visits for each customer where there is no transaction linked to that visit.
Input Format
Two relational tables are involved:
Visits
visit_id— unique identifier of the visitcustomer_id— identifier of the customer
Transactions
transaction_id— unique identifier of the transactionvisit_id— identifier of the visit that generated the transaction
Assume the tables are already populated.
Output Format
Return a result table with:
customer_idcount_no_trans— number of visits for that customer with no transaction
The rows should be ordered by customer_id.
Constraints
- A visit may have zero or one matching transaction.
- A customer can have zero, one, or many visits.
- Output only customers with at least one visit that has no transaction.
Example 1
Input
Visits +---------+------------+ | visit_id| customer_id| +---------+------------+ | 1 | 23 | | 2 | 9 | | 4 | 30 | | 5 | 54 | | 6 | 54 | | 7 | 54 | +---------+------------+ Transactions +----------------+---------+ | transaction_id | visit_id| +----------------+---------+ | 2 | 5 | | 3 | 5 | | 4 | 6 | +----------------+---------+
Output
+-------------+--------------+ | customer_id | count_no_trans| +-------------+--------------+ | 23 | 1 | | 9 | 1 | | 30 | 1 | | 54 | 1 | +-------------+--------------+
Explanation
Visits 1, 2, 4, and 7 have no matching transaction. Counting those by customer gives one no-transaction visit for each listed customer.
Premium problem context
Unlock deeper context for this problem
Premium adds guided hints, editorial links, similar variants, discussion resources, and concept maps so you can understand why a problem matters, not just solve it once.