Reverse Rows in a DataFrame | Dataford Interview Questions

Protium's analytics team receives repayment events in chronological order but sometimes needs to display each loan's event rows in reverse order for investigation. Write a PostgreSQL query that reverses the event sequence independently for every active loan.

Requirements

Use a window function to establish each loan's chronological event order, using event_date and event_id as a deterministic tie-breaker.
Return active loans, including active loans with no repayment events.
Assign reversed_position = 1 to the latest event for each loan, and preserve the original event details.
Order the final result by loan_id and reversed position, placing loans without events last within their loan ordering.

Schema

loans

Column	Type	Description
loan_idPK	INTEGER	Unique Protium loan identifier
borrower_name	VARCHAR(100)	Borrower's name
status	VARCHAR(20)	Current loan status

repayment_events

Column	Type	Description
event_idPK	INTEGER	Unique repayment event identifier
loan_id	INTEGER	Referenced loan identifier
event_date	DATE	Date the event was recorded
event_type	VARCHAR(30)	Repayment event category
amount	NUMERIC(10,2)	Event amount, which may be NULL

Your question is Reverse Rows in a DataFrame. 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