Katkat
Back

Query Active Loans by Borrower ID in SQL

LendingClubData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates proficiency with SQL data aggregation, grouping, and ordering to compute borrower-level loan totals, assessing practical skills in relational data manipulation within the Data Manipulation (SQL/Python) domain.

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.