# Collectives

## Stats

### Top backers

```
SELECT max(c.name) as backer, concat('https://opencollective.com/',max(c.slug)) as "backer profile", 
  sum(t."amount")/ 100 as "totalAmount", max(t.currency) as currency
FROM "Transactions" t
LEFT JOIN "Collectives" c ON c.id = t."FromCollectiveId"
WHERE t."CollectiveId"=19965
  AND t.type='CREDIT'
  AND t."deletedAt" IS NULL
GROUP BY t."FromCollectiveId"
ORDER BY "totalAmount" DESC
```

### Revenue breakdown per tier

```
SELECT max(t.name) as ticket, sum(o.quantity) as count,
  sum("totalAmount") / 100 as "totalAmount", max(o.currency) as currency
FROM "Orders" o
LEFT JOIN "Tiers" t ON t.id = o."TierId"
WHERE o."CollectiveId"=19965
  AND o.status='PAID'
  AND o."deletedAt" IS NULL
GROUP BY o."TierId"
```

### Breakdown of expenses

```
WITH expenses AS (
  SELECT max(category) as category, max(description) as description,
    sum(amount) as amount, max(currency) as currency
  FROM "Expenses"
  WHERE "CollectiveId"=19965
  GROUP BY description
  ORDER BY amount DESC
)
SELECT max(category) as category, count(*) as count,
  sum(amount) / 100 as "totalAmount", max(e.currency) as currency,
  string_agg(concat(e.description, ' (', e.currency, ' ',e.amount / 100,')'), ', ') as "expenses"
FROM "expenses" e 
GROUP BY category
ORDER BY "totalAmount" DESC
```

List of backers with first and last donation

```
SELECT o."createdAt", MAX(s."updatedAt") as "updateAt", bool_and(s."isActive") as "isActive", MAX(t."createdAt") as "lastTransactionAt", MAX(backer.slug) as "profile", o."totalAmount" as "orderAmount", SUM(t.amount) as "totalAmountInCents"
FROM "Orders" o
LEFT JOIN "Subscriptions" s ON s.id = o."SubscriptionId"
LEFT JOIN "Collectives" c on c.id = o."CollectiveId"
LEFT JOIN "Collectives" "backer" on backer.id = o."FromCollectiveId"
LEFT JOIN "Transactions" t ON t."OrderId" = o.id
WHERE c.slug='babel' AND t.type='CREDIT'
GROUP BY o.id
ORDER BY "totalAmountInCents" DESC
```

Number of donations and total donations per month per collective

```
SELECT 
  substring(cast(DATE_TRUNC('month', t."createdAt") as text) from 1 for 7) as month,
  MAX(g.slug) as collective,
  count(t.*) as "totalDonations", 
  SUM(t.amount)/100 as "totalDonationsAmount",
  (SUM(t.amount)/100)/count(t.*) as "avgDonation",
  MAX(g.currency) as "currency"

FROM "Transactions" t LEFT JOIN "Collectives" g ON t."CollectiveId" = g.id 
WHERE t.type='CREDIT' AND t."deletedAt" is NULL AND slug = 'webpack' AND t."PaymentMethodId" IS NOT NULL
GROUP BY month
ORDER BY month ASC
```

## Export

### Export all backers of a collective with private data (user.email)

```
SELECT mc."createdAt", m.role, u.email, mc.name, mc.description, mc.website, mc."twitterHandle"
FROM "Collectives" c
  LEFT JOIN "Members" m ON m."CollectiveId" = c.id 
  LEFT JOIN "Collectives" mc ON m."MemberCollectiveId" = mc.id 
  LEFT JOIN "Users" u ON u."CollectiveId" = mc.id 
WHERE c.slug='becoworking' AND m.role != 'HOST'
```

### New collectives for the past XX and creator details

```
select 
    c.id as "collectiveId", 
    slug, 
    c."createdAt" as "collectiveCreatedAt", 
    "HostCollectiveId", 
    tags,
    u."firstName" as firstname,
    u."lastName" as lastname,
    u.email as email
from "Collectives" as c
left join "Users" u on c."CreatedByUserId" = u.id
where 
    type ilike 'collective' 
    and c."deletedAt" is null
    and u."deletedAt" is null 
    and c."createdAt" > '2017-11-15';
```

### Expenses

Note: this export includes private data (user.email, expense.attachment)

```
SELECT 
  e."incurredAt", 
  e.status, 
  e.amount, 
  e.currency, 
  e.category, 
  e.description, 
  e."privateMessage", 
  e.attachment,
  u."firstName", 
  u."lastName", 
  u.email
FROM "Expenses" e 
LEFT JOIN "Users" u on e."UserId" = u.id
WHERE e."CollectiveId" = 302
```

## Administration

### Cancel all active subscriptions

Need to update `CollectiveId` (here 967) and `deactivatedAt`

```
BEGIN;

with subscriptions as (
  SELECT "SubscriptionId" as id
  FROM "Orders"
  WHERE "CollectiveId"=967 AND status='ACTIVE'
)
UPDATE "Subscriptions"
SET "isActive" = false, "deactivatedAt" = '2018-10-27'
WHERE id IN (select id from "subscriptions");

UPDATE "Orders" SET status = 'CANCELLED'
WHERE status='ACTIVE' AND "CollectiveId" = 967;

COMMIT;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://documentarians.gitbook.io/metadocumentation/internal/queries/collectives.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.