Are there any Queries for hosts?


#1

Get all the pending and approved expenses (not PAID) from all the collectives (example with the Open Source Collective Host (id 772))

For each expense, we show the balance available in the collective. Note that the amount of the expense does not include the payment processor fees (so an expense of $100 cannot be paid via PayPal if the balance of the collective is $100). Fees are usually 2.9% + $0.30 but could be higher for international payments.

WITH 
  collectives AS (
    SELECT g.id, g.slug FROM "Groups" g
    LEFT JOIN "UserGroups" ug ON ug."GroupId"=g.id
    WHERE ug."UserId"=772
  ),
  balances AS (
    SELECT "GroupId", sum("netAmountInGroupCurrency") / 100 as balance
    FROM "Transactions"
    WHERE "deletedAt" IS NULL
    GROUP BY "GroupId"
  )

SELECT 
  e."createdAt",
  CONCAT('https://opencollective.com/',c.slug,'/expenses') as collective,
  b.balance, u.username, e.title, e.amount / 100 as amount, e.currency,
  e.category, e.status, e.attachment, e."payoutMethod", e.notes
FROM "Expenses" e 
INNER JOIN collectives c ON c.id=e."GroupId"
LEFT JOIN "Users" u ON u.id=e."UserId"
LEFT JOIN balances b ON b."GroupId" = e."GroupId"
WHERE e.status IN ('PENDING', 'APPROVED') AND e."deletedAt" IS NULL

Get all the transactions (donations and expenses) for all the WWCode collectives in 2016

SELECT g.slug, t."createdAt", e.category, t."amount", t."currency", u."firstName", u."lastName", t."description", e.attachment, e."payoutMethod", t."txnCurrencyFxRate", CAST(t."netAmountInGroupCurrency" AS FLOAT) / 100 as "netAmount", CAST(t."hostFeeInTxnCurrency" AS FLOAT) / 100 as hostFee, CAST(t."paymentProcessorFeeInTxnCurrency" AS FLOAT) / 100 as processingFee, CAST(t."platformFeeInTxnCurrency" AS FLOAT) / 100 as "opencollectiveFee" FROM "Groups" g 
LEFT JOIN "Transactions" t ON t."GroupId" = g.id
LEFT JOIN "Expenses" e ON t."ExpenseId" = e.id
LEFT JOIN "Users" u ON t."UserId" = u.id
WHERE g.slug LIKE '%wwcode%' 
AND t."deletedAt" IS NULL
AND t."createdAt" >= '2016-01-01'AND t."createdAt" <= '2017-01-01'

Get all donors per collective in 2016 (for wwcode)

SELECT g.slug as collective, min(t."createdAt") "firstDonationAt", max(t."createdAt") "lastDonationAt", sum(t."amount") as "totalDonations", max(t."currency") as currency, max(u."firstName") as "firstName", max(u."lastName") as "lastName", max(u.email) as email 
FROM "Groups" g 
LEFT JOIN "Transactions" t ON t."GroupId" = g.id
LEFT JOIN "Users" u ON t."UserId" = u.id
WHERE g.slug LIKE '%wwcode%' 
AND t.amount > 0
AND (t.tags @> '{Donation}' OR t.tags IS NULL)
AND t."deletedAt" IS NULL
AND t."createdAt" >= '2016-01-01'AND t."createdAt" <= '2017-01-01'
GROUP BY u.id, g.slug

Compute total PayPal Fees:

SELECT count(*) as "transactionsCount", sum(t."amount") as "totalAmount", max(t."currency") as currency, (sum(t."amount") * 0.022 + count(*) * 0.3) as "paypalFeesForNonProfit" FROM "Groups" g 
LEFT JOIN "Transactions" t ON t."GroupId" = g.id
WHERE g.slug LIKE '%wwcode%' 
AND t."deletedAt" IS NULL
AND t.amount < 0
AND t."PaymentMethodId" IS NOT NULL
AND t."createdAt" >= '2016-01-01'AND t."createdAt" <= '2017-01-01'
GROUP BY t.currency

US taxes: 1099

Get all people to whom a host paid more than $600 in a given year in given categories.
And get all the expenses from those people.

WITH "totalExpenses" AS (
  SELECT "UserId", (SUM(amount) / 100) as "totalAmount"
  FROM "Expenses" e
  WHERE e."createdAt" >= '2016-01-01' AND e."createdAt" < '2017-01-01'
  AND e.currency = 'USD'
  AND e.status = 'PAID'
  GROUP BY "UserId"
 )
 
SELECT g.slug, u.username, u."firstName", u."lastName", u."email", e.title, e.category, (e.amount/100) as amount, e.currency, e.attachment
FROM "Expenses" e 
LEFT JOIN "totalExpenses" te ON te."UserId" = e."UserId"
LEFT JOIN "Users" u ON u.id = e."UserId"
LEFT JOIN "Groups" g on g.id = e."GroupId"
WHERE te."totalAmount" >= 600 
AND g.slug NOT LIKE '%wwcode%'
AND e."deletedAt" is NULL
AND e.category IN ('Office', 'Engineering', 'Other')
AND u.username NOT IN ('xdamman', 'piamancini')
ORDER BY u.id