Query Active Loans by Borrower ID in SQL

loans

+---------+------------+----------+-----------+---------------+
| loan_id | borrower_id| amount   | status    | payment_date  |
+---------+------------+----------+-----------+---------------+
| 1001    | 501        | 5000.00  | ACTIVE    | 2024-01-15    |
| 1002    | 502        | 7000.00  | DEFAULTED | 2023-12-20    |
| 1003    | 501        | 3000.00  | ACTIVE    | 2024-02-10    |
| 1004    | 503        | 4500.00  | CLOSED    | 2023-11-01    |
| 1005    | 504        | 6500.00  | ACTIVE    | 2024-03-05    |
+---------+------------+----------+-----------+---------------+

Scenario

Live CodeShare SQL task for a LendingClub risk-analytics position; candidate must query loan data that feeds credit-decision systems.

Question

Using the loans table below, write a SQL query that returns each borrower_id and the total amount of all loans whose status is 'ACTIVE', ordered by borrower_id ascending.

Hints

Filter by status, GROUP BY borrower_id, SUM amount, ORDER BY borrower_id.

Total Active Loan Amount per Borrower

Table loans

column	type	constraints
loan_id	INTEGER	PRIMARY KEY, NOT NULL
borrower_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
status	VARCHAR(20)	NOT NULL
payment_date	DATE	NOT NULL

Sample data:

amount	borrower_id	loan_id	payment_date	status
5000	501	1001	2024-01-15	ACTIVE
7000	502	1002	2023-12-20	DEFAULTED
3000	501	1003	2024-02-10	ACTIVE
4500	503	1004	2023-11-01	CLOSED
6500	504	1005	2024-03-05	ACTIVE

Expected output

Each row lists a borrower_id and the sum of amounts for that borrower's loans where status = 'ACTIVE', ordered by borrower_id ascending.

borrower_id	total_amount
501	8000
504	6500

Hints

1. Filter by status = 'ACTIVE'.
2. GROUP BY borrower_id.
3. Use SUM(amount) AS total_amount.
4. ORDER BY borrower_id ASC.