Analyze Loan Payments Using Pandas for Key Insights
Company: Affirm
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
loan_payments
+------------+------------+-------------+-----------+---------+
| loan_id | payment_id | payment_date | amount | status |
+------------+------------+-------------+-----------+---------+
| L1 | P1 | 2023-01-15 | 250.00 | success |
| L1 | P2 | 2023-02-15 | 250.00 | success |
| L1 | P3 | 2023-03-15 | 250.00 | success |
| L1 | P4 | 2023-04-15 | 250.00 | success |
| L2 | P5 | 2023-01-20 | 500.00 | failed |
+------------+------------+-------------+-----------+---------+
##### Scenario
Credit Risk – Analyze loan-payment data with pandas
##### Question
Using pandas, load the loan_payments table below and compute:
(a) total amount paid per loan_id,
(b) payment success rate per loan_id, and
(c) a flag indicating whether the loan has been fully repaid ($1,000 principal). Return the resulting DataFrame.
##### Hints
Use groupby, agg, and boolean logic.
Overview: This question evaluates data manipulation and aggregation skills using pandas, focusing on computing per-loan payment totals, payment success rates, and a repayment-status flag. It is commonly asked to assess applied data wrangling and numeric summarization abilities in the Data Manipulation (SQL/Python) domain, testing practical application rather than purely conceptual understanding.
Write an SQL query on the loan_payments table to compute, for each loan_id: (a) total amount paid from successful payments only, (b) payment success rate defined as successful payments divided by total payments, and (c) a flag fully_repaid that is 1 if total_paid is at least 1000 (the loan principal) and 0 otherwise. Return one row per loan_id.
Tables
loan_payments(loan_id VARCHAR, payment_id VARCHAR, payment_date DATE, amount DECIMAL(10,2), status VARCHAR)
Hints
- Use GROUP BY loan_id with conditional aggregation (CASE WHEN).
- Compute total_paid by summing amount only when status = 'success'.