SQL Multi-Concept Query

HardSQL · PostgreSQL

Problem

Applied Data Finance needs a servicing report for loans originated through its lending platform. Write a PostgreSQL query that identifies loans with the second-highest distinct principal amount within each product and combines borrower, product, and payment information.

Requirements

Rank distinct loan principal amounts within each product and retain every loan tied for second place.
Display each origination date as YYYY-MM-DD using PostgreSQL date formatting.
Return the borrower name, product name, principal amount, total non-null payments, and the most recent payment date and amount.
Include loans without payments, showing a total payment amount of 0 and NULL for latest payment fields.
Sort by product name, then loan ID.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique borrower identifier
customer_name	VARCHAR(100)	Borrower full name
email	VARCHAR(255)	Borrower email address

products

Column	Type	Description
product_idPK	INT	Unique lending product identifier
product_name	VARCHAR(100)	Name of the Applied Data Finance lending product

loans

Column	Type	Description
loan_idPK	INT	Unique loan identifier
customer_id	INT	References customers.customer_id
product_id	INT	References products.product_id
principal_amount	NUMERIC(12,2)	Approved loan principal
origination_date	DATE	Date the loan was originated

payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
loan_id	INT	References loans.loan_id
payment_date	DATE	Date the payment was received
payment_amount	NUMERIC(12,2)	Payment amount received

Tablescustomersproductsloanspayments