Rank Tenants by Payment Timeliness | Dataford Interview Questions

Your question is Rank Tenants by Payment Timeliness. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

Schema

properties

Column	Type	Description
property_idPK	INT	Unique property identifier
property_name	VARCHAR(100)	Property name

tenants

Column	Type	Description
tenant_idPK	INT	Unique tenant identifier
property_id	INT	Property associated with the tenant
tenant_name	VARCHAR(100)	Tenant full name

rent_payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
tenant_id	INT	Tenant responsible for the payment
due_date	DATE	Contractual payment due date
paid_date	DATE	Date the payment was received
payment_status	VARCHAR(20)	Payment processing status