Problem

Table: Users

1
2
3
4
5
6
7
8
9

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| user_id      | int     |
| user_name    | varchar |
| credit       | int     |
+--------------+---------+
user_id is the primary key (column with unique values) for this table.
Each row of this table contains the current credit information for each user.

Table: Transactions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| trans_id      | int     |
| paid_by       | int     |
| paid_to       | int     |
| amount        | int     |
| transacted_on | date    |
+---------------+---------+
trans_id is the primary key (column with unique values) for this table.
Each row of this table contains information about the transaction in the bank.
User with id (paid_by) transfer money to user with id (paid_to).

Leetcode Bank (LCB) helps its coders in making virtual payments. Our bank records all transactions in the table Transaction , we want to find out the current balance of all users and check whether they have breached their credit limit (If their current credit is less than 0).

Write a solution to report.

  • user_id,
  • user_name,
  • credit, current balance after performing transactions, and
  • credit_limit_breached, check credit_limit ("Yes" or "No")

Return the result table in any order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

    Input: 
    Users table:
    +------------+--------------+-------------+
    | user_id    | user_name    | credit      |
    +------------+--------------+-------------+
    | 1          | Moustafa     | 100         |
    | 2          | Jonathan     | 200         |
    | 3          | Winston      | 10000       |
    | 4          | Luis         | 800         | 
    +------------+--------------+-------------+
    Transactions table:
    +------------+------------+------------+----------+---------------+
    | trans_id   | paid_by    | paid_to    | amount   | transacted_on |
    +------------+------------+------------+----------+---------------+
    | 1          | 1          | 3          | 400      | 2020-08-01    |
    | 2          | 3          | 2          | 500      | 2020-08-02    |
    | 3          | 2          | 1          | 200      | 2020-08-03    |
    +------------+------------+------------+----------+---------------+
    Output: 
    +------------+------------+------------+-----------------------+
    | user_id    | user_name  | credit     | credit_limit_breached |
    +------------+------------+------------+-----------------------+
    | 1          | Moustafa   | -100       | Yes                   | 
    | 2          | Jonathan   | 500        | No                    |
    | 3          | Winston    | 9900       | No                    |
    | 4          | Luis       | 800        | No                    |
    +------------+------------+------------+-----------------------+
    Explanation: 
    Moustafa paid $400 on "2020-08-01" and received $200 on "2020-08-03", credit (100 -400 +200) = -$100
    Jonathan received $500 on "2020-08-02" and paid $200 on "2020-08-08", credit (200 +500 -200) = $500
    Winston received $400 on "2020-08-01" and paid $500 on "2020-08-03", credit (10000 +400 -500) = $9990
    Luis did not received any transfer, credit = $800

Solution

Method 1 – SQL Aggregation and Join

Intuition

To get the current balance for each user, we need to subtract the total amount they paid from the total amount they received, and add this to their initial credit. If the resulting credit is less than 0, the credit limit is breached.

Approach

  1. For each user, calculate the sum of amounts received (paid_to) and the sum of amounts paid (paid_by) from the Transactions table.
  2. Compute the current credit as: Users.credit + received - paid.
  3. If the current credit is less than 0, set credit_limit_breached to ‘Yes’, else ‘No’.
  4. Return user_id, user_name, credit, and credit_limit_breached for all users.
MySQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
  u.user_id,
  u.user_name,
  u.credit + COALESCE(r.received, 0) - COALESCE(p.paid, 0) AS credit,
  CASE WHEN u.credit + COALESCE(r.received, 0) - COALESCE(p.paid, 0) < 0 THEN 'Yes' ELSE 'No' END AS credit_limit_breached
FROM Users u
LEFT JOIN (
  SELECT paid_to, SUM(amount) AS received
  FROM Transactions
  GROUP BY paid_to
) r ON u.user_id = r.paid_to
LEFT JOIN (
  SELECT paid_by, SUM(amount) AS paid
  FROM Transactions
  GROUP BY paid_by
) p ON u.user_id = p.paid_by;

Complexity

  • ⏰ Time complexity: O(N), where N is the number of rows in the Transactions table (for aggregation and joining).
  • 🧺 Space complexity: O(N) for storing intermediate results.