-
Notifications
You must be signed in to change notification settings - Fork 0
Queries to compute all contributions
Xavier Damman edited this page Mar 30, 2022
·
2 revisions
Recorded in this spreadsheet
WITH
fromDate as (VALUES('2022-02-01')),
tillDAte as (VALUES('2022-03-01')),
hosted_collectives as (
-- SELECT id FROM "Collectives" WHERE "HostCollectiveId" = 73495
SELECT "CollectiveId" as id, max(c.type) as "type", max(c.slug) as "slug", max(m."since") as "hostedSince"
FROM "Members" m
LEFT JOIN "Collectives" c ON c.id = m."CollectiveId"
WHERE m."MemberCollectiveId" = 73495
AND m.role='HOST' --364
AND m."deletedAt" IS NULL --230
GROUP BY m."CollectiveId" --227
),
members as (
SELECT "MemberCollectiveId" as "MemberCollectiveId", max(c.slug) as "memberSlug", max(c.type) as "memberType", max(m.role) as "memberRole",
m."CollectiveId" as "CollectiveId"
FROM "Members" m
LEFT JOIN "Collectives" c ON m."MemberCollectiveId" = c.id
WHERE m."CollectiveId" IN (SELECT id FROM hosted_collectives)
AND c.id IS NOT NULL
AND m."deletedAt" IS NULL
AND c."deletedAt" IS NULL
GROUP BY m."MemberCollectiveId", m."CollectiveId"
),
expenses as (
SELECT DISTINCT e."CollectiveId", e.id, e.status, e."lastEditedById"
FROM "ExpenseHistories" e
LEFT JOIN hosted_collectives hc ON hc.id = e."CollectiveId"
WHERE e."deletedAt" IS NULL
AND e."createdAt" < (table tillDate)::DATE
AND e."createdAt" > (table fromDate)::DATE
AND e."createdAt" > hc."hostedSince"
),
expenses_stats as (
-- CollectiveId, UserId, expenses_submitted, expenses_approved, expenses_paid
SELECT e."CollectiveId", e."lastEditedById" as "UserId",
SUM(
CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END
) as "expenses_submitted",
SUM(
CASE WHEN status = 'APPROVED' THEN 1 ELSE 0 END
) as "expenses_approved",
SUM(
CASE WHEN status = 'PAID' THEN 1 ELSE 0 END
) as "expenses_paid"
FROM expenses e
GROUP BY e."CollectiveId", e."lastEditedById"
),
orders_stats as (
SELECT o."CollectiveId",
o."CreatedByUserId" as "UserId",
count(o.id) as "orders_created"
FROM "Orders" o
LEFT JOIN "hosted_collectives" hc ON o."CollectiveId" = hc.id
WHERE o."deletedAt" IS NULL
AND o."CollectiveId" IN (SELECT id FROM hosted_collectives)
AND o."createdAt" < (table tillDate)::DATE
AND o."createdAt" > (table fromDate)::DATE
AND o."createdAt" > hc."hostedSince"
AND status IN ('PAID', 'ACTIVE')
GROUP BY o."CreatedByUserId", o."CollectiveId"
),
updates_stats as (
SELECT u."CollectiveId", u."CreatedByUserId" as "UserId", count(id) as "updates"
FROM "Updates" u
WHERE u."CollectiveId" IN (SELECT id FROM hosted_collectives)
AND u."publishedAt" IS NOT NULL
AND u."deletedAt" IS NULL
AND u."createdAt" < (table tillDate)::DATE
AND u."createdAt" > (table fromDate)::DATE
GROUP BY u."CreatedByUserId", u."CollectiveId"
),
comments_stats as (
SELECT c."CollectiveId", c."CreatedByUserId" as "UserId", count(id) as "comments" FROM "Comments" c
WHERE c."CollectiveId" IN (SELECT id FROM hosted_collectives)
AND c."deletedAt" IS NULL
AND c."createdAt" < (table tillDate)::DATE
AND c."createdAt" > (table fromDate)::DATE
GROUP BY c."CreatedByUserId", c."CollectiveId"
),
donations_stats_per_collective as (
SELECT max(t."CreatedByUserId") as "CreatedByUserId", max(c.slug) as "backerSlug", max(c.type) as "backerType",
t."FromCollectiveId" as "BackerCollectiveId", t."CollectiveId" as "CollectiveId", sum(amount) / 100 as "totalDonated"
FROM "Transactions" t
LEFT JOIN hosted_collectives hc ON hc.id = t."CollectiveId"
LEFT JOIN "Collectives" c ON c.id = t."FromCollectiveId"
WHERE t.type = 'CREDIT'
AND t."deletedAt" IS NULL
AND t."CollectiveId" IN (SELECT "CollectiveId" FROM hosted_collectives)
AND t."createdAt" < (table tillDate)::DATE
AND t."createdAt" > (table fromDate)::DATE
AND t."createdAt" > hc."hostedSince"
GROUP BY t."FromCollectiveId", t."CollectiveId"
),
donations_stats as (
-- We need to separate the case for individual donations
-- as someone can make donations with multiple user profiles (e.g. guest or incognito)
-- we exlude UserId 273665 https://opencollective.com/donations-not-registered-on-oc
-- and UserId 261750 https://opencollective.com/wikiloc-outdoor
with donations_stats_users as (
SELECT
t."CreatedByUserId" as "UserId",
max(uc.slug) as "backerSlug", 'USER' as "backerType",
max(uc.id) as "BackerCollectiveId", sum(amount) / 100 as "totalDonated"
FROM "Transactions" t
LEFT JOIN hosted_collectives hc ON hc.id = t."CollectiveId"
LEFT JOIN "Collectives" c ON c.id = t."FromCollectiveId"
LEFT JOIN "Users" u ON u.id = t."CreatedByUserId"
LEFT JOIN "Collectives" uc ON uc.id = u."CollectiveId"
WHERE t.type = 'CREDIT'
AND t."deletedAt" IS NULL
AND t."CollectiveId" IN (SELECT hc.id FROM hosted_collectives hc)
AND t."createdAt" < (table tillDate)::DATE
AND t."createdAt" > (table fromDate)::DATE
AND t."createdAt" > hc."hostedSince"
AND c.type = 'USER'
AND c.slug NOT IN ('donations-not-registered-on-oc', 'wikiloc-outdoor')
GROUP BY t."CreatedByUserId"
),
-- when donating as an organization, we cannot allocate
-- that donated amount to the user who performed the action on its behalf
-- note: we don't take into account collective to collective donations as
-- they didn't contribute to the funding of the host
donations_stats_others as (
SELECT
null::integer as "UserId",
max(c.slug) as "backerSlug", max(c.type) as "backerType",
max(c.id) as "BackerCollectiveId", sum(amount) / 100 as "totalDonated"
FROM "Transactions" t
LEFT JOIN hosted_collectives hc ON hc.id = t."CollectiveId"
LEFT JOIN "Collectives" c ON c.id = t."FromCollectiveId"
WHERE t.type = 'CREDIT'
AND t."deletedAt" IS NULL
AND t."CollectiveId" IN (SELECT hc.id FROM hosted_collectives hc)
AND t."createdAt" < (table tillDate)::DATE
AND t."createdAt" > (table fromDate)::DATE
AND t."createdAt" > hc."hostedSince"
AND c.type = 'ORGANIZATION'
GROUP BY t."FromCollectiveId"
)
SELECT * FROM donations_stats_users UNION SELECT * FROM donations_stats_others
),
stats as (
SELECT u.id as "UserId", u."CollectiveId" as "UserCollectiveId",
m."CollectiveId" as "CollectiveId",
"orders_created",
"expenses_submitted", "expenses_approved", "expenses_paid",
"comments", "updates"
FROM "Users" u
LEFT JOIN members m ON m."MemberCollectiveId" = u."CollectiveId"
LEFT JOIN "orders_stats" os ON os."UserId" = u.id AND os."CollectiveId" = m."CollectiveId"
LEFT JOIN "expenses_stats" e ON e."UserId" = u.id AND e."CollectiveId" = m."CollectiveId"
LEFT JOIN "updates_stats" us ON us."UserId" = u.id AND us."CollectiveId" = m."CollectiveId"
LEFT JOIN "comments_stats" cs ON cs."UserId" = u.id AND cs."CollectiveId" = m."CollectiveId"
),
aggregated_users_stats as (
WITH userids as (
SELECT id, "CollectiveId" as "UserCollectiveId" FROM "Users" u WHERE id IN (
SELECT "UserId" FROM orders_stats
UNION SELECT "UserId" FROM expenses_stats
UNION SELECT "UserId" FROM comments_stats
UNION SELECT "UserId" FROM updates_stats
)
),
collectives_created as (
SELECT "CreatedByUserId" as "UserId", count(*) as "collectives_created" FROM "Collectives" c
LEFT JOIN "hosted_collectives" hc ON hc.id = c.id
WHERE c."createdAt" >= (hc."hostedSince"::TIMESTAMP - INTERVAL '1 DAY')
AND c."createdAt" < (table tillDate)::DATE
AND c."createdAt" > (table fromDate)::DATE
GROUP BY "CreatedByUserId"
),
orders as (
SELECT os."UserId", SUM(orders_created) as "orders_created" FROM "orders_stats" os GROUP BY os."UserId"
),
expenses as (
SELECT es."UserId",
SUM(expenses_submitted) as "expenses_submitted",
SUM(expenses_approved) as "expenses_approved",
SUM(expenses_paid) as "expenses_paid"
FROM "expenses_stats" es GROUP BY es."UserId"
),
comments as (
SELECT cs."UserId", SUM(comments) as "comments" FROM "comments_stats" cs GROUP BY cs."UserId"
),
updates as (
SELECT us."UserId", SUM(updates) as "updates" FROM "updates_stats" us GROUP BY us."UserId"
)
SELECT u.id as "UserId", u."UserCollectiveId" as "UserCollectiveId", uc.slug as "userSlug",
cc.collectives_created, o.orders_created, e.expenses_submitted, e.expenses_approved, e.expenses_paid, c.comments, up.updates
FROM userids u
LEFT JOIN orders o ON o."UserId" = u.id
LEFT JOIN expenses e ON e."UserId" = u.id
LEFT JOIN comments c ON c."UserId" = u.id
LEFT JOIN updates up ON up."UserId" = u.id
LEFT JOIN collectives_created cc ON cc."UserId" = u.id
LEFT JOIN "Collectives" uc ON u."UserCollectiveId" = uc.id
-- ORDER BY expenses_paid DESC NULLS LAST
)
SELECT
-- CASE WHEN s."UserId" IS NOT NULL THEN s."UserId" ELSE ds."UserId" END as "UserId",
CASE WHEN ds."backerType" IS NOT NULL THEN ds."backerType" ELSE 'USER' END as "type",
CASE WHEN ds."backerSlug" IS NOT NULL THEN ds."backerSlug" ELSE s."userSlug" END as "slug",
s.collectives_created as "collectives created", s.orders_created as "orders created", s.expenses_submitted as "expenses submitted", s.expenses_approved as "expenses approved", s.expenses_paid as "expenses paid", s.comments as "comments", s.updates as "updates",
ds."totalDonated" as "totalDonated"
FROM aggregated_users_stats s
FULL JOIN donations_stats ds ON ds."BackerCollectiveId" = s."UserCollectiveId" -- should be 51 euros donated as guest account, so Order.FromCollectiveId != user."CollectiveId"
; -- 3160 -- 2961 with new donations_stats
SELECT
s."UserId",
CASE
WHEN s."backerSlug" IS NOT NULL THEN CONCAT('https://opencollective.com/',s."backerSlug")
WHEN mc.slug IS NOT NULL then CONCAT('https://opencollective.com/',mc.slug)
ELSE NULL
END as "profile",
CASE
WHEN s."backerType" IS NOT NULL THEN s."backerType" ELSE 'USER'
END as "type",
s.orders_created, s."totalDonated", expenses_submitted, expenses_approved, expenses_paid, comments, updates
FROM aggregated_stats s
LEFT JOIN "Collectives" mc ON mc.id = s."UserCollectiveId"
WHERE mc.slug IS NULL AND s."backerSlug" is null
ORDER BY "totalDonated" DESC NULLS LAST
--ORDER BY "expenses_paid" DESC NULLS LAST
;