Missed minimum payments
mediumMissed minimum payments
Capital One SQL Interview Question
Capital One's collections team contacts customers who repeatedly pay less than the minimum amount due on their card statement.
Find every customer who paid less than the minimum due in 2 or more months. Show the customer_name and the number of those months as missed_payments.
Asked of
- Data Analyst
- Business Analyst
- Data Scientist
- ML Engineer
statementsTable18 rows
| Column Name | Type |
|---|---|
| statement_id | BIGINT |
| account_id | BIGINT |
| statement_month | DATE |
| balance | DOUBLE |
| minimum_due | DOUBLE |
| amount_paid | DOUBLE |
accountsTable8 rows
| Column Name | Type |
|---|---|
| account_id | BIGINT |
| customer_name | VARCHAR |
| product | VARCHAR |
| credit_limit | BIGINT |
| opened_date | DATE |
statementsExample Input
| statement_id | account_id | statement_month | balance | minimum_due | amount_paid |
|---|---|---|---|---|---|
| 1 | 601 | 2024-04-01 | 1200 | 35 | 35 |
| 2 | 602 | 2024-04-01 | 2400 | 60 | 2400 |
| 3 | 604 | 2024-04-01 | 1350 | 40 | 20 |
| 5 | 601 | 2024-05-01 | 1850 | 45 | 45 |
| 6 | 602 | 2024-05-01 | 3100 | 75 | 500 |
| 7 | 604 | 2024-05-01 | 2100 | 55 | 0 |
| 9 | 601 | 2024-06-01 | 2250 | 55 | 60 |
| 10 | 602 | 2024-06-01 | 2900 | 70 | 2900 |
| 11 | 604 | 2024-06-01 | 2640 | 65 | 65 |
accountsExample Input
| account_id | customer_name | product | credit_limit | opened_date |
|---|---|---|---|---|
| 601 | Jordan Lee | Credit Card | 5000 | 2021-03-15 |
| 602 | Aisha Khan | Credit Card | 12000 | 2019-08-02 |
| 603 | Mateo Garcia | Savings | NULL | 2020-11-20 |
| 604 | Emily Chen | Credit Card | 3000 | 2022-06-10 |
Example Output
| customer_name | missed_payments |
|---|---|
| Emily Chen | 2 |
Explanation
Emily Chen paid 20 against a minimum of 40 in April and 0 against 55 in May. In June, the payment was exactly the minimum of 65, which is not less than the minimum, so June does not count. That gives 2 missed payments.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Capital One
Difficulty
medium
Topic
aggregations
Your query
Starting the SQL engine...