# 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;
```