Queries to export data

Export emails of all admins

All users that are admin of at least one collective hosted by allforclimate (Host Id: 73495)

SELECT max(uc.slug) as profile, u.email, STRING_AGG(c.slug, ', ') as collectives
  FROM "Members" m
  LEFT JOIN "Collectives" uc ON uc.id = m."MemberCollectiveId"
  LEFT JOIN "Collectives" c ON c.id = m."CollectiveId"
  LEFT JOIN "Users" u ON u."CollectiveId" = m."MemberCollectiveId"
WHERE m.role='ADMIN'
  AND m."CollectiveId" IN (SELECT "CollectiveId" FROM "Members" WHERE role = 'HOST' AND "MemberCollectiveId"='73495')
  AND u.email IS NOT NULL
  AND c."isActive" IS TRUE
  AND c. "deletedAt" IS NULL
GROUP BY email

Export all twitter handles

Twitter handles of collectives hosted by All for Climate

SELECT 
  CONCAT('https://opencollective.com/', MAX(c.slug)) as "collective url", 
  CONCAT('https://twitter.com/', c."twitterHandle") as "twitter url",
  MAX(c.description) as "collective description"
FROM "Collectives" c
WHERE type='COLLECTIVE'
  AND "HostCollectiveId" = 73495
  AND c."twitterHandle" IS NOT NULL
  AND c."isActive" IS TRUE
   AND c. "deletedAt" IS NULL
GROUP BY c."twitterHandle"

Twitter handles of all admins of collectives hosted by All for Climate

SELECT 
  MAX(uc.name) as name, 
  CONCAT('https://opencollective.com/', max(uc."slug")) as "opencollective profile", 
  CONCAT('https://twitter.com/', uc."twitterHandle") as "twitter url", 
  max(m.role) as role, 
  STRING_AGG(c.slug, ', ') as collectives
FROM "Members" m
LEFT JOIN "Collectives" uc ON uc.id = m."MemberCollectiveId"
LEFT JOIN "Collectives" c ON c.id = m."CollectiveId"
WHERE m.role='ADMIN' 
  AND m."CollectiveId" IN (SELECT "CollectiveId" FROM "Members" WHERE role = 'HOST' AND "MemberCollectiveId"=73495)
  AND uc."twitterHandle" IS NOT NULL
  AND c."isActive" IS TRUE
  AND c. "deletedAt" IS NULL
GROUP BY uc."twitterHandle"
``