Stats per collective

Recorded in this spreadsheet

Total credit/debit and balance

SELECT concat('https://opencollective.com/', MAX(c.slug)) as collective, 
  to_char(date_trunc('week', max(c."createdAt")), 'YYYY-mm-dd') as "createdAt",
  count(distinct t."FromCollectiveId") as "backers",
  SUM(CASE WHEN t.type = 'CREDIT' THEN amount ELSE 0 END) / 100 as "total credit",
  SUM(CASE WHEN t.type = 'DEBIT' THEN amount ELSE 0 END) / 100 as "total debit",
  SUM("amount") / 100 as "balance"
FROM "Transactions" t LEFT JOIN "Collectives" c ON c.id=t."CollectiveId"
WHERE c."HostCollectiveId" = 73495
--  AND t."createdAt" >= '2019-01-01'
--  AND t."createdAt" < '2024-01-01'
  AND t."deletedAt" IS NULL
GROUP BY c.slug
ORDER by "total credit" DESC

Contribution to the fiscal host per collective

SELECT 
  concat('https://opencollective.com/', MAX(c.slug)) as collective,
  SUM(t.amount) / 100 as "host fees"
FROM "Transactions" t 
LEFT JOIN "Collectives" c ON t."FromCollectiveId" = c.id
WHERE t."HostCollectiveId" = 73495
  AND t."CollectiveId" = 73495
--  AND t."createdAt" > '2023-01-01' 
  AND t."type" = 'CREDIT'
GROUP BY "FromCollectiveId"
ORDER BY "host fees" DESC