with "monthlySubscriptions" as(SELECTt."GroupId", COALESCE(SUM(t.amount * 12),0) as totalFROM "Subscriptions" sLEFT JOIN "Donations" d ON s.id = d."SubscriptionId"LEFT JOIN "Transactions" tON (s.id = d."SubscriptionId"AND t.id = (SELECT MAX(id) from "Transactions" t where t."DonationId" = d.id))WHERE t.amount > 0AND t."deletedAt" IS NULLAND s.interval = 'month'AND s."isActive" IS TRUEAND s."deletedAt" IS NULLGROUP BY t."GroupId"),â€‹"yearlyAndOneTimeSubscriptions" as(SELECTt."GroupId", COALESCE(SUM(t.amount),0) as total FROM "Transactions" tLEFT JOIN "Donations" d ON t."DonationId" = d.idLEFT JOIN "Subscriptions" s ON d."SubscriptionId" = s.idWHERE t.amount > 0AND t."deletedAt" IS NULLAND t."createdAt" > (current_date - INTERVAL '12 months')AND ((s.interval = 'year' AND s."isActive" IS TRUE AND s."deletedAt" IS NULL) OR s.interval IS NULL)GROUP BY t."GroupId"),â€‹"inActiveSubscriptions" as(SELECTt."GroupId", COALESCE(SUM(t.amount),0) as total FROM "Transactions" tLEFT JOIN "Donations" d on t."DonationId" = d.idLEFT JOIN "Subscriptions" s ON d."SubscriptionId" = s.idWHERE t.amount > 0AND t."deletedAt" IS NULLAND t."createdAt" > (current_date - INTERVAL '12 months')AND s.interval = 'month' AND s."isActive" IS FALSE AND s."deletedAt" IS NULLGROUP BY t."GroupId"),â€‹"expensesData" as(select e."GroupId", e.status, count(*) as "countExpenses", sum(amount) as "totalExpenses", max("createdAt") as "lastExpenseDate" from "Expenses" eWHERE "deletedAt" is nullGROUP BY "GroupId", statusORDER BY "GroupId")â€‹SELECTid as "groupId",slug,COALESCE(ms.total,0)/100 + COALESCE(ys.total,0)/100 + COALESCE(ias.total,0)/100 as "annualBudget",currency,ed.status as "expenseStatus",ed."lastExpenseDate",ed."countExpenses",ed."totalExpenses"/100 as "sumOfExpenses"â€‹from "Groups" gLEFT JOIN "monthlySubscriptions" ms on ms."GroupId" = g.idLEFT JOIN "yearlyAndOneTimeSubscriptions" ys on ys."GroupId" = g.idLEFT JOIN "inActiveSubscriptions" ias on ias."GroupId" = g.idLEFT JOIN "expensesData" ed on ed."GroupId" = g.idORDER BY g.id

with "backers" as(select "GroupId", count(*) as "backerCount" from "UserGroups"where role = 'BACKER'GROUP BY "GroupId")â€‹selectg.id as "groupId",g.name as "groupName",g.slug as "groupSlug",u.id as "userId",u."firstName" as "userFirstName",u."lastName" as "userLastName",u.email as "userEmail",u.username as "username",u.website as "userWebsite",u."twitterHandle" as "userTwitter",ug.role,b."backerCount"from "UserGroups" ugLEFT JOIN "Groups" g on ug."GroupId" = g.idLEFT JOIN "Users" u on ug."UserId" = u.idLEFT JOIN "backers" b on ug."GroupId" = b."GroupId"WHERE ug.role = 'MEMBER' OR ug.role = 'HOST'ORDER BY g.id

with subs as(select distinct(id) as id, max("chargeRetryCount") as "chargeRetryCount" from "SubscriptionHistories" sh where "deletedAt" is null and "chargeRetryCount" > 0 group by id)â€‹select oh.id, max("FromCollectiveId") as "FromCollectiveId", max("SubscriptionId"), max("chargeRetryCount") as "subId"from "OrderHistories" ohleft join subs s on oh."SubscriptionId" = s.idwhere "chargeRetryCount" > 0group by oh.id having count("PaymentMethodId") > 1

-- Revenue and transaction splits by month with all currencies-- converted to USD.---- Note: the exchange rates are from July 10 2020â€‹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.874114WHEN (t.currency = 'GBP') THEN t.amount / 0.79071WHEN (t.currency = 'MXN') THEN t.amount / 22.66051WHEN (t.currency = 'CAD') THEN t.amount / 1.354446WHEN (t.currency = 'CHF') THEN t.amount / 0.9392WHEN (t.currency = 'UYU') THEN t.amount / 43.695617WHEN (t.currency = 'AUD') THEN t.amount / 1.426737WHEN (t.currency = 'INR') THEN t.amount / 74.77525WHEN (t.currency = 'JPY') THEN t.amount / 107.175937WHEN (t.currency = 'NZD') THEN t.amount / 1.522297WHEN (t.currency = 'NGN') THEN t.amount / 388.000345WHEN (t.currency = 'CZK') THEN t.amount / 23.274006WHEN (t.currency = 'BRL') THEN t.amount / 5.357999WHEN (t.currency = 'SEK') THEN t.amount / 8.981097ELSE 0END AS "amountInUSD",CASEWHEN (t."data"->>'isFeesOnTop' = 'true' AND t."type" = 'CREDIT' AND t."CollectiveId" = 1) THEN t."amount"WHEN (t.currency = 'USD') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 1WHEN (t.currency = 'EUR') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 0.874114WHEN (t.currency = 'GBP') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 0.79071WHEN (t.currency = 'MXN') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 22.66051WHEN (t.currency = 'CAD') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 1.354445WHEN (t.currency = 'CHF') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 0.9392WHEN (t.currency = 'UYU') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 43.695617WHEN (t.currency = 'AUD') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 1.426737WHEN (t.currency = 'INR') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 74.77525WHEN (t.currency = 'JPY') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 107.175937WHEN (t.currency = 'NZD') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 1.522297WHEN (t.currency = 'NGN') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 388.000345WHEN (t.currency = 'CZK') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 23.274006WHEN (t.currency = 'BRL') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 5.357999WHEN (t.currency = 'SEK') AND t.amount > 0 THEN t."platformFeeInHostCurrency" / 8.981097ELSE 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,CASEWHEN (t."data"->>'isFeesOnTop' = 'true' AND t."type" = 'CREDIT' AND t."CollectiveId" = 1) THEN t."amount"ELSE 0END AS feesOnTopâ€‹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 '2020/01/01' AND '2021/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",SUM(feesOnTop) as "feesOnTop"â€‹FROM conversionsGROUP BY "givenMonth"ORDER BY "givenMonth"