Top-Decile Risk Segments SQL | Dataford Interview Questions

American Credit Acceptance wants to monitor delinquency patterns across its auto loan portfolio. Using posted payment transactions from 2025, write a PostgreSQL query to identify the top-decile risk segment, where a segment is defined by borrower state and risk tier.

Requirements

Join loans to payment transactions and include only transactions dated in 2025.
Calculate payment count, delinquent payment count, and delinquency rate for each state and risk tier. Treat LATE and CHARGED_OFF as delinquent.
Use a ranking window function to divide segments into ten risk deciles, with the highest delinquency rates in decile 1.
Return only the top-decile segment, ordered by delinquency rate descending and delinquent payment count descending.

Schema

loans

Column	Type	Description
loan_idPK	INTEGER	Unique auto loan identifier
state	VARCHAR(2)	Borrower state
risk_tier	VARCHAR(30)	Underwriting risk classification

payment_transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique payment transaction identifier
loan_id	INTEGER	Loan associated with the payment transaction
transaction_date	DATE	Date the payment transaction was posted
status	VARCHAR(20)	Payment outcome status

Your question is Top-Decile Risk Segments SQL. Start with the requirements and the two 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