CREATE OR REPLACE VIEW public.local_community_remote_subscribers AS
SELECT community.name AS "community.name",
community.actor_id AS "community.actor_id",
person.name AS "person.name",
person.display_name AS "person.display_name",
person.actor_id AS "person.actor_id",
person.last_refreshed_at AS "person.last_refreshed_at",
person.bot_account AS "person.bot_account"
FROM ((community
JOIN community_follower ON ((community_follower.community_id = community.id)))
JOIN person ON ((community_follower.person_id = person.id)))
WHERE ((community.instance_id = 1) AND (person.instance_id <> 1));
This will create a view within the Lemmy database, for reusability. If you just want to run a one-off query or prefer to store it in other form, omit the first line.