You are given a database table `properties` (id, address, unit_count), `leases` (id, property_id, tenant_id, start_date, end_date), and `payments` (id, lease_id, amount, payment_date). Write a SQL query to find the top 3 properties with the highest total collected rent in the last 12 months, including the property address and total amount.
technical screen · 10-15 minutes
How to structure your answer
The ideal answer utilizes a MECE (Mutually Exclusive, Collectively Exhaustive) approach to construct the SQL query. First, identify the relevant tables: properties, leases, and payments. Second, filter payments to include only those within the last 12 months using payment_date and DATE_SUB(CURDATE(), INTERVAL 12 MONTH). Third, aggregate the amount from payments by lease_id to get total rent per lease. Fourth, join leases with the aggregated payments on id and property_id. Fifth, join properties with the result on id to retrieve address. Sixth, group the results by property_id and address, summing the total rent. Finally, order the results in descending order by total rent and limit to the top 3.
Sample answer
SELECT
p.address,
SUM(pay.amount) AS total_collected_rent
FROM
properties p
JOIN
leases l ON p.id = l.property_id
JOIN
payments pay ON l.id = pay.lease_id
WHERE
pay.payment_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY
p.id, p.address
ORDER BY
total_collected_rent DESC
LIMIT 3;
This query first joins properties, leases, and payments tables to link payment records to their respective properties. It then filters payments to include only those made within the last 12 months from the current date (CURDATE()). The SUM(pay.amount) function calculates the total collected rent for each property. Results are grouped by property_id and address to ensure accurate aggregation per property. Finally, the results are ordered in descending order by total_collected_rent and limited to the top 3 entries, providing the addresses and their corresponding total rent.
Key points to mention
- • Joining `properties`, `leases`, and `payments` tables correctly.
- • Filtering payments within the last 12 months using `DATE('now', '-12 months')` (or equivalent for specific SQL dialects like `GETDATE()` or `CURRENT_DATE`).
- • Aggregating `amount` using `SUM()` and grouping by `property.address`.
- • Ordering the results in descending order by the total collected rent.
- • Limiting the output to the top 3 properties.
Common mistakes to avoid
- ✗ Incorrectly joining tables, leading to missing data or Cartesian products.
- ✗ Using an inappropriate date function for the specific SQL dialect (e.g., `NOW()` vs. `GETDATE()` vs. `CURRENT_DATE`).
- ✗ Forgetting to `GROUP BY` the property address when using an aggregate function.
- ✗ Not ordering the results correctly before applying the `LIMIT` clause.
- ✗ Selecting `property_id` instead of `address` as requested.