Banking: Total Amount Deposited Per Customer

SQL coding challenge · Difficulty: medium · +100 XP

Problem

The bank's risk team wants to know the total amount deposited by each customer across all their accounts. Only count type = 'deposit' transactions. Customers with no deposits → show 0.

Tables

Table: Customers

| customer_id | name | email |
| --- | --- | --- |
| 101 | Krishna | krishna@gmail.com |
| 102 | Sudheer | sudheer@gmail.com |
| 103 | Raj | raj@gmail.com |
| 104 | Ashna | ashna@gmail.com |
| 105 | Parvati | parvati@gmail.com |

Table: Accounts

| account_id | customer_id | account_type | balance |
| --- | --- | --- | --- |
| 201 | 101 | Savings | 25000.00 |
| 202 | 102 | Current | 15000.00 |
| 203 | 103 | Savings | 32000.00 |
| 204 | 104 | Current | 18000.00 |
| 205 | 105 | Savings | 27500.00 |

Table: Transactions

| txn_id | account_id | txn_date | txn_type | amount |
| --- | --- | --- | --- | --- |
| 301 | 201 | 2024-05-01 | Deposit | 5000.00 |
| 302 | 202 | 2024-05-02 | Withdrawal | 3000.00 |
| 303 | 201 | 2024-05-03 | Withdrawal | 2000.00 |
| 304 | 203 | 2024-05-04 | Deposit | 4000.00 |
| 305 | 204 | 2024-05-05 | Deposit | 6000.00 |
| 306 | 205 | 2024-05-06 | Withdrawal | 1500.00 |

Expected Output

| name | total_deposited |
| --- | --- |
| Ashna | 6000.00 |
| Krishna | 5000.00 |
| Parvati | 0.00 |
| Raj | 4000.00 |
| Sudheer | 0.00 |

Return: customer_name, total_deposited
Only sum deposit transactions
Customers with no deposits → 0.00
Sort by total_deposited descending