Number of Transactions per Visit - Problem

You are given two tables: Visits and Transactions.

The Visits table records every visit a user makes to the bank with columns user_id and visit_date.

The Transactions table records every transaction with columns user_id, transaction_date, and amount. Multiple transactions can occur on the same date.

A bank wants to create a chart showing:

  • How many users made exactly 0 transactions in a single visit
  • How many users made exactly 1 transaction in a single visit
  • How many users made exactly 2 transactions in a single visit
  • And so on...

Write a SQL query to count the number of users for each possible transaction count (from 0 to the maximum transactions made by any user in a single visit).

Return the result with columns transactions_count and visits_count, ordered by transactions_count.

Table Schema

Visits
Column Name Type Description
user_id PK int ID of the user visiting the bank
visit_date PK date Date of the visit
Primary Key: (user_id, visit_date)
Transactions
Column Name Type Description
user_id int ID of the user making transaction
transaction_date date Date of the transaction (same as visit_date)
amount int Transaction amount
Primary Key: None (may contain duplicates)

Input & Output

Example 1 — Mixed Transaction Counts
Input Tables:
Visits
user_id visit_date
1 2020-01-01
2 2020-01-02
12 2020-01-01
19 2020-01-02
1 2020-01-03
2 2020-01-03
2 2020-01-04
3 2020-01-05
Transactions
user_id transaction_date amount
1 2020-01-02 120
2 2020-01-03 22
3 2020-01-05 101
12 2020-01-01 30
1 2020-01-03 38
1 2020-01-03 12
Output:
transactions_count visits_count
0 4
1 3
2 1
💡 Note:

Breaking down each visit: User 1 on 2020-01-01 (0 transactions), User 2 on 2020-01-02 (0 transactions), User 12 on 2020-01-01 (1 transaction), User 19 on 2020-01-02 (0 transactions), User 1 on 2020-01-03 (2 transactions), User 2 on 2020-01-03 (1 transaction), User 2 on 2020-01-04 (0 transactions), User 3 on 2020-01-05 (1 transaction). So we have 4 visits with 0 transactions, 3 visits with 1 transaction, and 1 visit with 2 transactions.
Example 2 — All Zero Transactions
Input Tables:
Visits
user_id visit_date
1 2020-01-01
2 2020-01-02
Transactions
user_id transaction_date amount
Output:
transactions_count visits_count
0 2
💡 Note:

Both visits have no corresponding transactions, so all visits have 0 transactions. The result shows 2 visits with 0 transactions.
Example 3 — High Transaction Count
Input Tables:
Visits
user_id visit_date
1 2020-01-01
Transactions
user_id transaction_date amount
1 2020-01-01 100
1 2020-01-01 200
1 2020-01-01 300
Output:
transactions_count visits_count
0 0
1 0
2 0
3 1
💡 Note:

User 1's single visit on 2020-01-01 has 3 transactions. The result must show the complete range from 0 to 3, where only transactions_count = 3 has 1 visit.

Constraints

  • 1 ≤ user_id ≤ 1000
  • visit_date and transaction_date are valid dates
  • 1 ≤ amount ≤ 1000
  • Each transaction corresponds to a visit (guaranteed by problem statement)

Visualization

Tap to expand
Transactions per Visit Distribution AnalysisInput: Visit-Transaction Pairsuser_idvisit_datetx_count12020-01-01022020-01-02012020-01-032122020-01-011GROUP BYtx_countOutput: Distributiontransactions_countvisits_count0211212 visits with0 transactions1 visit with1 transactionComplete range from 0 to MAX ensures no gaps in transaction counts
Understanding the Visualization
1
Join Tables
LEFT JOIN preserves zero-transaction visits
2
Count Transactions
GROUP BY visit to count transactions
3
Generate Range
Create 0 to MAX transaction counts
4
Count Distribution
Count visits for each transaction level
Key Takeaway
🎯 Key Insight: Use LEFT JOIN to preserve zero-transaction visits, essential for complete distribution analysis

Related Problems

Asked in
Amazon 28 Microsoft 22 Goldman Sachs 18
32.0K Views
Medium Frequency
~25 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen