Question

Formatted question description: https://leetcode.ca/all/1555.html

 1555. Bank Account Summary
 SQL Schema
 Table: Users

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


 Table: Transaction

 +---------------+---------+
 | Column Name   | Type    |
 +---------------+---------+
 | trans_id      | int     |
 | paid_by       | int     |
 | paid_to       | int     |
 | amount        | int     |
 | transacted_on | date    |
 +---------------+---------+
 trans_id is the primary key for this table.
 Each row of this table contains the 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 wheter they have breached their credit limit (If their current credit is less than 0).

 Write an SQL query to report.

 user_id
 user_name
 credit, current balance after performing transactions.
 credit_limit_breached, check credit_limit ("Yes" or "No")
 Return the result table in any order.

 The query result format is in the following example.



 Users table:
 +------------+--------------+-------------+
 | user_id    | user_name    | credit      |
 +------------+--------------+-------------+
 | 1          | Moustafa     | 100         |
 | 2          | Jonathan     | 200         |
 | 3          | Winston      | 10000       |
 | 4          | Luis         | 800         |
 +------------+--------------+-------------+

 Transaction 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    |
 +------------+------------+------------+----------+---------------+

 Result table:
 +------------+------------+------------+-----------------------+
 | user_id    | user_name  | credit     | credit_limit_breached |
 +------------+------------+------------+-----------------------+
 | 1          | Moustafa   | -100       | Yes                   |
 | 2          | Jonathan   | 500        | No                    |
 | 3          | Winston    | 9990       | No                    |
 | 4          | Luis       | 800        | No                    |
 +------------+------------+------------+-----------------------+

 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 didn't received any transfer, credit = $800

Algorithm

– step 1, intermediate data: transanction per user

/*

| trans_id   | paid_by    | paid_to    | amount   | transacted_on |
+------------+------------+------------+----------+---------------+
| 1          | 1          | 3          | 400      | 2020-08-01    |

will become

| user_id    |   trans    |
+------------+------------+
| 1          | -400       |
| 3          | 400        |

*/

– step 2, aggregate step-1 result to get final result

Code

SQL

-- step 1, intermediate data: transanction per user

select paid_by as user_id, -amount as trans
from Transaction
union all
select paid_to as user_id, amount as trans
from Transaction



-- step 2, aggregate step-1 result to get final result

select Users.user_id as user_id
    , Users.user_name as user_name
    , credit + ifnull(sum(trans),0) as credit
    , case when credit + ifnull(sum(trans),0) > 0 then 'No' else 'Yes' end as credit_limit_breached
from(
    select paid_by as user_id, -amount as trans
    from Transaction
    union all
    select paid_to as user_id, amount as trans
    from Transaction
) t
right join users
    on t.user_id=users.user_id
group by user_id

All Problems

All Solutions