SELECT r.name, s.room_id, s.current_state_events
    FROM room_stats_current s
    LEFT JOIN room_stats_state r USING (room_id)
    ORDER BY current_state_events DESC
    LIMIT 20;

SELECT rss.name, s.room_id, COUNT(s.room_id)
    FROM state_groups_state s
    LEFT JOIN room_stats_state rss USING (room_id)
    GROUP BY s.room_id, rss.name
    ORDER BY COUNT(s.room_id) DESC
    LIMIT 20;

curl "http://localhost:8008/_synapse/admin/v1/rooms/${ROOMID}" -X DELETE -H 'Accept: application/json' -H 'Referer: http://localhost:8080/' -H "authorization: Bearer ${TOKEN}" --data '{ "purge": true, "message": "Sorry - kicking you out to clean up the database" }'

DELETE FROM
state_groups_state
WHERE
room_id IN (
    SELECT
    DISTINCT(state_groups.room_id) AS room_id_gone
    FROM
    state_groups
    LEFT JOIN events USING(room_id)
    WHERE
    events.room_id IS NULL
    GROUP BY
    room_id_gone
);

DELETE FROM
state_groups
WHERE
room_id IN (
    SELECT
    DISTINCT(state_groups.room_id) AS room_id_gone
    FROM
    state_groups
    LEFT JOIN events USING(room_id)
    WHERE
    events.room_id IS NULL
    GROUP BY
    room_id_gone
);