# 部屋別メッセージ数の集計のためのインデクス
create index x_events_message_speed on events(room_id,origin_server_ts) 
where type='m.room.message';

# 部屋別メッセージ数を集計するビュー
CREATE OR REPLACE VIEW room_speed as 
 SELECT events.room_id,
    count(*) AS speed
   FROM events
  WHERE events.type = 'm.room.message'::text AND events.origin_server_ts::double precision >= (date_part('epoch'::text, CURRENT_TIMESTAMP) * 1000::double precision - 86400000::double precision)
  GROUP BY events.room_id;

# 集計して部屋の公開エイリアスと結合
select speed,canonical_alias from room_speed 
left join room_stats_state on room_stats_state.room_id = room_speed.room_id 
where speed>0 order by speed desc;