Practical Exam: Loan Insights
EasyLoan offers a wide range of loan services, including personal loans, car loans, and mortgages.
EasyLoan offers loans to clients from Canada, United Kingdom and United States.
The analytics team wants to report performance across different geographic areas. They aim to identify areas of strength and weakness for the business strategy team.
They need your help to ensure the data is accessible and reliable before they start reporting.
Database Schema
The data you need is in the database named lending.
Task 1
The analytics team wants to use the client table to create a dashboard for client details. For them to proceed, they need to be sure the data is clean enough to use.
The client table below illustrates what the analytics team expects the data types and format to be.
Write a query that makes the client table match the description provided. Your query should not update the client table.
| Column Name | Description |
|---|---|
| client_id | Unique integer (set by the database, can’t take any other value) |
| date_of_birth | Date of birth of the client, as a date (format: YYYY-MM-DD) |
| employment_status | Current employment status of the client, either employed or unemployed, as a lower case string |
| country | The country where the client resides, either USA, UK or CA, as an upper case string |
SELECT
client_id,
CAST(date_of_birth AS DATE) AS date_of_birth,
CASE
WHEN employment_status = 'Full-time' THEN 'employed'
WHEN employment_status = 'Part-time' THEN 'employed'
WHEN employment_status IN ('Emplouyed', 'employed') THEN 'employed'
ELSE 'unemployed'
END AS employment_status,
UPPER(country) AS country
FROM client;
Task 2
You have been told that there was a problem in the backend system as some of the repayment_channel values are missing.
The missing values are critical to the analysis so they need to be filled in before proceeding.
Luckily, they have discovered a pattern in the missing values:
- Repayment higher than 4000 dollars should be made via
bank account. - Repayment lower than 1000 dollars should be made via
mail.
Write a query that makes the repayment table match this criteria.
SELECT
repayment_id,
loan_id,
repayment_date,
repayment_amount,
CASE
WHEN repayment_channel = '-' THEN
CASE
WHEN repayment_amount > 4000 THEN 'bank account'
WHEN repayment_amount < 1000 THEN 'mail'
END
ELSE repayment_channel
END AS repayment_channel
FROM
repayment
WHERE
repayment_channel IN ('mail', 'phone', 'credit card', 'debit card', '-', 'bank account');Task 3
Starting on January 1st, 2022, all US clients started to use an online system to sign contracts.
The analytics team wants to analyze the loans for US clients who used the new online system.
Write a query that returns the data for the analytics team. Your output should include client_id,contract_date, principal_amount and loan_type columns.
SELECT l.client_id, contract_date, principal_amount, loan_type
FROM loan AS l
INNER JOIN client AS ci
ON l.client_id = ci.client_id
INNER JOIN contract AS c
ON l.contract_id = c.contract_id
WHERE country = 'USA' AND contract_date >= '2022-01-01'Task 4
The business strategy team is considering offering a more competitive rate to the US market.
The analytic team want to compare the average interest rates offered by the company for the same loan type in different countries to determine if there are significant differences.
Write a query that returns the data for the analytics team. Your output should include loan_type, country and avg_rate columns.
SELECT loan_type, country, AVG(interest_rate) AS avg_rate
FROM loan AS l
INNER JOIN client AS ci
ON l.client_id = ci.client_id
GROUP BY loan_type, country