Skip to content

Commit d97a1a9

Browse files
huumnhuumn
committed
fix and deduplicate aggregates for stackers/subs lists
1 parent 46d34da commit d97a1a9

File tree

4 files changed

+121
-149
lines changed

4 files changed

+121
-149
lines changed

‎api/resolvers/sub.js‎

Lines changed: 92 additions & 122 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,72 @@ export async function getSub (parent, { name }, { models, me }) {
3333
})
3434
}
3535

36+
export async function topSubs (parent, { query, cursor, when, from, to, limit, by = 'revenue' }, { models, me }) {
37+
const decodedCursor = decodeCursor(cursor)
38+
const [fromDate, toDate] = whenRange(when, from, to || decodeCursor.time)
39+
const granularity = timeUnitForRange([fromDate, toDate]).toUpperCase()
40+
41+
let column
42+
switch (by) {
43+
case 'revenue': column = Prisma.sql`revenue`; break
44+
case 'spent': column = Prisma.sql`spent`; break
45+
case 'stacked': column = Prisma.sql`stacked`; break
46+
case 'items': column = Prisma.sql`nitems`; break
47+
default: throw new GqlInputError('invalid sort')
48+
}
49+
50+
const subs = await models.$queryRaw`
51+
WITH user_subs AS (
52+
${query}
53+
),
54+
sub_outgoing AS (
55+
SELECT user_subs.name,
56+
COALESCE(floor(sum("AggPayOut"."sumMtokens") FILTER (WHERE "AggPayOut"."payOutType" = 'TERRITORY_REVENUE') / 1000), 0) as revenue,
57+
COALESCE(floor(sum("AggPayOut"."sumMtokens") FILTER (WHERE "AggPayOut"."payOutType" = 'ZAP') / 1000), 0) as stacked
58+
FROM user_subs
59+
LEFT JOIN "AggPayOut" ON "AggPayOut"."subName" = user_subs.name
60+
WHERE "AggPayOut"."timeBucket" >= ${fromDate}
61+
AND "AggPayOut"."timeBucket" <= ${toDate}
62+
AND "AggPayOut"."granularity" = ${granularity}::"AggGranularity"
63+
AND "AggPayOut"."slice" = 'SUB_BY_TYPE'
64+
AND "AggPayOut"."payInType" IS NULL
65+
GROUP BY user_subs.name
66+
),
67+
sub_incoming AS (
68+
SELECT user_subs.name,
69+
floor(COALESCE(sum("AggPayIn"."sumMcost"), 0) / 1000) as spent,
70+
sum("AggPayIn"."countGroup") FILTER (WHERE "AggPayIn"."payInType" = 'ITEM_CREATE') as nitems
71+
FROM user_subs
72+
LEFT JOIN "AggPayIn" ON "AggPayIn"."subName" = user_subs.name
73+
WHERE "AggPayIn"."timeBucket" >= ${fromDate}
74+
AND "AggPayIn"."timeBucket" <= ${toDate}
75+
AND "AggPayIn"."granularity" = ${granularity}::"AggGranularity"
76+
AND "AggPayIn"."slice" = 'SUB_BY_TYPE'
77+
AND "AggPayIn"."subName" IS NOT NULL
78+
AND "AggPayIn"."payInType" <> 'DEFUNCT_TERRITORY_DAILY_PAYOUT'
79+
GROUP BY user_subs.name
80+
),
81+
sub_stats AS (
82+
SELECT COALESCE(sub_outgoing.name, sub_incoming.name) as name,
83+
COALESCE(sub_outgoing."revenue", 0) as revenue,
84+
COALESCE(sub_outgoing."stacked", 0) as stacked,
85+
COALESCE(sub_incoming."spent", 0) as spent,
86+
COALESCE(sub_incoming."nitems", 0) as nitems
87+
FROM sub_outgoing
88+
FULL JOIN sub_incoming ON sub_outgoing.name = sub_incoming.name
89+
)
90+
SELECT * FROM sub_stats
91+
JOIN "Sub" ON sub_stats.name = "Sub".name
92+
ORDER BY ${column} DESC NULLS LAST, "Sub".created_at ASC
93+
OFFSET ${decodedCursor.offset}
94+
LIMIT ${limit}`
95+
96+
return {
97+
cursor: subs.length === limit ? nextCursorEncoded(decodedCursor, limit) : null,
98+
subs
99+
}
100+
}
101+
36102
export default {
37103
Query: {
38104
sub: getSub,
@@ -89,147 +155,51 @@ export default {
89155
return latest?.createdAt
90156
},
91157
topSubs: async (parent, { cursor, when, by = 'stacked', from, to, limit }, { models, me }) => {
92-
const decodedCursor = decodeCursor(cursor)
93-
const [fromDate, toDate] = whenRange(when, from, to || decodeCursor.time)
94-
const granularity = timeUnitForRange([fromDate, toDate]).toUpperCase()
95-
96-
let column
97-
switch (by) {
98-
case 'revenue': column = Prisma.sql`revenue`; break
99-
case 'spent': column = Prisma.sql`spent`; break
100-
case 'stacked': column = Prisma.sql`stacked`; break
101-
case 'items': column = Prisma.sql`nitems`; break
102-
default: throw new GqlInputError('invalid sort')
103-
}
104-
105-
const subs = await models.$queryRaw`
106-
WITH sub_outgoing AS (
107-
SELECT "AggPayIn"."subName", floor(coalesce(sum("AggPayIn"."sumMcost"), 0) / 1000) as spent,
108-
sum("AggPayIn"."countGroup") FILTER (WHERE "AggPayIn"."payInType" = 'ITEM_CREATE') as nitems
109-
FROM "AggPayIn"
110-
WHERE "AggPayIn"."timeBucket" >= ${fromDate}
111-
AND "AggPayIn"."timeBucket" <= ${toDate}
112-
AND "AggPayIn"."granularity" = ${granularity}::"AggGranularity"
113-
AND "AggPayIn"."slice" = 'SUB_BY_TYPE'
114-
AND "AggPayIn"."subName" IS NOT NULL
115-
AND "AggPayIn"."payInType" <> 'DEFUNCT_TERRITORY_DAILY_PAYOUT'
116-
GROUP BY "AggPayIn"."subName"
117-
),
118-
sub_stats AS (
119-
SELECT "AggPayOut"."subName", COALESCE(sub_outgoing."spent", 0) as spent,
120-
COALESCE(sub_outgoing."nitems", 0) as nitems,
121-
floor(coalesce(sum("AggPayOut"."sumMtokens") FILTER (WHERE "AggPayOut"."payOutType" = 'ZAP'), 0) / 1000) as stacked,
122-
floor(coalesce(sum("AggPayOut"."sumMtokens") FILTER (WHERE "AggPayOut"."payOutType" = 'TERRITORY_REVENUE'), 0) / 1000) as revenue
123-
FROM "AggPayOut"
124-
LEFT JOIN sub_outgoing ON "AggPayOut"."subName" = sub_outgoing."subName"
125-
WHERE "AggPayOut"."timeBucket" >= ${fromDate}
126-
AND "AggPayOut"."timeBucket" <= ${toDate}
127-
AND "AggPayOut"."granularity" = ${granularity}::"AggGranularity"
128-
AND "AggPayOut"."slice" = 'SUB_BY_TYPE'
129-
AND "AggPayOut"."subName" IS NOT NULL
130-
AND "AggPayOut"."payInType" IS NULL
131-
GROUP BY "AggPayOut"."subName", sub_outgoing."spent", sub_outgoing."nitems"
132-
)
133-
SELECT * FROM sub_stats
134-
JOIN "Sub" ON sub_stats."subName" = "Sub".name
135-
ORDER BY ${column} DESC NULLS LAST, "Sub".created_at ASC
136-
OFFSET ${decodedCursor.offset}
137-
LIMIT ${limit}`
158+
const query = Prisma.sql`
159+
SELECT "Sub".name
160+
FROM "Sub"
161+
WHERE "Sub".status <> 'STOPPED'
162+
GROUP BY "Sub".name
163+
`
138164

139-
return {
140-
cursor: subs.length === limit ? nextCursorEncoded(decodedCursor, limit) : null,
141-
subs
142-
}
165+
return await topSubs(parent, { query, cursor, when, from, to, limit, by }, { models, me })
143166
},
144-
userSubs: async (_parent, { name, cursor, when, by = 'revenue', from, to, limit }, { models, me }) => {
167+
userSubs: async (parent, { name, cursor, when, by = 'revenue', from, to, limit }, { models, me }) => {
145168
if (!name) {
146169
throw new GqlInputError('must supply user name')
147170
}
148171

149-
const decodedCursor = decodeCursor(cursor)
150-
const [fromDate, toDate] = whenRange(when, from, to || decodeCursor.time)
151-
const granularity = timeUnitForRange([fromDate, toDate]).toUpperCase()
152-
153-
let column
154-
switch (by) {
155-
case 'revenue': column = Prisma.sql`revenue`; break
156-
case 'spent': column = Prisma.sql`spent`; break
157-
case 'stacked': column = Prisma.sql`stacked`; break
158-
case 'items': column = Prisma.sql`nitems`; break
159-
default: throw new GqlInputError('invalid sort')
160-
}
161-
162-
const subs = await models.$queryRaw`
163-
WITH user_subs AS (
164-
SELECT "Sub".name
165-
FROM "Sub"
166-
JOIN users ON users.id = "Sub"."userId"
167-
WHERE users.name = ${name}
168-
AND "Sub".status = 'ACTIVE'
169-
GROUP BY "Sub".name
170-
),
171-
sub_outgoing AS (
172-
SELECT user_subs.name,
173-
COALESCE(floor(sum("AggPayOut"."sumMtokens") FILTER (WHERE "AggPayOut"."payOutType" = 'TERRITORY_REVENUE') / 1000), 0) as revenue,
174-
COALESCE(floor(sum("AggPayOut"."sumMtokens") FILTER (WHERE "AggPayOut"."payOutType" = 'ZAP') / 1000), 0) as stacked
175-
FROM user_subs
176-
LEFT JOIN "AggPayOut" ON "AggPayOut"."subName" = user_subs.name
177-
WHERE "AggPayOut"."timeBucket" >= ${fromDate}
178-
AND "AggPayOut"."timeBucket" <= ${toDate}
179-
AND "AggPayOut"."granularity" = ${granularity}::"AggGranularity"
180-
AND "AggPayOut"."slice" = 'SUB_BY_TYPE'
181-
AND "AggPayOut"."payInType" IS NULL
182-
GROUP BY user_subs.name
183-
),
184-
sub_stats AS (
185-
SELECT sub_outgoing.name,
186-
COALESCE(sub_outgoing."revenue", 0) as revenue,
187-
COALESCE(sub_outgoing."stacked", 0) as stacked,
188-
COALESCE(floor(sum("AggPayIn"."sumMcost") / 1000), 0) as spent,
189-
COALESCE(sum("AggPayIn"."countGroup") FILTER (WHERE "AggPayIn"."payInType" = 'ITEM_CREATE'), 0) as nitems
190-
FROM sub_outgoing
191-
LEFT JOIN "AggPayIn" ON "AggPayIn"."subName" = sub_outgoing.name
192-
WHERE "AggPayIn"."timeBucket" >= ${fromDate}
193-
AND "AggPayIn"."timeBucket" <= ${toDate}
194-
AND "AggPayIn"."granularity" = ${granularity}::"AggGranularity"
195-
AND "AggPayIn"."slice" = 'SUB_BY_TYPE'
196-
AND "AggPayIn"."payInType" <> 'DEFUNCT_TERRITORY_DAILY_PAYOUT'
197-
GROUP BY sub_outgoing.name, sub_outgoing.revenue, sub_outgoing.stacked
198-
)
199-
SELECT * FROM sub_stats
200-
JOIN "Sub" ON sub_stats.name = "Sub".name
201-
ORDER BY ${column} DESC NULLS LAST, "Sub".created_at ASC
202-
OFFSET ${decodedCursor.offset}
203-
LIMIT ${limit}`
172+
const query = Prisma.sql`
173+
SELECT "Sub".name
174+
FROM "Sub"
175+
JOIN users ON users.id = "Sub"."userId" AND users.name = ${name}
176+
WHERE "Sub".status <> 'STOPPED'
177+
GROUP BY "Sub".name
178+
`
204179

205-
return {
206-
cursor: subs.length === limit ? nextCursorEncoded(decodedCursor, limit) : null,
207-
subs
208-
}
180+
return await topSubs(parent, { query, cursor, when, from, to, limit, by }, { models, me })
209181
},
210182
mySubscribedSubs: async (parent, { cursor }, { models, me }) => {
211183
if (!me) {
212184
throw new GqlAuthenticationError()
213185
}
214186

215-
const decodedCursor = decodeCursor(cursor)
216-
const subs = await models.$queryRaw`
217-
SELECT "Sub".*,
218-
"MuteSub"."userId" IS NOT NULL as "meMuteSub",
219-
TRUE as "meSubscription"
187+
const query = Prisma.sql`
188+
SELECT "Sub".name
220189
FROM "SubSubscription"
221190
JOIN "Sub" ON "SubSubscription"."subName" = "Sub".name
222-
LEFT JOIN "MuteSub" ON "MuteSub"."subName" = "Sub".name AND "MuteSub"."userId" = ${me.id}
223191
WHERE "SubSubscription"."userId" = ${me.id}
224-
AND "Sub".status <> 'STOPPED'
225-
ORDER BY "Sub".name ASC
226-
OFFSET ${decodedCursor.offset}
227-
LIMIT ${LIMIT}
192+
AND "Sub".status <> 'STOPPED'
193+
GROUP BY "Sub".name
228194
`
229195

196+
const { subs, cursor: mySubscribedSubsCursor } = await topSubs(parent, { query, cursor, when: 'forever', limit: LIMIT }, { models, me })
230197
return {
231-
cursor: subs.length === LIMIT ? nextCursorEncoded(decodedCursor, LIMIT) : null,
232-
subs
198+
cursor: mySubscribedSubsCursor,
199+
subs: subs.map(sub => ({
200+
...sub,
201+
meSubscription: true
202+
}))
233203
}
234204
}
235205
},

‎api/resolvers/user.js‎

Lines changed: 22 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,8 @@ export async function topUsers (parent, { cursor, when, by = 'stacked', from, to
6868
column = Prisma.sql`spent`; break
6969
case 'items':
7070
column = Prisma.sql`nitems`; break
71+
case 'streak':
72+
column = Prisma.sql`streak`; break
7173
default:
7274
throw new GqlInputError('invalid sort')
7375
}
@@ -80,29 +82,35 @@ export async function topUsers (parent, { cursor, when, by = 'stacked', from, to
8082
WHERE "AggPayIn"."timeBucket" >= ${fromDate}
8183
AND "AggPayIn"."timeBucket" <= ${toDate}
8284
AND "AggPayIn"."granularity" = ${granularity}::"AggGranularity"
83-
AND "AggPayIn"."payInType" NOT IN ('WITHDRAWAL', 'AUTO_WITHDRAWAL', 'PROXY_PAYMENT')
85+
AND "AggPayIn"."payInType" NOT IN ('WITHDRAWAL', 'AUTO_WITHDRAWAL', 'PROXY_PAYMENT', 'BUY_CREDITS')
8486
AND "AggPayIn"."slice" = 'USER_BY_TYPE'
8587
GROUP BY "AggPayIn"."userId"
8688
),
87-
user_stats AS (
88-
SELECT "AggPayOut"."userId", COALESCE(user_outgoing."spent", 0) as spent,
89-
COALESCE(user_outgoing."nitems", 0) as nitems, floor(sum("AggPayOut"."sumMtokens") / 1000) as stacked
89+
user_incoming AS (
90+
SELECT "AggPayOut"."userId", floor(sum("AggPayOut"."sumMtokens") / 1000) as stacked
9091
FROM "AggPayOut"
91-
LEFT JOIN user_outgoing ON "AggPayOut"."userId" = user_outgoing."userId"
9292
WHERE "AggPayOut"."timeBucket" >= ${fromDate}
9393
AND "AggPayOut"."timeBucket" <= ${toDate}
9494
AND "AggPayOut"."granularity" = ${granularity}::"AggGranularity"
9595
AND "AggPayOut"."slice" = 'USER_BY_TYPE'
96-
AND "AggPayOut"."payInType" NOT IN ('WITHDRAWAL', 'AUTO_WITHDRAWAL', 'PROXY_PAYMENT')
97-
GROUP BY "AggPayOut"."userId", user_outgoing."spent", user_outgoing."nitems"
96+
AND "AggPayOut"."payInType" IS NOT NULL
97+
AND "AggPayOut"."payInType" NOT IN ('WITHDRAWAL', 'AUTO_WITHDRAWAL', 'PROXY_PAYMENT', 'BUY_CREDITS')
98+
GROUP BY "AggPayOut"."userId"
99+
),
100+
user_stats AS (
101+
SELECT COALESCE(user_outgoing."userId", user_incoming."userId") as "userId", COALESCE(user_outgoing."spent", 0) as spent,
102+
COALESCE(user_outgoing."nitems", 0) as nitems, COALESCE(user_incoming."stacked", 0) as stacked
103+
FROM user_outgoing
104+
FULL JOIN user_incoming ON user_outgoing."userId" = user_incoming."userId"
98105
)
99106
SELECT * FROM user_stats
100107
JOIN users ON user_stats."userId" = users.id
108+
WHERE users.id NOT IN (${Prisma.join([...SN_SYSTEM_ONLY_IDS, USER_ID.anon])})
101109
ORDER BY ${column} DESC NULLS LAST, users.created_at ASC
102110
OFFSET ${decodedCursor.offset}
103111
LIMIT ${limit}`
104112
).map(
105-
u => (u.hideFromTopUsers || SN_SYSTEM_ONLY_IDS.includes(u.id)) && (!me || me.id !== u.id) ? null : u
113+
u => u.hideFromTopUsers && (!me || me.id !== u.id) ? null : u
106114
)
107115

108116
return {
@@ -185,23 +193,11 @@ export default {
185193
}
186194
},
187195
topCowboys: async (parent, { cursor }, { models, me }) => {
188-
const decodedCursor = decodeCursor(cursor)
189-
const range = whenRange('forever')
190-
191-
const users = (await models.$queryRawUnsafe(`
192-
SELECT *
193-
FROM users
194-
WHERE streak IS NOT NULL
195-
ORDER BY streak DESC, created_at ASC
196-
OFFSET $3
197-
LIMIT ${LIMIT}`, ...range, decodedCursor.offset)
198-
).map(
199-
u => (u.hideFromTopUsers || u.hideCowboyHat) && (!me || me.id !== u.id) ? null : u
200-
)
201-
196+
const { users, cursor: topCowboysCursor } = await topUsers(parent, { cursor, when: 'forever', by: 'streak', limit: LIMIT }, { models, me })
197+
const cowboys = users.map(u => (u?.hideCowboyHat && (!me || me.id !== u.id)) ? null : u).filter(u => u?.streak !== null)
202198
return {
203-
cursor: users.length === LIMIT ? nextCursorEncoded(decodedCursor) : null,
204-
users
199+
cursor: cowboys.length === LIMIT ? topCowboysCursor : null,
200+
users: cowboys
205201
}
206202
},
207203
userSuggestions: async (parent, { q, limit }, { models }) => {
@@ -1067,7 +1063,7 @@ export default {
10671063
AND "AggPayOut"."timeBucket" <= ${toDate}
10681064
AND "AggPayOut"."granularity" = ${granularity}::"AggGranularity"
10691065
AND "AggPayOut"."slice" = 'USER_BY_TYPE'
1070-
AND "AggPayOut"."payInType" NOT IN ('WITHDRAWAL', 'AUTO_WITHDRAWAL', 'PROXY_PAYMENT')
1066+
AND "AggPayOut"."payInType" NOT IN ('WITHDRAWAL', 'AUTO_WITHDRAWAL', 'PROXY_PAYMENT', 'BUY_CREDITS')
10711067
GROUP BY "AggPayOut"."userId"
10721068
`
10731069
return (stacked && msatsToSats(stacked)) || 0
@@ -1091,7 +1087,7 @@ export default {
10911087
AND "AggPayIn"."timeBucket" <= ${toDate}
10921088
AND "AggPayIn"."granularity" = ${granularity}::"AggGranularity"
10931089
AND "AggPayIn"."slice" = 'USER_BY_TYPE'
1094-
AND "AggPayIn"."payInType" NOT IN ('WITHDRAWAL', 'AUTO_WITHDRAWAL', 'PROXY_PAYMENT')
1090+
AND "AggPayIn"."payInType" NOT IN ('WITHDRAWAL', 'AUTO_WITHDRAWAL', 'PROXY_PAYMENT', 'BUY_CREDITS')
10951091
GROUP BY "AggPayIn"."userId"
10961092
`
10971093

‎fragments/users.js‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -353,6 +353,13 @@ export const MY_SUBSCRIBED_SUBS = gql`
353353
mySubscribedSubs(cursor: $cursor) {
354354
subs {
355355
...SubFullFields
356+
nitems(when: "forever")
357+
358+
optional {
359+
stacked(when: "forever")
360+
spent(when: "forever")
361+
revenue(when: "forever")
362+
}
356363
}
357364
cursor
358365
}

‎pages/settings/subscriptions/territories.js‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,6 @@ export default function MySubscribedSubs ({ ssrData }) {
2222
destructureData={data => data.mySubscribedSubs}
2323
rank
2424
subActionDropdown
25-
statCompsProp={[]}
2625
/>
2726
</SubscribeTerritoryContextProvider>
2827
</SubscriptionLayout>

0 commit comments

Comments
 (0)