Visitors Without Checkout Activity

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

visit_log =

[{"visit_id":10,"customer_id":201},{"visit_id":11,"customer_id":202},{"visit_id":12,"customer_id":203}]

purchase_log =

[{"transaction_id":1001,"visit_id":10,"amount":40},{"transaction_id":1002,"visit_id":11,"amount":60},{"transaction_id":1003,"visit_id":12,"amount":80}]

Visitors Without Checkout Activity

Hints

Visitors Without Checkout Activity

Hints