Affirm Loan Reporting Design | Dataford Interview Questions

Given Affirm's loan process, how would you design your tables and write queries to answer reporting questions like total number of customers? Use the provided customer and loan data to produce a single summary report that includes customers with and without loans.

Output

Return one row with total_customers, customers_with_loans, approved_loans, and approved_principal
Include every customer in total_customers, including customers without loans
Return the summary as a single row

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique Affirm customer identifier
full_name	VARCHAR(120)	Customer's full name
email	VARCHAR(255)	Customer email address

loans

Column	Type	Description
loan_idPK	INT	Unique loan identifier
customer_id	INT	Customer who applied for the loan
loan_status	VARCHAR(30)	Current loan status
principal_amount	NUMERIC(12,2)	Original loan principal
merchant_name	VARCHAR(120)	Merchant associated with the loan

Your question is Affirm Loan Reporting Design. 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