Net Credit Balance and Limit Alerts

Medium

A digital wallet team needs a post-settlement balance snapshot for every account holder. Table: users user_id: unique account identifier user_name: display name credit: starting credit before applying transfers Table: transactions trans_id: unique transfer identifier paid_by: sender user_id paid_to: receiver user_id amount: transfer amount transacted_on: transfer date Accounting rules: For each transfer, subtract amount from paid_by. For the same transfer, add amount to paid_to. All transfers are applied to the users table to produce final balances. Task: Return every user with: final credit after all transfers credit_limit_breached = "Yes" if final credit < 0, else "No" Output requirements: Return columns in this exact order: user_id user_name credit credit_limit_breached Order by user_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

users:
| user_id | user_name | credit |
|---------|-----------|--------|
| 1       | Alina     | 500    |
| 2       | Bruno     | 200    |
| 3       | Chi       | 1200   |
| 4       | Dev       | 300    |

transactions:
| trans_id | paid_by | paid_to | amount | transacted_on |
|----------|---------|---------|--------|---------------|
| 1        | 1       | 3       | 700    | 2026-02-01    |
| 2        | 3       | 2       | 150    | 2026-02-02    |
| 3        | 2       | 1       | 120    | 2026-02-04    |
| 4        | 2       | 2       | 50     | 2026-02-05    |

Output

[
  {"user_id":1,"user_name":"Alina","credit":-80,"credit_limit_breached":"Yes"},
  {"user_id":2,"user_name":"Bruno","credit":230,"credit_limit_breached":"No"},
  {"user_id":3,"user_name":"Chi","credit":1750,"credit_limit_breached":"No"},
  {"user_id":4,"user_name":"Dev","credit":300,"credit_limit_breached":"No"}
]

Explanation

Each transfer contributes a debit for the sender and a credit for the receiver. A self-transfer (2 -> 2) has net zero impact on final balance.

Example 2

Input

users:
| user_id | user_name | credit |
|---------|-----------|--------|
| 10      | Sam       | -20    |
| 11      | Sam       | 40     |
| 12      | Rhea      | 0      |

transactions:
| trans_id | paid_by | paid_to | amount | transacted_on |
|----------|---------|---------|--------|---------------|
| 100      | 11      | 10      | 60     | 2025-09-10    |
| 101      | 10      | 12      | 15     | 2025-09-15    |

Output

[
  {"user_id":10,"user_name":"Sam","credit":25,"credit_limit_breached":"No"},
  {"user_id":11,"user_name":"Sam","credit":-20,"credit_limit_breached":"Yes"},
  {"user_id":12,"user_name":"Rhea","credit":15,"credit_limit_breached":"No"}
]

Explanation

Users with the same display name are independent by user_id. Credit-limit breach is evaluated from final numeric balance only.

Example 3

Input

users:
| user_id | user_name | credit |
|---------|-----------|--------|
| 21      | Omar      | 100    |
| 22      | Priya     | -5     |

transactions:
| trans_id | paid_by | paid_to | amount | transacted_on |
|----------|---------|---------|--------|---------------|
| (no rows) |

Output

[
  {"user_id":21,"user_name":"Omar","credit":100,"credit_limit_breached":"No"},
  {"user_id":22,"user_name":"Priya","credit":-5,"credit_limit_breached":"Yes"}
]

Explanation

Users without transaction activity must still appear with their original credit values.

Constraints

1 <= number of rows in users <= 300000
0 <= number of rows in transactions <= 500000
1 <= user_id <= 1000000000 and user_id is unique in users
1 <= trans_id <= 1000000000 and trans_id is unique in transactions
-1000000000 <= credit <= 1000000000
0 <= amount <= 1000000000
paid_by and paid_to always reference existing users.user_id
transacted_on is a valid date (YYYY-MM-DD)
Return exactly: user_id, user_name, credit, credit_limit_breached
credit_limit_breached must be 'Yes' or 'No'
Order output by user_id ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with required columns

users: | user_id | user_name | credit | |---------|-----------|--------| | 1 | Alina | 500 | | 2 | Bruno | 200 | | 3 | Chi | 1200 | | 4 | Dev | 300 | transactions: | trans_id | paid_by | paid_to | amount | transacted_on | |----------|---------|---------|--------|---------------| | 1 | 1 | 3 | 700 | 2026-02-01 | | 2 | 3 | 2 | 150 | 2026-02-02 | | 3 | 2 | 1 | 120 | 2026-02-04 | | 4 | 2 | 2 | 50 | 2026-02-05 |

[ {"user_id":1,"user_name":"Alina","credit":-80,"credit_limit_breached":"Yes"}, {"user_id":2,"user_name":"Bruno","credit":230,"credit_limit_breached":"No"}, {"user_id":3,"user_name":"Chi","credit":1750,"credit_limit_breached":"No"}, {"user_id":4,"user_name":"Dev","credit":300,"credit_limit_breached":"No"} ]

users: | user_id | user_name | credit | |---------|-----------|--------| | 10 | Sam | -20 | | 11 | Sam | 40 | | 12 | Rhea | 0 | transactions: | trans_id | paid_by | paid_to | amount | transacted_on | |----------|---------|---------|--------|---------------| | 100 | 11 | 10 | 60 | 2025-09-10 | | 101 | 10 | 12 | 15 | 2025-09-15 |

[ {"user_id":10,"user_name":"Sam","credit":25,"credit_limit_breached":"No"}, {"user_id":11,"user_name":"Sam","credit":-20,"credit_limit_breached":"Yes"}, {"user_id":12,"user_name":"Rhea","credit":15,"credit_limit_breached":"No"} ]

users: | user_id | user_name | credit | |---------|-----------|--------| | 21 | Omar | 100 | | 22 | Priya | -5 | transactions: | trans_id | paid_by | paid_to | amount | transacted_on | |----------|---------|---------|--------|---------------| | (no rows) |