Reverse Rows in a DataFrame

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

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

Output details

• Return columns in this order: loan_id, reversed_position, original_event_id, event_type, event_date, and amount.
• Sort loan_id and reversed_position in ascending order, placing NULL reversed_position values last.