High credit utilization
mediumHigh credit utilization
Capital One SQL Interview Question
Capital One's credit risk team watches credit utilization, which is the share of a card's credit limit that a customer is using. High utilization can be an early sign that a customer is struggling.
Using each account's June 2024 statement, find every customer whose utilization is above 30%. Show the customer_name and utilization as utilization_pct: the balance as a percentage of the credit limit, rounded to 1 decimal place.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Data Scientist
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 | utilization_pct |
|---|---|
| Emily Chen | 88 |
| Jordan Lee | 45 |
Explanation
In June, Jordan Lee had a balance of 2,250 on a 5,000 limit, which is 45.0%. Emily Chen owed 2,640 on a 3,000 limit, which is 88.0%. Aisha Khan owed 2,900 on a 12,000 limit, only 24.2%, so Aisha Khan is left out.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Capital One
Difficulty
medium
Topic
calculations
Your query
Starting the SQL engine...