Find customers who visited a store but never made a transaction, and count how many such visits each customer had.
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.
visit_id.Count visits for each customer where there is no transaction linked to that visit.
Two relational tables are involved:
Visitsvisit_id — unique identifier of the visitcustomer_id — identifier of the customerTransactionstransaction_id — unique identifier of the transactionvisit_id — identifier of the visit that generated the transactionAssume the tables are already populated.
Return a result table with:
customer_idcount_no_trans — number of visits for that customer with no transactionThe rows should be ordered by customer_id.
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
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.