Loading problem...
A retail analytics team wants to measure how often shoppers enter the store but leave without completing checkout.
Table: visit_log
Table: purchase_log
Business definition: A visit is considered transaction-free when its visit_id does not appear in purchase_log.
Task: For each customer, count how many of their visits were transaction-free.
Output requirements:
Supported submission environments:
visit_log:
| visit_id | customer_id |
|----------|-------------|
| 1 | 23 |
| 2 | 9 |
| 4 | 30 |
| 5 | 54 |
| 6 | 96 |
| 7 | 54 |
| 8 | 54 |
purchase_log:
| transaction_id | visit_id | amount |
|----------------|----------|--------|
| 2 | 5 | 310 |
| 3 | 5 | 300 |
| 9 | 5 | 200 |
| 12 | 1 | 910 |
| 13 | 2 | 970 |[
{"customer_id":30,"count_no_trans":1},
{"customer_id":54,"count_no_trans":2},
{"customer_id":96,"count_no_trans":1}
]Explanation
Visit 5 is transacted (multiple rows still count as one transacted visit). Visits 4, 6, 7, and 8 are transaction-free, which yields counts by customer.
visit_log:
| visit_id | customer_id |
|----------|-------------|
| 10 | 201 |
| 11 | 202 |
| 12 | 203 |
purchase_log:
| transaction_id | visit_id | amount |
|----------------|----------|--------|
| 1001 | 10 | 40 |
| 1002 | 11 | 60 |
| 1003 | 12 | 80 |[]Explanation
Every visit appears in purchase_log, so no customer has a transaction-free visit.
visit_log:
| visit_id | customer_id |
|----------|-------------|
| 21 | 701 |
| 22 | 701 |
| 23 | 702 |
| 24 | 703 |
| 25 | 703 |
| 26 | 703 |
purchase_log:
| transaction_id | visit_id | amount |
|----------------|----------|--------|
| 2001 | 23 | 120 |
| 2002 | 24 | 30 |[
{"customer_id":701,"count_no_trans":2},
{"customer_id":703,"count_no_trans":2}
]Explanation
Customer 701 has two transaction-free visits. Customer 703 has three visits, one transacted and two transaction-free.
Constraints