Monthly Transaction Tally
0:00:00
Your team at Capital One wants a unified view of settled card transactions.
Using both card and settlement transaction logs, determine how many card transactions bank successfully settled for each card user during August 2025.
A card transaction is considered settled if it is simultaneously “approved” in card_transactions and “settled” in settlement_transactions table.
Note:
1. The settlement_transactions table may contain transactions from sources other than cards.
2. Both tables have composite primary key on columns txn_ts and external_ref
3. A card user is any user logged in the card_transactions table, regardless of the time or transaction status.
Schema:
Input:
card_transactions table
| column | type |
|---|---|
| txn_id | INT |
| user_id | INT |
| txn_ts | DATETIME |
| amount | DECIMAL |
| status | VARCHAR |
| external_ref | VARCHAR |
settlement_transactions table
| column | type |
|---|---|
| txn_id | INT |
| user_id | INT |
| txn_ts | DATETIME |
| amount | DECIMAL |
| status | VARCHAR |
| external_ref | VARCHAR |
Output:
| column | type |
|---|---|
| user_id | INT |
| txn_count | INT |
Example:
Input:
card_transactions table
| txn_id | user_id | txn_ts | amount | status | external_ref |
|---|---|---|---|---|---|
| 1 | 101 | 2025-07-30 11:22:00 | 11.11 | approved | abcd123 |
| 2 | 101 | 2025-08-02 11:22:00 | 11.11 | approved | abcd123 |
| 3 | 101 | 2025-08-02 11:59:55 | 22.22 | pre-auth | abcd222 |
| 4 | 101 | 2025-08-02 12:00:00 | 22.22 | approved | abcd222 |
| 5 | 101 | 2025-08-04 23:00:00 | 60.00 | approved | abcd456 |
| 6 | 101 | 2025-08-05 09:40:00 | 80.00 | declined | abcd999 |
| 7 | 103 | 2025-08-06 09:40:00 | 99.99 | approved | abcd999 |
settlement_transactions table
| settlement_id | user_id | txn_ts | amount | status | external_ref |
|---|---|---|---|---|---|
| 10 | 101 | 2025-07-30 11:22:00 | 11.11 | settled | abcd123 |
| 11 | 101 | 2025-08-02 11:22:00 | 11.11 | settled | abcd123 |
| 12 | 101 | 2025-08-02 12:00:00 | 22.22 | settled | abcd222 |
| 13 | 101 | 2025-08-04 23:00:00 | 60.00 | pending | abcd456 |
| 14 | 101 | 2025-08-04 11:00:00 | 50.00 | settled | zzzz111 |
| 15 | 103 | 2025-08-06 09:40:00 | 99.99 | pending | abcd999 |
| 16 | 103 | 2025-08-06 11:40:00 | 99.99 | settled | abcd999 |
Output:
| user_id | txn_count |
|---|---|
| 101 | 2 |
| 103 | 0 |
Explanation:
User 101
- txn_id 1 - excluded, not in August
- txn_id 2 - included
- txn_id 3 - excluded, status
- txn_id 4 - included
- txn_id 5 - excluded, corresponding settlement transaction settlement_id = 13 is “pending”
- txn_id 6 - excluded, status
User 103
- txn_id 7 - excluded, corresponding settlement transaction settlement_id = 15 is “pending”
Note: settlement_id = 16 is not a card transaction, since its timetamp doesn’t match any transaction logged in card_transactions table
.
.
.
.
Comments