AppFolio Property Manager provides property teams with payment and lease activity data. Write a PostgreSQL query that ranks tenants by payment timeliness within each property.
A payment is considered completed only when payment_status = 'paid' and paid_date is not null. A completed payment is on time when its paid_date is on or before its due_date.
Requirements
- Calculate each tenant's completed payment count, on-time payment percentage, and average days late. Treat on-time payments as zero days late.
- Include only tenants with at least two completed payments.
- Rank tenants separately within each property by on-time percentage descending, then average days late ascending. Use the tenant ID as a deterministic final tie-breaker.
- Return results ordered by property ID and tenant rank.
