This query gets total units, occupied units, rent roll, move ins, move outs, transfers (to and from) by unit type and year-month.
Written by: Tam Meuwissen
Written on: September 2, 2026
with transfers as
(
select distinct 'transfer' as event_type
, a.rental_id as transfer_to_rental_id
, b.rental_id as transfer_from_rental_id
, a.unit_id
, a.tenant_id
, a.facility_id
, a.event_date
, date_trunc('month', a.event_date)::date as year_month
, b.unit_type as transfer_from_unit_type
, b.unit_name as transfer_from_unit_name
, a.unit_type as transfer_to_unit_type
, a.unit_name as transfer_to_unit_name
from
(
select distinct [u.id](<http://u.id/>) as unit_id_join
, u.facility_id
, u.type as unit_type
, [u.name](<http://u.name/>) as unit_name
, [r.id](<http://r.id/>) as rental_id
, r.unit_id
, r.tenant_id
, r.start_time::date as event_date
from rentals r
join units u
on r.unit_id = [u.id](<http://u.id/>)
) a
join
(select distinct [u.id](<http://u.id/>) as unit_id_join
, u.facility_id
, u.type as unit_type
, [u.name](<http://u.name/>) as unit_name
, [r.id](<http://r.id/>) as rental_id
, r.unit_id
, r.tenant_id
, r.end_time::date as event_date
from rentals r
join units u
on r.unit_id = [u.id](<http://u.id/>)
) b
on a.event_date = b.event_date
and a.tenant_id = b.tenant_id
and a.unit_name <> b.unit_name
join unit_metrics um
on a.unit_id_join = um.unit_id
and um.time::date = a.event_date
where a.event_date::date >= '2026-01-01'
and a.facility_id = '313be220-508c-42f7-a2b3-a7d5c7a5dee0' --Lawtone SE Park Ave
)
--------------------------------------------------------------------------
/* Move-Ins and Move-Outs (joined to unit_metrics via [units.id](<http://units.id/>), matched on date) /
--------------------------------------------------------------------------
, move_ins as
(
select 'Move In' as event_type
, [r.id](<http://r.id/>) as rental_id
, r.unit_id
, r.tenant_id
, u.facility_id
, u.type as unit_type
, [u.name](<http://u.name/>) as unit_name
, u.amenities as unit_amenities
, r.start_time::date as event_date
, date_trunc('month', r.start_time::date)::date as year_month
from rentals r
join units u
on r.unit_id = [u.id](<http://u.id/>)
join unit_metrics um
on [u.id](<http://u.id/>) = um.unit_id
and um.time::date = r.start_time::date
where start_time::date >= '2026-01-01'
and u.facility_id = '313be220-508c-42f7-a2b3-a7d5c7a5dee0' --Lawtone SE Park Ave
)
, move_outs as
(
select 'Move Out' as event_type
, [r.id](<http://r.id/>) as rental_id
, r.unit_id
, r.tenant_id
, u.facility_id
, u.type as unit_type
, [u.name](<http://u.name/>) as unit_name
, u.amenities as unit_amenities
, end_time::date as event_date
, date_trunc('month', r.end_time::date)::date as year_month
from rentals r
join units u
on r.unit_id = [u.id](<http://u.id/>)
join unit_metrics um
on [u.id](<http://u.id/>) = um.unit_id
and um.time::date = r.end_time::date
where end_time::date >= '2026-01-01'
and u.facility_id = '313be220-508c-42f7-a2b3-a7d5c7a5dee0' --Lawtone SE Park Ave
)
--------------------------------------------------------------------------
/ Filter out move-ins/move-outs that are actually transfers /
--------------------------------------------------------------------------
, move_ins_filtered as
(
select mi.
from move_ins mi
where not exists (
select 1
from transfers t
where t.transfer_to_rental_id = mi.rental_id
)
)
, move_outs_filtered as
(
select mo.*
from move_outs mo
where not exists (
select 1
from transfers t
where t.transfer_from_rental_id = mo.rental_id
)
)
--------------------------------------------------------------------------
/* Event counts per facility_id / unit_type / year_month /
--------------------------------------------------------------------------
, move_in_counts as
(
select facility_id, unit_type, year_month, count() as move_in_count
from move_ins_filtered
group by facility_id, unit_type, year_month
)
, move_out_counts as
(
select facility_id, unit_type, year_month, count() as move_out_count
from move_outs_filtered
group by facility_id, unit_type, year_month
)
, transfer_in_counts as
(
select facility_id, transfer_to_unit_type as unit_type, year_month, count() as transfer_to_count
from transfers
group by facility_id, transfer_to_unit_type, year_month
)
, transfer_out_counts as
(
select facility_id, transfer_from_unit_type as unit_type, year_month, count() as transfer_from_count
from transfers
group by facility_id, transfer_from_unit_type, year_month
)
--------------------------------------------------------------------------
/ Month-end snapshot metrics: total units, occupied units, rent roll /
--------------------------------------------------------------------------
, month_end_dates as
(
select date_trunc('month', um.time::date)::date as year_month
, max(um.time::date) as month_end_date
from unit_metrics um
join units u
on um.unit_id = [u.id](<http://u.id/>)
where u.facility_id = '313be220-508c-42f7-a2b3-a7d5c7a5dee0' --Lawtone SE Park Ave
group by date_trunc('month', um.time::date)::date
)
, unit_metrics_monthly as
(
select u.facility_id
, u.type as unit_type
, med.year_month
, count([um.id](<http://um.id/>)) as total_units
, sum(um.rented) as occupied_units
, sum(um.current_rate) as rent_roll
from unit_metrics um
join units u
on um.unit_id = [u.id](<http://u.id/>)
join month_end_dates med
on um.time::date = med.month_end_date
where u.facility_id = '313be220-508c-42f7-a2b3-a7d5c7a5dee0' --Lawtone SE Park Ave
group by u.facility_id, u.type, med.year_month
)
--------------------------------------------------------------------------
/ Build the full list of facility_id / unit_type / year_month combos /
--------------------------------------------------------------------------
, all_keys as
(
select facility_id, unit_type, year_month from move_in_counts
union
select facility_id, unit_type, year_month from move_out_counts
union
select facility_id, unit_type, year_month from transfer_in_counts
union
select facility_id, unit_type, year_month from transfer_out_counts
union
select facility_id, unit_type, year_month from unit_metrics_monthly
)
--------------------------------------------------------------------------
/ Final result */
--------------------------------------------------------------------------
select
k.facility_id
, [f.name](<http://f.name/>) as facility_name
, k.unit_type
, k.year_month
, coalesce(mi.move_in_count, 0) as move_in_count
, coalesce(mo.move_out_count, 0) as move_out_count
, coalesce(ti.transfer_to_count, 0) as transfer_to_count
, coalesce(to_.transfer_from_count, 0) as transfer_from_count
, um_m.total_units
, um_m.occupied_units
, um_m.rent_roll
from all_keys k
left join facilities f
on k.facility_id = [f.id](<http://f.id/>)
left join move_in_counts mi
on k.facility_id = mi.facility_id and k.unit_type = mi.unit_type and k.year_month = mi.year_month
left join move_out_counts mo
on k.facility_id = mo.facility_id and k.unit_type = mo.unit_type and k.year_month = mo.year_month
left join transfer_in_counts ti
on k.facility_id = ti.facility_id and k.unit_type = ti.unit_type and k.year_month = ti.year_month
left join transfer_out_counts to_
on k.facility_id = to_.facility_id and k.unit_type = to_.unit_type and k.year_month = to_.year_month
left join unit_metrics_monthly um_m
on k.facility_id = um_m.facility_id and k.unit_type = um_m.unit_type and k.year_month = um_m.year_month
where k.year_month >= '2026-01-01'
order by k.facility_id, k.unit_type, k.year_month--select * from rentals limit 100
--select * from units limit 100
--------------------------------------------------------------------------
/* Transfer Units */
--------------------------------------------------------------------------
select distinct a.rental_id
, a.unit_id
, a.tenant_id
, a.facility_id
, a.start_time as transfer_date
, b.unit_type as transfer_from_unit_type
, b.unit_name as transfer_from_unit_name
, a.unit_type as transfer_to_unit_type
, a.unit_name as transfer_to_unit_name
from
(
select distinct u.facility_id
, u.type as unit_type
, u.name as unit_name
, r.id as rental_id
, r.unit_id
, r.tenant_id
, r.start_time::date
from rentals r
join units u
on r.unit_id = u.id
) a
join
(select distinct u.facility_id
, u.type as unit_type
, u.name as unit_name
, r.id as rental_id
, r.unit_id
, r.tenant_id
, r.end_time::date
from rentals r
join units u
on r.unit_id = u.id
) b
on a.start_time::date = b.end_time::date
and a.tenant_id = b.tenant_id
and a.unit_name <> b.unit_name
where start_time::date >= '2026-01-01'
and a.facility_id = '313be220-508c-42f7-a2b3-a7d5c7a5dee0' --Lawtone SE Park Ave
order by start_time::date
--------------------------------------------------------------------------
/* Move-Ins and Move-Outs */
--------------------------------------------------------------------------
--move ins
select r.id as rental_id
, r.unit_id
, r.tenant_id
, u.facility_id
, u.name as unit_name
, u.amenities as unit_amenities
, r.start_time::date as start_year_month
from rentals r
join units u
on r.unit_id = u.id
where start_time::date >= '2026-01-01'
and u.facility_id = '313be220-508c-42f7-a2b3-a7d5c7a5dee0' --Lawtone SE Park Ave
order by start_time::date
--move outs
select r.id as rental_id
, r.unit_id
, r.tenant_id
, u.facility_id
, u.name as unit_name
, u.amenities as unit_amenities
, end_time::date as end_year_month
from rentals r
join units u
on r.unit_id = u.id
where end_time::date >= '2026-01-01'
and u.facility_id = '313be220-508c-42f7-a2b3-a7d5c7a5dee0' --Lawtone SE Park Ave
order by end_time::date