SELECTto_char(date_trunc('month', t."createdAt"), 'YYYY-mm') as "month",COUNT(DISTINCT(t."HostCollectiveId")) as "activeHosts",COUNT(DISTINCT(t."CollectiveId")) as "activeCollectives",COUNT(DISTINCT(t."FromCollectiveId")) as "activeBackers"FROM "Transactions" t​LEFT JOIN "Collectives" c On (t."CollectiveId" = c.id AND c.type LIKE 'COLLECTIVE')​WHERE t."deletedAt" IS NULL AND ((t."OrderId" IS NOT NULL AND t.type LIKE 'CREDIT')OR (t."ExpenseId" IS NOT NULL AND t.type LIKE 'DEBIT')) ANDt."createdAt" BETWEEN '2016/01/01' AND '2019/01/01'GROUP BY "month" ORDER BY "month"
per year with total donations and platform fees per currency (USD and EUR):
SELECTto_char(date_trunc('year', t."createdAt"), 'YYYY') as "year",COUNT(DISTINCT(t."HostCollectiveId")) as "activeHosts",COUNT(DISTINCT(t."CollectiveId")) as "activeCollectives",COUNT(DISTINCT(t."FromCollectiveId")) as "activeBackers",SUM(CASE WHEN t."OrderId" IS NOT NULL AND t.type='CREDIT' AND t.currency = 'USD' THEN t.amount / 100END) as "totalDonations (USD)",SUM(CASE WHEN t."OrderId" IS NOT NULL AND t.type='CREDIT' AND t.currency = 'EUR' THEN t.amount / 100END) as "totalDonations (EUR)",SUM(CASE WHEN t."OrderId" IS NOT NULL AND t.type='CREDIT' AND t.currency = 'USD' THEN -t."platformFeeInHostCurrency" / 100END) as "Platform fee (USD)",SUM(CASE WHEN t."OrderId" IS NOT NULL AND t.type='CREDIT' AND t.currency = 'EUR' THEN -t."platformFeeInHostCurrency" / 100END) as "Platform fee (EUR)"FROM "Transactions" t​LEFT JOIN "Collectives" c On (t."CollectiveId" = c.id AND c.type LIKE 'COLLECTIVE')​WHERE t."deletedAt" IS NULLAND ((t."OrderId" IS NOT NULL AND t.type LIKE 'CREDIT')OR (t."ExpenseId" IS NOT NULL AND t.type LIKE 'DEBIT'))GROUP BY "year" ORDER BY "year"
selectdate_trunc('month', t."createdAt") as "month",count(distinct("FromCollectiveId"))from "Transactions" t​where "deletedAt" is nulland "FromCollectiveId" != "HostCollectiveId"and type ilike 'credit'GROUP BY "month" ORDER BY "month"
selectcount(distinct("FromCollectiveId"))​from "Transactions" t​where "deletedAt" is null and "FromCollectiveId" != "HostCollectiveId" and type ilike 'credit' and "createdAt" < '2017-11-01 00:00:00.00-00'
For all the collectives created in a given month, how many of them are still active after 3 months?
WITH "collectivesCreated" as (SELECTc."createdAt" as "createdAt",c.slug,min(t."createdAt") as "firstTransaction",max(t."createdAt") as "latestTransaction",EXTRACT(day from (min(t."createdAt") - c."createdAt")) as "daysToFirstTransaction",EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) as "activeDays",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 7 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 7 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active7",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 14 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 14 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active14",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 30 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 30 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active30",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 60 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 60 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active60",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 90 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 90 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active90",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 180 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 180 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active180",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 365 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 365 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active365"​FROM "Collectives" cLEFT JOIN "Transactions" t ON (c.id=t."CollectiveId" and t."createdAt" < '2018-02-01')WHERE c.type ilike 'collective'GROUP BY c.id)​SELECTto_char("createdAt", 'YYYY-mm') as "month",count(*) as "totalCollectivesCreated",SUM(active7) as "active7days",SUM(active14) as "active14days",SUM(active30) as "active30days",SUM(active60) as "active60days",SUM(active90) as "active90days",SUM(active180) as "active180days",SUM(active365) as "active365days",ROUND((SUM(active7)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent7days",ROUND((SUM(active14)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent14days",ROUND((SUM(active30)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent30days",ROUND((SUM(active60)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent60days",ROUND((SUM(active90)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent90days",ROUND((SUM(active180)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent180days",ROUND((SUM(active365)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent365days"​FROM "collectivesCreated"GROUP BY month ORDER BY month NULLS FIRST
For all the collectives created in a given month, how many of them are still active after 3 months?
WITH "collectivesCreated" as (SELECTc."createdAt" as "createdAt",c.slug,min(t."createdAt") as "firstTransaction",max(t."createdAt") as "latestTransaction",EXTRACT(day from (min(t."createdAt") - c."createdAt")) as "daysToFirstTransaction",EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) as "activeDays",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 7 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 7 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active7",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 14 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 14 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active14",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 30 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 30 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active30",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 60 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 60 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active60",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 90 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 90 THEN 0WHEN min(t."createdAt") IS NULL THEN 0END as "active90"​FROM "Collectives" cLEFT JOIN "Transactions" t ON (c.id=t."CollectiveId" and t."ExpenseId" is not null and t."createdAt" < '2018-02-01')WHERE c.type ilike 'collective'GROUP BY c.id)​SELECTto_char("createdAt", 'YYYY-mm') as "month",count(*) as "totalCollectivesCreated",SUM(active7) as "active7days",SUM(active14) as "active14days",SUM(active30) as "active30days",SUM(active60) as "active60days",SUM(active90) as "active90days",ROUND((SUM(active7)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent7days",ROUND((SUM(active14)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent14days",ROUND((SUM(active30)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent30days",ROUND((SUM(active60)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent60days",ROUND((SUM(active90)::FLOAT::numeric/count(*)::FLOAT::numeric),3) AS "percent90days"​FROM "collectivesCreated"GROUP BY month ORDER BY month NULLS FIRST
For all the monthly subscriptions that started in a given month, how many are still active after 3 months?
WITH "activeSubscriptions" as (SELECTs."createdAt" as "createdAt",min(t."createdAt") as "firstTransaction",max(t."createdAt") as "latestTransaction",EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) as "activeDays",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 90 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 90 THEN 0END as "active90",​CASEWHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 60 THEN 1WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 60 THEN 0END as "active60",​CASEWHEN (max(fc.type) ilike 'user') THEN 1ELSE 0END as "isUser",​CASEWHEN (max(fc.type) ilike 'organization') THEN 1ELSE 0END as "isOrg"​FROM "Transactions" t​LEFT JOIN "Orders" d ON (d.id=t."OrderId" and t."createdAt" < '2018-03-01')LEFT JOIN "Subscriptions" s ON s.id=d."SubscriptionId"LEFT JOIN "Collectives" fc on fc.id = d."FromCollectiveId"​WHERE s.interval = 'month'GROUP BY s.id)​SELECTto_char("createdAt", 'YYYY-mm') as "month",/* total subscription counts */count(*) as "totalSubscriptionsCreated",SUM("isUser") as "totalSubscriptionsCreatedByUsers",SUM("isOrg") as "totalSubscriptionsCreatedByOrgs",​/* 60-day subscription calcs */SUM(active60) as "totalActiveAfter60days",SUM(active60 * "isUser") as "totalActiveByUsersAfter60days",SUM(active60 * "isOrg") as "totalActiveByOrgsAfter60days",​/* 90-day subscription calcs */SUM(active90) as "totalActiveAfter90days",SUM(active90 * "isUser") as "totalActiveByUsersAfter90days",SUM(active90 * "isOrg") as "totalActiveByOrgsAfter90days"FROM "activeSubscriptions" GROUP BY month ORDER BY month
with subs as (select s.id, s."activatedAt", s."deactivatedAt" from "Orders" o​left join "Subscriptions" s on s.id = o."SubscriptionId"​where o."processedAt" is not null and o."SubscriptionId" is not null and o."deletedAt" is null and s."deletedAt" is null and s."activatedAt" is not null)​selectto_char("activatedAt", 'YYYY-mm') as "month", /* change to "deactivatedAt" to get deactivated count */count(*) as "subsCount"​from subs​Group by "month" order by "month"
SELECT sum(amount) / 100 as "totalAmount", max(t.currency) as currency, max(c.slug) as collective, max(c.website) as website, max(c."twitterHandle") as twitter, max(c.description) as descriptionFROM "Transactions" tLEFT JOIN "Collectives" c ON c.id = t."CollectiveId"WHERE t."createdAt" > '2018-03-01'AND t."createdAt" < '2018-04-01'AND t.type='CREDIT'AND t."platformFeeInHostCurrency" < 0GROUP BY t."CollectiveId"ORDER BY "totalAmount" DESC
SELECT sum(amount) / 100 as "totalAmount", max(t.currency) as currency, max(c.slug) as collective, max(c.website) as website, max(c."twitterHandle") as twitter, max(c.description) as descriptionFROM "Transactions" tLEFT JOIN "Collectives" c ON c.id = t."CollectiveId"WHERE t."createdAt" > '2018-03-01'AND c."createdAt" > '2018-03-01'AND t."createdAt" < '2018-04-01'AND c."createdAt" < '2018-04-01'AND t.type='CREDIT'AND t."platformFeeInHostCurrency" < 0GROUP BY t."CollectiveId"ORDER BY "totalAmount" DESC
SELECT max(c.slug) as collective, max(c."createdAt") as "createdAt", count(*) as "totalNewBackers", max(c.website) as website, max(c."twitterHandle") as twitter, max(c.description) as descriptionFROM "Members" mLEFT JOIN "Collectives" c ON m."CollectiveId" = c.idWHERE m."createdAt" > '2018-03-01'AND m."createdAt" < '2018-04-01'AND m.role='BACKER'GROUP BY "CollectiveId"ORDER BY "totalNewBackers" DESC
with res as (SELECT CONCAT('https://opencollective.com/', max(backer.slug)) as backer, sum(amount) / 100 as "amount", max(t.currency) as currency, string_agg(DISTINCT c.slug, ', ') AS "collectives supported", max(backer."twitterHandle") as twitter, max(backer.description) as description, max(backer.website) as websiteFROM "Transactions" tLEFT JOIN "Collectives" backer ON backer.id = t."FromCollectiveId"LEFT JOIN "Collectives" c ON c.id = t."CollectiveId"WHERE t."createdAt" > '2018-05-01'AND t."createdAt" < '2018-06-01'AND t.type='CREDIT'AND t."platformFeeInHostCurrency" < 0AND t."deletedAt" IS NULLGROUP BY t."FromCollectiveId"ORDER BY "amount" DESC)SELECT row_number() over(order by "amount" DESC) as "#", * from res
selectto_char(t."createdAt", 'YYYY-mm') as "month",sum(t.amount)/100 as amount,max(t.currency) as currency,c.slug as "fromCollective"​from "Transactions" tinner join "Collectives" c on c.id = t."FromCollectiveId"​whereamount > 0 and"CollectiveId" != 1 andt."platformFeeInHostCurrency" < 0 andt."deletedAt" IS NULL and((t."OrderId" IS NOT NULL AND t.type LIKE 'CREDIT')OR (t."ExpenseId" IS NOT NULL AND t.type LIKE 'DEBIT'))AND c.type ilike 'organization'AND ((t."RefundTransactionId" IS NOT NULL ANDt."data"->'refund' IS NULL ANDt.type = 'CREDIT') OR t."RefundTransactionId" IS NULL)​group by c.slug, "month"order by c.slug
with old as (SELECTto_char(date_trunc('month', t."createdAt"), 'YYYY-mm') as "month",count(c.id) as "count",min(t."createdAt") as "firstTransaction",SUM("amount") / 100 as "totalAmount",t.currencyFROM "Transactions" tLEFT JOIN "Collectives" c ON c.id = t."FromCollectiveId"WHEREt."createdAt" >= '2018-01-01'AND t."deletedAt" IS NULLAND t.type='CREDIT'AND c.type='ORGANIZATION'AND c."createdAt" < date_trunc('month', t."createdAt")::DATE -- organizations created before the first day of the monthGROUP BY t.currency, month),new as (SELECTto_char(date_trunc('month', t."createdAt"), 'YYYY-mm') as "month", count(c.id) as "count",SUM("amount") / 100 as "totalAmount",t.currencyFROM "Transactions" tLEFT JOIN "Collectives" c ON c.id = t."FromCollectiveId"WHEREt."createdAt" >= '2018-01-01'AND t."deletedAt" IS NULLAND t.type='CREDIT'AND c.type='ORGANIZATION'AND c."createdAt" >= date_trunc('month', t."createdAt")::DATE -- organizations created after the first day of the monthGROUP BY t.currency, month)​SELECTold.month, old.currency,new.count as "# new orgs", new."totalAmount",old.count as "# old orgs", old."totalAmount",to_char(100.0 * new."totalAmount" /(old."totalAmount"+new."totalAmount")::float,'999D99%') as "% of new orgs"FROM oldLEFT JOIN new ON old.currency = new.currency AND old.month = new.monthORDER BY month DESC
Note: you can limit this view to one single currency by adding WHERE old.currency = 'USD'
:
FROM oldLEFT JOIN new ...WHERE old.currency = 'USD'ORDER BY month DESC
SELECT slug, coalesce(sum(t.amount), 0)/100 as "amountDonated" from "Collectives" c inner join "Transactions" t on c.id = t."FromCollectiveId"where c."createdAt" >= '2018-05-1' and c."createdAt" < '2018-06-1'and c.type = 'ORGANIZATION' AND t.type = 'CREDIT' group by c.slughaving coalesce(sum(t.amount), 0) > 100;
selectto_char(t."createdAt", 'YYYY-mm') as "month",sum(t.amount)/100 as amount,max(t.currency) as currency,c.slug as "collective"​from "Transactions" tinner join "Collectives" c on c.id = t."CollectiveId"​whereamount > 0 and"CollectiveId" != 1 andt."platformFeeInHostCurrency" < 0 andt."deletedAt" IS NULL and((t."OrderId" IS NOT NULL AND t.type LIKE 'CREDIT')OR (t."ExpenseId" IS NOT NULL AND t.type LIKE 'DEBIT'))​​group by c.slug, "month"order by c.slug
SELECTto_char(date_trunc('week', c."createdAt"), 'YYYY-mm-dd') as "month",count(*) filter (where tags::text ilike '%open source%') as "newOpenSourceCollectives",count(*) filter (where tags::text ilike '%tech meetup%') as "newTechMeetups",count(*) filter (where ((tags::text not ilike '%open source%' AND tags::text not ilike '%tech meetup%') OR tags is null)) as "newOtherCollectives",COUNT(*) as "totalNewCollectives"FROM "Collectives" c​WHERE c.type ilike 'collective' and "createdAt" is not null and "createdAt" > '2016-01-01'​GROUP BY "month" ORDER BY "month"
-- Revenue and transaction splits by month with all currencies-- converted to USD.---- Note: the exchange rates are from November 8 2018​with conversions as (selectdate_trunc('month', t."createdAt") as "givenMonth",​/* deal with currency */CASEWHEN (t.currency = 'USD') THEN t.amount / 1WHEN (t.currency = 'EUR') THEN t.amount / 0.88WHEN (t.currency = 'MXN') THEN t.amount / 20.09WHEN (t.currency = 'AUD') THEN t.amount / 1.39WHEN (t.currency = 'CAD') THEN t.amount / 1.31WHEN (t.currency = 'INR') THEN t.amount / 73.02WHEN (t.currency = 'SEK') THEN t.amount / 9.07WHEN (t.currency = 'GBP') THEN t.amount / 0.77ELSE 0END AS "amountInUSD",CASEWHEN (t.currency = 'USD') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 1WHEN (t.currency = 'EUR') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 0.88WHEN (t.currency = 'MXN') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 20.09WHEN (t.currency = 'AUD') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 1.39WHEN (t.currency = 'CAD') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 1.31WHEN (t.currency = 'INR') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 73.02WHEN (t.currency = 'SEK') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 9.07WHEN (t.currency = 'GBP') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 0.77ELSE 0END AS "platformFeeInUSD",​/*Generate donations categories- added-funds (manually added funds - we didn't get a platform fee)// for rest of these we charge a fee- recurringMonthlyNew (new monthly subscription in this month)- recurringMonthlyOld (carryover monthly subscription in this month)- recurringAnnualNew (new annual subscription this month)- recurringAnnualOld (carryover annual subscription renewed this month)- one-time (one-time donations)*/​CASEWHENt.amount > 0 AND t."OrderId" IS NOT NULL AND(t."platformFeeInHostCurrency" = 0 OR t."platformFeeInHostCurrency" IS NULL)THEN 1ELSE 0END AS addedFunds,​​CASEWHEN t.amount > 0 ANDd."SubscriptionId" is NULL AND(t."platformFeeInHostCurrency" is not null AND t."platformFeeInHostCurrency" != 0)THEN 1ELSE 0END AS oneTimeDonations,​CASEWHENt.amount > 0 AND(t."platformFeeInHostCurrency" IS NOT NULL AND t."platformFeeInHostCurrency" != 0) ANDd."SubscriptionId" is NOT NULL AND s."interval" like 'month%'THEN 1ELSE 0END AS recurringMonthlyTotal,​CASEWHENt.amount > 0 AND t."OrderId" IS NOT NULL AND(t."platformFeeInHostCurrency" IS NOT NULL AND t."platformFeeInHostCurrency" != 0) ANDd."SubscriptionId" is NOT NULL AND s."interval" like 'month%' ANDdate_trunc('month', t."createdAt") = date_trunc('month', s."activatedAt")THEN 1ELSE 0END AS recurringMonthlyNew,​CASEWHENt.amount > 0 AND(t."platformFeeInHostCurrency" IS NOT NULL AND t."platformFeeInHostCurrency" != 0) ANDd."SubscriptionId" is NOT NULL AND s."interval" like 'month%' ANDdate_trunc('month', t."createdAt") > date_trunc('month', s."activatedAt")THEN 1ELSE 0END AS recurringMonthlyOld,​CASEWHENt.amount > 0 AND(t."platformFeeInHostCurrency" IS NOT NULL AND t."platformFeeInHostCurrency" != 0) ANDd."SubscriptionId" is NOT NULL AND s."interval" like 'year%'THEN 1ELSE 0END AS recurringAnnuallyTotal,​CASEWHENt.amount > 0 AND(t."platformFeeInHostCurrency" IS NOT NULL AND t."platformFeeInHostCurrency" != 0) ANDd."SubscriptionId" is NOT NULL AND s."interval" like 'year%' ANDdate_trunc('month', t."createdAt") = date_trunc('month', s."activatedAt")THEN 1ELSE 0END AS recurringAnnuallyNew,​CASEWHENt.amount > 0 AND(t."platformFeeInHostCurrency" IS NOT NULL AND t."platformFeeInHostCurrency" != 0) ANDd."SubscriptionId" is NOT NULL AND s."interval" like 'year%' ANDdate_trunc('month', t."createdAt") > date_trunc('month', s."activatedAt")THEN 1ELSE 0END AS recurringAnnuallyOld,​/*Generate expenses categories- total (all expenses recorded)- manual (submitted but no money exchanged from us)- paypal (paid through paypal)*/​CASEWHENt.amount < 0 AND t."ExpenseId" IS NOT NULLTHEN 1ELSE 0END AS totalExpensesRecorded,​CASEWHENt.amount < 0 AND t."ExpenseId" IS NOT NULL ANDt."PaymentMethodId" IS NULLTHEN 1ELSE 0END AS manualExpenses,​CASEWHENt.amount < 0 AND t."ExpenseId" IS NOT NULL ANDt."PaymentMethodId" IS NOT NULLTHEN 1ELSE 0END AS paypalExpenses,​/*Generate user categories- backer- sponsor (org)*/​CASEWHEN (fc.type ilike 'user') THEN 1ELSE 0END as "isUser",​CASEWHEN (fc.type ilike 'organization') THEN 1ELSE 0END as "isOrg",​/** isNotRefund: The transaction isn't either a refund orrefunded. */CASEWHEN (t."RefundTransactionId" IS NULL) THEN 1ELSE 0END as isNotRefund,/** hasBeenRefunded: A refunded transaction represents theoriginal donation from User to Collective */CASEWHEN (t."RefundTransactionId" IS NOT NULL ANDt."data"->'refund' IS NULL ANDt.type = 'CREDIT')THEN 1ELSE 0END as hasBeenRefunded,/** isRefund: A refund is true when the transaction representsmoving funds from Collective to User after a refund. */CASEWHEN (t."RefundTransactionId" IS NOT NULL ANDt."data"->'refund' IS NOT NULL ANDt."type" = 'DEBIT')THEN 1ELSE 0END as isRefund​FROM "Transactions" tLEFT JOIN "Orders" d on t."OrderId" = d.idLEFT JOIN "Subscriptions" s on d."SubscriptionId" = s.idLEFT JOIN "Collectives" fc on t."FromCollectiveId" = fc.idWHEREt."deletedAt" IS NULL ANDt."createdAt" BETWEEN '2016/01/01' AND '2020/01/01' ANDd."deletedAt" IS NULL ANDs."deletedAt" IS NULL)​/* End temporary table */​SELECTto_char("givenMonth", 'YYYY-mm') as "month",​/* donations */(SUM("amountInUSD" * recurringMonthlyTotal * (isNotRefund + isRefund) +"amountInUSD" * recurringAnnuallyTotal * (isNotRefund + isRefund) +"amountInUSD" * oneTimeDonations * (isNotRefund + isRefund) +"amountInUSD" * addedFunds) / 100)::DECIMAL(10, 0)::moneyAS "totalMoneyBroughtIntoPlatformInUSD",​(SUM("amountInUSD" * recurringMonthlyTotal * (isNotRefund + isRefund) +"amountInUSD" * recurringAnnuallyTotal * (isNotRefund + isRefund) +"amountInUSD" * oneTimeDonations * (isNotRefund + isRefund)) / 100)::DECIMAL(10, 0)::moneyAS "totalDonationsMadeOnPlatformInUSD",​(SUM("amountInUSD" * isRefund / 100))::DECIMAL(10, 0)::moneyAS "refundTransactions",​(SUM("platformFeeInUSD")/-100)::DECIMAL(10,0)::money AS "OCFeeInUSD",​/* monthly donations */​/* total donations */(SUM("amountInUSD" * recurringMonthlyTotal * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "recurringMonthlyTotalDonationsInUSD",(SUM("amountInUSD" * recurringMonthlyTotal * (isNotRefund + isRefund) * "isUser")/100)::DECIMAL(10,0)::money AS "recurringMonthlyTotalDonationsFromUsersInUSD",(SUM("amountInUSD" * recurringMonthlyTotal * (isNotRefund + isRefund) * "isOrg")/100)::DECIMAL(10,0)::money AS "recurringMonthlyTotalDonationsFromOrgsInUSD",​/* old donations */(SUM("amountInUSD" * recurringMonthlyOld * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "recurringMonthlyOldDonationsInUSD",(SUM("amountInUSD" * recurringMonthlyOld * (isNotRefund + isRefund) * "isUser")/100)::DECIMAL(10,0)::money AS "recurringMonthlyOldDonationsFromUsersInUSD",(SUM("amountInUSD" * recurringMonthlyOld * (isNotRefund + isRefund) * "isOrg")/100)::DECIMAL(10,0)::money AS "recurringMonthlyOldDonationsFromOrgsInUSD",​/* new donations */(SUM("amountInUSD" * recurringMonthlyNew * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "recurringMonthlyNewDonationsInUSD",(SUM("amountInUSD" * recurringMonthlyNew * (isNotRefund + isRefund) * "isUser")/100)::DECIMAL(10,0)::money AS "recurringMonthlyNewDonationsFromUsersInUSD",(SUM("amountInUSD" * recurringMonthlyNew * (isNotRefund + isRefund) * "isOrg")/100)::DECIMAL(10,0)::money AS "recurringMonthlyNewDonationsFromOrgsInUSD",​/* annual donations */​/* total donations */(SUM("amountInUSD" * recurringAnnuallyTotal * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "recurringAnnualDonationsInUSD",(SUM("amountInUSD" * recurringAnnuallyTotal * (isNotRefund + isRefund) * "isUser")/100)::DECIMAL(10,0)::money AS "recurringAnnuallyTotalDonationsFromUsersInUSD",(SUM("amountInUSD" * recurringAnnuallyTotal * (isNotRefund + isRefund) * "isOrg")/100)::DECIMAL(10,0)::money AS "recurringAnnuallyTotalDonationsFromOrgsInUSD",​/* old donations */(SUM("amountInUSD" * recurringAnnuallyOld * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "recurringAnnuallyOldDonationsInUSD",(SUM("amountInUSD" * recurringAnnuallyOld * (isNotRefund + isRefund) * "isUser")/100)::DECIMAL(10,0)::money AS "recurringAnnuallyOldDonationsFromUsersInUSD",(SUM("amountInUSD" * recurringAnnuallyOld * (isNotRefund + isRefund) * "isOrg")/100)::DECIMAL(10,0)::money AS "recurringAnnuallyOldDonationsFromOrgsInUSD",​/* new donations */(SUM("amountInUSD" * recurringAnnuallyNew * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "recurringAnnuallyNewDonationsInUSD",(SUM("amountInUSD" * recurringAnnuallyNew * (isNotRefund + isRefund) * "isUser")/100)::DECIMAL(10,0)::money AS "recurringAnnuallyNewDonationsFromUsersInUSD",(SUM("amountInUSD" * recurringAnnuallyNew * (isNotRefund + isRefund) * "isOrg")/100)::DECIMAL(10,0)::money AS "recurringAnnuallyNewDonationsFromOrgsInUSD",​/* one-time donations */(SUM("amountInUSD" * oneTimeDonations * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "oneTimeDonationsInUSD",(SUM("amountInUSD" * oneTimeDonations * (isNotRefund + isRefund) * "isUser")/100)::DECIMAL(10,0)::money AS "oneTimeDonationsFromUsersInUSD",(SUM("amountInUSD" * oneTimeDonations * (isNotRefund + isRefund) * "isOrg")/100)::DECIMAL(10,0)::money AS "oneTimeDonationsFromOrgsInUSD",​/* added funds */(SUM("amountInUSD" * addedFunds * (isNotRefund + isRefund))/100):: DECIMAL(10,0)::money AS "addedFundsInUSD",​/* expenses */(SUM("amountInUSD" * totalExpensesRecorded * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "expensesPaidInUSD",(SUM("amountInUSD" * manualExpenses * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "manualExpensesInUSD",(SUM("amountInUSD" * paypalExpenses * (isNotRefund + isRefund))/100)::DECIMAL(10,0)::money AS "paypalExpensesInUSD",​/* counts of transactions */COUNT(*)/2 AS "numTransactions",SUM(recurringMonthlyTotal + recurringAnnuallyTotal + oneTimeDonations + addedFunds) AS "numMoneyBroughtInEntries",SUM(recurringMonthlyTotal + recurringAnnuallyTotal + oneTimeDonations) AS "numDonationMadeOnPlatformEntries",​/* monthly */SUM(recurringMonthlyTotal * (isNotRefund + isRefund)) as "numRecurringMonthlyTotalDonations",SUM(recurringMonthlyTotal * (isNotRefund + isRefund) * "isUser") as "numRecurringMonthlyTotalDonationsFromUsers",SUM(recurringMonthlyTotal * (isNotRefund + isRefund) * "isOrg") as "numRecurringMonthlyTotalDonationsFromOrgs",​SUM(recurringMonthlyOld * (isNotRefund + isRefund)) as "numRecurringMonthlyOldDonations",SUM(recurringMonthlyOld * (isNotRefund + isRefund) * "isUser") as "numRecurringMonthlyOldDonationsFromUsers",SUM(recurringMonthlyOld * (isNotRefund + isRefund) * "isOrg") as "numRecurringMonthlyOldDonationsFromOrgs",​SUM(recurringMonthlyNew * (isNotRefund + isRefund)) as "numRecurringMonthlyNewDonations",SUM(recurringMonthlyNew * (isNotRefund + isRefund) * "isUser") as "numRecurringMonthlyNewDonationsFromUsers",SUM(recurringMonthlyNew * (isNotRefund + isRefund) * "isOrg") as "numRecurringMonthlyNewDonationsFromOrgs",​/* annually */SUM(recurringAnnuallyTotal * (isNotRefund + isRefund)) as "numRecurringAnnualDonations",SUM(recurringAnnuallyTotal * (isNotRefund + isRefund) * "isUser") as "numRecurringAnnuallyTotalDonationsFromUsers",SUM(recurringAnnuallyTotal * (isNotRefund + isRefund) * "isOrg") as "numRecurringAnnuallyTotalDonationsFromOrgs",​SUM(recurringAnnuallyOld * isNotRefund) as "numRecurringAnnuallyOldDonations",SUM(recurringAnnuallyOld * isNotRefund * "isUser") as "numRecurringAnnuallyOldDonationsFromUsers",SUM(recurringAnnuallyOld * isNotRefund * "isOrg") as "numRecurringAnnuallyOldDonationsFromOrgs",​SUM(recurringAnnuallyNew * (isNotRefund + isRefund)) as "numRecurringAnnuallyNewDonations",SUM(recurringAnnuallyNew * (isNotRefund + isRefund) * "isUser") as "numRecurringAnnuallyNewDonationsFromUsers",SUM(recurringAnnuallyNew * (isNotRefund + isRefund) * "isOrg") as "numRecurringAnnuallyNewDonationsFromOrgs",​/* one-time */SUM(oneTimeDonations * (isNotRefund + isRefund)) as "numOneTimeDonations",SUM(oneTimeDonations * (isNotRefund + isRefund) * "isUser") as "numOneTimeDonationsFromUsers",SUM(oneTimeDonations * (isNotRefund + isRefund) * "isOrg") as "numOneTimeDonationsFromOrgs",​SUM(addedFunds) as "numAddedFunds",SUM(totalExpensesRecorded) as "numExpensesPaid"​FROM conversionsGROUP BY "givenMonth"ORDER BY "givenMonth"
SELECT"hostCurrency",-sum("platformFeeInHostCurrency") / 100 as "revenue",date_trunc('month', "createdAt") as "month"FROM "Transactions"WHERE "OrderId" IS NOT NULLAND type = 'CREDIT'AND "deletedAt" IS NULLAND "platformFeeInHostCurrency" < 0GROUP BY "hostCurrency", "month"ORDER BY month DESC
select"currency",sum("amount") as "origAmount",date_trunc('month', "createdAt") as "givenMonth",casewhen "SubscriptionId" is Null THEN falseelse trueend as "recurring"from "Transactions"where "type" = 'DONATION' and "PaymentMethodId" is NOT NULLGROUP BY "currency", "givenMonth", "recurring"
Note: it doesn't include expenses (yet).
selectt."GroupId",g."name",t."txnCurrency",sum(t."amount") as "origAmount",date_trunc('month', t."createdAt") as "givenMonth",cast(sum(t."platformFeeInTxnCurrency") as FLOAT)/100 as "platformFee",cast(sum(t."hostFeeInTxnCurrency") AS FLOAT)/100 as "hostFee",cast(sum(t."paymentProcessorFeeInTxnCurrency") AS FLOAT)/100 as "stripeFee",cast(sum(t."amountInTxnCurrency") as FLOAT)/100 as "totalDonationsInTxnCurrency",count(t."id") as "numDonations"from "Transactions" tLEFT JOIN "Groups" g on t."GroupId" = g."id"where "type" = 'DONATION' and "txnCurrency" IS NOT NULL and "PaymentMethodId" is NOT NULLGROUP By t."GroupId", g."name", t."txnCurrency", "givenMonth"ORDER BY t."GroupId"
You'll need to update the month start and end dates to run it for a particular month and update %name for different organizatons.
selectt."GroupId",g."name",sum(t."amount") as "origAmount",cast(sum(t."platformFeeInTxnCurrency") as FLOAT)/100 as "platformFee",cast(sum(t."hostFeeInTxnCurrency") AS FLOAT)/100 as "hostFee",cast(sum(t."paymentProcessorFeeInTxnCurrency") AS FLOAT)/100 as "stripeFee",cast(sum(t."amountInTxnCurrency") as FLOAT)/100 as "totalDonationsInTxnCurrency",count(t."id") as "numDonations"from "Transactions" tLEFT JOIN "Groups" g on t."GroupId" = g."id"whereg."slug" like 'wwcode%' andt."createdAt" between '2016/05/01' and '2016/05/31'GROUP By t."GroupId", g."name"ORDER BY t."GroupId"
Change UserId to other hosts to find out for anyone else.
selectcast(COALESCE(sum(t."netAmountInGroupCurrency"), 0) as float)/100 as "hostBalance"from "UserGroups" ugleft join "Transactions" t on t."GroupId" = ug."GroupId"​where (ug."UserId" = 40 or ug."UserId" = 772)and ug.role like 'HOST'and ug."GroupId" not in (1, 7, 34) /* remove opencollective, tipbox and ispcwa (because it's negative) */and t."deletedAt" is null
with recentdonations as (SELECT distinct("DonationId")​FROM "Transactions"​WHERE "createdAt" between '2016/10/29' and '2016/11/30'and "DonationId" is not null and "deletedAt" is null)​selectd.id as "DonationId",d."UserId" as "UserId",d."GroupId" as "GroupId",d.amount as "Amount",d."createdAt" as "DonationCreatedAt",d.currency as currencyfrom "Donations" d​left join "Subscriptions" s on d."SubscriptionId" = s.id​whered.id not in (select "DonationId" from recentdonations)and d."deletedAt" is nulland s."deletedAt" is nulland s."isActive" = trueand d."isProcessed" = true​order by currency;