Early Loan Payment Completion | Dataford Interview Questions

Write a SQL query to identify members who have completed their loan payments ahead of schedule on Happy Money.

Use the members, loans, and loan_payments tables. Consider only completed loans and posted payments. Treat the latest posted payment date as the actual completion date.

Output

One row per qualifying member with member_id, member_name, ahead_of_schedule_loans, and max_days_ahead
Include only members with at least one completed loan paid before its scheduled end date
Order by max_days_ahead descending, then member_id ascending

Schema

members

Column	Type	Description
member_idPK	INT	Unique Happy Money member identifier
member_name	VARCHAR(100)	Member's display name
email	VARCHAR(255)	Member email address

loans

Column	Type	Description
loan_idPK	INT	Unique loan identifier
member_id	INT	Member who holds the loan
loan_status	VARCHAR(20)	Current loan status
scheduled_end_date	DATE	Scheduled date for the final loan payment

loan_payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
loan_id	INT	Loan associated with the payment
payment_date	DATE	Date the payment was posted
payment_status	VARCHAR(20)	Processing status of the payment
payment_amount	DECIMAL(12,2)	Payment amount

Your question is Early Loan Payment Completion. 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