Top Credit Score Applicants by State | Dataford Interview Questions

How would you identify the top 5% of loan applicants based on their credit score within each state at American Credit Acceptance?

Write a SQL query using the provided applicant, application, and state reference data. Exclude applicants without a credit score and include applicants whose score meets or exceeds their state's 95th percentile cutoff.

Output

One row per qualifying applicant, with applicant_id, applicant_name, state_code, state_name, credit_score, state_percentile_cutoff, and state_rank.
Sort by state_code, descending credit_score, then applicant_id.

Schema

loan_applications

Column	Type	Description
application_idPK	INT	Unique loan application identifier
applicant_id	INT	Applicant associated with the application
state_code	VARCHAR(10)	State code associated with the application
credit_score	NUMERIC(4,0)	Applicant credit score

applicant_profiles

Column	Type	Description
applicant_idPK	INT	Unique applicant identifier
applicant_name	VARCHAR(100)	Applicant display name

state_reference

Column	Type	Description
state_codePK	VARCHAR(10)	State abbreviation or reference code
state_name	VARCHAR(50)	Full state name
region	VARCHAR(30)	Broad geographic region

Your question is Top Credit Score Applicants by State. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem