Queries for Collectives


#1

Stats

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 "Groups" g ON t."GroupId" = g.id 
WHERE type='DONATION' AND t."deletedAt" is NULL AND slug = 'preact' AND t."PaymentMethodId" IS NOT NULL
GROUP BY month
ORDER BY month ASC

Export

Expenses

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

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

Get the list of RSVPs for an event

SELECT 
  r."createdAt", r.quantity, r.status, r.description, 
  u."firstName", u."lastName", u.email, u."twitterHandle", u.website, u.avatar, u.description 
FROM 
  "Responses" r 
LEFT JOIN "Users" u ON u.id=r."UserId" 
LEFT JOIN "Events" e ON e.id = r."EventId" 
WHERE e.slug = 'benelux'