Coding questions · Affirm
Analyze Loan Payments Using Pandas for Key Insights
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.
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.
Loan payment success rate and repayment status per loan
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.
Table loan_payments
| column | type | constraints |
|---|---|---|
| loan_id | VARCHAR | |
| payment_id | VARCHAR | |
| payment_date | DATE | |
| amount | DECIMAL(10,2) | |
| status | VARCHAR |
Sample data:
| amount | loan_id | payment_date | payment_id | status |
|---|---|---|---|---|
| 250 | L1 | 2023-01-15 | P1 | success |
| 250 | L1 | 2023-02-15 | P2 | success |
| 250 | L1 | 2023-03-15 | P3 | success |
| 250 | L1 | 2023-04-15 | P4 | success |
| 500 | L2 | 2023-01-20 | P5 | failed |
Expected output
One row per loan_id with: total_paid = sum of amounts from successful payments only; success_rate = count of successful payments divided by total payments; fully_repaid = 1 if total_paid >= 1000, else 0.
| fully_repaid | loan_id | success_rate | total_paid |
|---|---|---|---|
| 1 | L1 | 1 | 1000 |
| 0 | L2 | 0 | 0 |
Hints
- Use GROUP BY loan_id with conditional aggregation (CASE WHEN).
- Compute total_paid by summing amount only when status = 'success'.
- Compute success_rate as AVG(CASE WHEN status = 'success' THEN 1.0 ELSE 0.0 END).
- Flag fully repaid with CASE WHEN SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) >= 1000 THEN 1 ELSE 0 END.