Visitors Without Checkout Activity

Easy

A retail analytics team wants to measure how often shoppers enter the store but leave without completing checkout. Table: visit_log visit_id: unique identifier of a store visit customer_id: shopper identifier Table: purchase_log transaction_id: unique transaction identifier visit_id: visit where checkout happened amount: purchase value for that transaction Multiple transaction rows can belong to the same visit. 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: Return exactly these columns in this order: customer_id count_no_trans Sort by customer_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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    |

Output

[
  {"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.

Example 2

Input

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     |

Output

[]

Explanation

Every visit appears in purchase_log, so no customer has a transaction-free visit.

Example 3

Input

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     |

Output

[
  {"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

0 <= rows in visit_log <= 300000
0 <= rows in purchase_log <= 400000
1 <= visit_id <= 1000000000
1 <= customer_id <= 1000000000
1 <= transaction_id <= 1000000000
0 <= amount <= 1000000
visit_log.visit_id is unique
purchase_log.transaction_id is unique
purchase_log.visit_id always references an existing visit_log.visit_id
A visit may have zero, one, or multiple purchase_log rows
Return only customers with count_no_trans > 0
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: customer_id, count_no_trans

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 |

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 |

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 |