Visit Transaction Frequency Distribution

Hard

A retail-banking analytics team tracks branch attendance and in-branch transactions. Table: visits user_id: customer identifier visit_date: date of a branch visit (user_id, visit_date) is unique Table: transactions user_id: customer identifier transaction_date: date of a recorded transaction amount: transaction amount duplicate rows are allowed and each row counts as a separate transaction event Guarantee: Every row in transactions belongs to a valid visit with the same (user_id, date). Task: For each visit in visits, count how many transactions happened on that exact visit. Then produce a histogram with: transactions_count: number of transactions in one visit visits_count: how many visits had exactly that transactions_count Critical output rule: Include every integer bucket from 0 to max(transactions_count) observed in any visit. If a bucket has no visits, include it with visits_count = 0. Output requirements: Return exactly two columns: transactions_count, visits_count. Order by transactions_count ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

visits:
| user_id | visit_date  |
|---------|-------------|
| 1       | 2024-01-01  |
| 2       | 2024-01-02  |
| 12      | 2024-01-01  |
| 19      | 2024-01-03  |
| 1       | 2024-01-02  |
| 2       | 2024-01-03  |
| 1       | 2024-01-04  |
| 7       | 2024-01-11  |
| 9       | 2024-01-25  |
| 8       | 2024-01-28  |

transactions:
| user_id | transaction_date | amount |
|---------|------------------|--------|
| 1       | 2024-01-02       | 120    |
| 2       | 2024-01-03       | 22     |
| 7       | 2024-01-11       | 232    |
| 1       | 2024-01-04       | 7      |
| 9       | 2024-01-25       | 33     |
| 9       | 2024-01-25       | 66     |
| 8       | 2024-01-28       | 1      |
| 9       | 2024-01-25       | 99     |

Output

[
  {"transactions_count":0,"visits_count":4},
  {"transactions_count":1,"visits_count":5},
  {"transactions_count":2,"visits_count":0},
  {"transactions_count":3,"visits_count":1}
]

Explanation

Visit-level transaction counts are [0,0,0,0,1,1,1,1,1,3]. The histogram must include bucket 2 even though no visit produced exactly two transactions.

Example 2

Input

visits:
| user_id | visit_date  |
|---------|-------------|
| 31      | 2024-03-01  |
| 31      | 2024-03-02  |
| 45      | 2024-03-01  |
| 60      | 2024-03-04  |

transactions:
| user_id | transaction_date | amount |
|---------|------------------|--------|
| (no rows) |

Output

[
  {"transactions_count":0,"visits_count":4}
]

Explanation

No visit has any transaction, so max bucket is 0 and the output contains only bucket 0.

Example 3

Input

visits:
| user_id | visit_date  |
|---------|-------------|
| 5       | 2024-02-01  |
| 5       | 2024-02-10  |
| 6       | 2024-02-01  |
| 7       | 2024-02-01  |
| 8       | 2024-02-14  |

transactions:
| user_id | transaction_date | amount |
|---------|------------------|--------|
| 5       | 2024-02-01       | 10     |
| 5       | 2024-02-01       | 10     |
| 5       | 2024-02-10       | 5      |
| 5       | 2024-02-10       | 9      |
| 6       | 2024-02-01       | 3      |
| 8       | 2024-02-14       | 1      |
| 8       | 2024-02-14       | 2      |
| 8       | 2024-02-14       | 3      |
| 8       | 2024-02-14       | 4      |

Output

[
  {"transactions_count":0,"visits_count":1},
  {"transactions_count":1,"visits_count":1},
  {"transactions_count":2,"visits_count":2},
  {"transactions_count":3,"visits_count":0},
  {"transactions_count":4,"visits_count":1}
]

Explanation

Duplicate transaction rows still count as separate events. Bucket 3 must be present with zero visits.

Constraints

1 <= number of rows in visits <= 200000
0 <= number of rows in transactions <= 300000
1 <= user_id <= 1000000000
visit_date and transaction_date are valid YYYY-MM-DD dates
(user_id, visit_date) is unique in visits
transactions may contain duplicate rows and duplicates count normally
Every transaction row has a matching visit row with same user_id and date
Return all integer buckets from 0 through max visit-level transaction count
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact columns: transactions_count, visits_count

Preparing your editor. You can read the problem while it loads.

Visit Transaction Frequency Distribution

Hard

A retail-banking analytics team tracks branch attendance and in-branch transactions. Table: visits user_id: customer identifier visit_date: date of a branch visit (user_id, visit_date) is unique Table: transactions user_id: customer identifier transaction_date: date of a recorded transaction amount: transaction amount duplicate rows are allowed and each row counts as a separate transaction event Guarantee: Every row in transactions belongs to a valid visit with the same (user_id, date). Task: For each visit in visits, count how many transactions happened on that exact visit. Then produce a histogram with: transactions_count: number of transactions in one visit visits_count: how many visits had exactly that transactions_count Critical output rule: Include every integer bucket from 0 to max(transactions_count) observed in any visit. If a bucket has no visits, include it with visits_count = 0. Output requirements: Return exactly two columns: transactions_count, visits_count. Order by transactions_count ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

visits:
| user_id | visit_date  |
|---------|-------------|
| 1       | 2024-01-01  |
| 2       | 2024-01-02  |
| 12      | 2024-01-01  |
| 19      | 2024-01-03  |
| 1       | 2024-01-02  |
| 2       | 2024-01-03  |
| 1       | 2024-01-04  |
| 7       | 2024-01-11  |
| 9       | 2024-01-25  |
| 8       | 2024-01-28  |

transactions:
| user_id | transaction_date | amount |
|---------|------------------|--------|
| 1       | 2024-01-02       | 120    |
| 2       | 2024-01-03       | 22     |
| 7       | 2024-01-11       | 232    |
| 1       | 2024-01-04       | 7      |
| 9       | 2024-01-25       | 33     |
| 9       | 2024-01-25       | 66     |
| 8       | 2024-01-28       | 1      |
| 9       | 2024-01-25       | 99     |

Output

[
  {"transactions_count":0,"visits_count":4},
  {"transactions_count":1,"visits_count":5},
  {"transactions_count":2,"visits_count":0},
  {"transactions_count":3,"visits_count":1}
]

Explanation

Visit-level transaction counts are [0,0,0,0,1,1,1,1,1,3]. The histogram must include bucket 2 even though no visit produced exactly two transactions.

Example 2

Input

visits:
| user_id | visit_date  |
|---------|-------------|
| 31      | 2024-03-01  |
| 31      | 2024-03-02  |
| 45      | 2024-03-01  |
| 60      | 2024-03-04  |

transactions:
| user_id | transaction_date | amount |
|---------|------------------|--------|
| (no rows) |

Output

[
  {"transactions_count":0,"visits_count":4}
]

Explanation

No visit has any transaction, so max bucket is 0 and the output contains only bucket 0.

Example 3

Input

visits:
| user_id | visit_date  |
|---------|-------------|
| 5       | 2024-02-01  |
| 5       | 2024-02-10  |
| 6       | 2024-02-01  |
| 7       | 2024-02-01  |
| 8       | 2024-02-14  |

transactions:
| user_id | transaction_date | amount |
|---------|------------------|--------|
| 5       | 2024-02-01       | 10     |
| 5       | 2024-02-01       | 10     |
| 5       | 2024-02-10       | 5      |
| 5       | 2024-02-10       | 9      |
| 6       | 2024-02-01       | 3      |
| 8       | 2024-02-14       | 1      |
| 8       | 2024-02-14       | 2      |
| 8       | 2024-02-14       | 3      |
| 8       | 2024-02-14       | 4      |

Output

[
  {"transactions_count":0,"visits_count":1},
  {"transactions_count":1,"visits_count":1},
  {"transactions_count":2,"visits_count":2},
  {"transactions_count":3,"visits_count":0},
  {"transactions_count":4,"visits_count":1}
]

Explanation

Duplicate transaction rows still count as separate events. Bucket 3 must be present with zero visits.

Constraints

1 <= number of rows in visits <= 200000
0 <= number of rows in transactions <= 300000
1 <= user_id <= 1000000000
visit_date and transaction_date are valid YYYY-MM-DD dates
(user_id, visit_date) is unique in visits
transactions may contain duplicate rows and duplicates count normally
Every transaction row has a matching visit row with same user_id and date
Return all integer buckets from 0 through max visit-level transaction count
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact columns: transactions_count, visits_count