0/304

00:00:00

Description

Editorial

Visit Transaction Frequency Distribution

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

visits =

[{"user_id":1,"visit_date":"2024-01-01"},{"user_id":2,"visit_date":"2024-01-02"},{"user_id":12,"visit_date":"2024-01-01"},{"user_id":19,"visit_date":"2024-01-03"},{"user_id":1,"visit_date":"2024-01-02"},{"user_id":2,"visit_date":"2024-01-03"},{"user_id":1,"visit_date":"2024-01-04"},{"user_id":7,"visit_date":"2024-01-11"},{"user_id":9,"visit_date":"2024-01-25"},{"user_id":8,"visit_date":"2024-01-28"}]

transactions =

[{"user_id":1,"transaction_date":"2024-01-02","amount":120},{"user_id":2,"transaction_date":"2024-01-03","amount":22},{"user_id":7,"transaction_date":"2024-01-11","amount":232},{"user_id":1,"transaction_date":"2024-01-04","amount":7},{"user_id":9,"transaction_date":"2024-01-25","amount":33},{"user_id":9,"transaction_date":"2024-01-25","amount":66},{"user_id":8,"transaction_date":"2024-01-28","amount":1},{"user_id":9,"transaction_date":"2024-01-25","amount":99}]

Visit Transaction Frequency Distribution

Hints

Visit Transaction Frequency Distribution

Hints