find_inactive_rooms.sql

CREATE TEMP TABLE old_rooms AS
select distinct e_create.room_id
from events e_create 
where e_create.type='m.room.create' and 
e_create.origin_server_ts < [INSERT TIMESTAMP HERE]; # filter rooms created before timestamp, newer rooms not affected


CREATE TEMP TABLE old_rooms_new_events AS
select * 
from events e_types 
where exists(select * from old_rooms where old_rooms.room_id = e_types.room_id) and 
	e_types.origin_server_ts > [INSERT TIMESTAMP HERE]; # filter events after this timestamp to see if there are any.


select distinct room_id from events final_e where exists(select * from old_rooms where old_rooms.room_id=final_e.room_id) and not exists(select * from old_rooms_new_events where old_rooms_new_events.room_id=final_e.room_id);