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
Number of Transactions per Visit INPUT Visits Table user_id date 1 Jan-1 2 Jan-1 1 Jan-2 3 Jan-1 Transactions Table user_id date amount 1 Jan-1 100 1 Jan-1 200 1 Jan-2 150 2 Jan-1 50 Goal: Count visits by transaction count ALGORITHM STEPS 1 LEFT JOIN Tables Visits LEFT JOIN Transactions on user_id and date 2 Group by Visit GROUP BY user_id, date COUNT transactions per visit 3 Generate Sequence Create 0 to MAX(count) using recursive CTE 4 Final Aggregation LEFT JOIN sequence with visit counts, fill 0s Intermediate Result: user1, Jan-1: 2 txns user2, Jan-1: 1 txn user1, Jan-2: 1 txn user3, Jan-1: 0 txns FINAL RESULT Output Table transactions_count visits 0 1 1 2 2 1 Distribution View 0 txn: 1 visit 1 txn: 2 visits 2 txn: 1 visit OK - Complete! All counts 0 to max shown Key Insight: Use LEFT JOIN to include visits with zero transactions. Generate a sequence from 0 to MAX using recursive CTE to ensure all transaction counts appear in output, even if no visits had that exact count. Fill missing counts with 0 using COALESCE or IFNULL. TutorialsPoint - Number of Transactions per Visit | Optimal Solution

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