· 4 years ago · May 07, 2021, 06:12 PM
1explain(analyze,buffers)
2select coalesce("wave_invoices"."month", "stripe_invoices"."month")::date as "month",
3 (coalesce("wave_invoices"."totalAmount", 0) + coalesce("stripe_invoices"."totalAmount", 0))::int as "total_revenue"
4from (select "s"."month" as "month",
5 sum("s"."amount") as "totalAmount",
6 count(distinct "s"."id") as "invoiceCount",
7 count(distinct "s"."eventId") as "eventCount"
8from (select "event"."id" as "eventId",
9 "event_income"."id" as "id",
10 date_trunc('month', "event_income"."date" AT TIME ZONE 'America/New_York') as "month",
11 "event_income"."amount" as "amount"
12 from "event_income"
13 inner join "event" on "event"."id" = "event_income"."eventId"
14 inner join "user" uu on uu.id = "event"."ownerId"
15 where "event_income"."date" is not null
16 and exists (select 1 from (select substring(email from '@(.*)$') as domain, s."eventId"
17 from "user" u
18 inner join (select ei."eventId", e."ownerId", date_trunc('month', ei."date" AT TIME ZONE 'America/New_York') as "month"
19 from "event_income" ei
20 inner join "event" e on e."id" = ei."eventId") s on s."ownerId" = u."id"
21 where substring(u.email from '@(.*)$') = substring(uu.email from '@(.*)$')
22 and s."month" between timestamp '2020-01-01 00:00:00'
23 and date_trunc('month', "event_income"."date" AT TIME ZONE 'America/New_York') - INTERVAL '1 MONTHS'
24 and substring(u.email from '@(.*)$') != 'gmail.com'
25 ) ss
26 group by ss."eventId", ss."domain"
27 having count(ss."domain") >= 1
28)
29 ) s
30group by "s"."month") as "wave_invoices"
31
32 full join (select "s"."month" as "month",
33 sum("s"."amount") as "totalAmount",
34 count(distinct "s"."id") as "invoiceCount",
35 count(distinct "s"."eventId") as "eventCount"
36from (select "eventId",
37 "invoice"->>'id' as "id",
38 date_trunc('month', ("invoice"->>'paidAt')::timestamptz AT TIME ZONE 'America/New_York') as "month",
39 (("invoice"->>'amount')::real + coalesce(("invoice"->>'creditsRedeemed'), '0')::real) / 100 as "amount"
40 from "user" uu
41 inner join (select "event"."id" as "eventId",
42 "value" as "invoice",
43 "event"."ownerId"
44 from "event",
45 jsonb_array_elements("invoices")
46 where "invoices" is not null and "deletedAt" is null
47 ) s on s."ownerId" = uu.id
48 where "invoice"->>'paidAt' is not null
49 and exists (select substring(email from '@(.*)$') as domain, ss."eventId", ss."invoice"
50 from "user" u
51 inner join (select e.id as "eventId",
52 e."ownerId" as "ownerId",
53 "value" as "invoice"
54 from "event" e,
55 jsonb_array_elements("invoices")
56 where "invoices" is not null and "deletedAt" is null) ss on ss."ownerId" = u."id"
57 where substring(u.email from '@(.*)$') = substring(uu.email from '@(.*)$')
58 and date_trunc('month', (ss."invoice"->>'paidAt')::timestamptz AT TIME ZONE 'America/New_York') between
59 timestamp '2020-01-01 00:00:00'
60 and date_trunc('month', (s."invoice"->>'paidAt')::timestamptz AT TIME ZONE 'America/New_York') - INTERVAL '1 MONTHS'
61 and ss."invoice"->>'paidAt' is not null
62 and substring(u.email from '@(.*)$') != 'gmail.com'
63 group by substring(email from '@(.*)$'), ss."eventId", ss."invoice"
64 having count(substring(email from '@(.*)$')) >= 1
65 )
66 ) s
67group by "s"."month") as "stripe_invoices" on "stripe_invoices"."month" = "wave_invoices"."month"
68where extract('year' from coalesce("wave_invoices"."month", "stripe_invoices"."month")) >= 2020
69order by "month" desc nulls last;