Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit47f43d5

Browse files
committed
Add 30 day reports endpoint for Tony
1 parent3aeed51 commit47f43d5

File tree

4 files changed

+189
-0
lines changed

4 files changed

+189
-0
lines changed
Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,74 @@
1+
WITH latest_paymentAS (
2+
SELECT
3+
p.winnings_id,
4+
MAX(p.version)AS max_version
5+
FROMfinance.payment p
6+
GROUP BYp.winnings_id
7+
),
8+
recent_paymentsAS (
9+
SELECT
10+
w.winning_id,
11+
w.winner_id,
12+
w.category,
13+
w.external_idAS challenge_id,
14+
w.created_atAS winning_created_at,
15+
p.payment_id,
16+
p.payment_status,
17+
p.installment_number,
18+
p.billing_account,
19+
p.total_amount,
20+
p.gross_amount,
21+
p.challenge_fee,
22+
p.challenge_markup,
23+
p.date_paid,
24+
p.created_atAS payment_created_at
25+
FROMfinance.winnings w
26+
JOINfinance.payment p
27+
ONp.winnings_id=w.winning_id
28+
JOIN latest_payment lp
29+
ONlp.winnings_id=p.winnings_id
30+
ANDlp.max_version=p.version
31+
WHEREw.type='PAYMENT'
32+
ANDp.installment_number=1
33+
ANDp.payment_status='PAID'
34+
AND COALESCE(p.date_paid,p.created_at)>= (CURRENT_DATE- INTERVAL'3 months')
35+
)
36+
SELECT
37+
cl."name"AS customer,
38+
COALESCE(c."projectId"::text, ba."projectId")AS project_id,
39+
proj.nameAS project_name,
40+
ba.id::textAS billing_account_id,
41+
ba."name"AS billing_account_name,
42+
rp.challenge_id,
43+
c."name"AS challenge_name,
44+
c."createdAt"AS challenge_created_at,
45+
rp.winner_idAS member_id,
46+
mem.handleAS member_handle,
47+
CASE
48+
WHENrp.category::text ILIKE'%REVIEW%' THEN'review'
49+
WHENrp.category::text ILIKE'%COPILOT%' THEN'copilot'
50+
ELSE'prize'
51+
ENDAS payment_type,
52+
COALESCE(rp.gross_amount,rp.total_amount)AS member_payment,
53+
COALESCE(
54+
rp.challenge_fee,
55+
COALESCE(rp.gross_amount,rp.total_amount)* (rp.challenge_markup/100.0)
56+
)AS fee,
57+
COALESCE(rp.date_paid,rp.payment_created_at)AS payment_date
58+
FROM recent_payments rp
59+
LEFT JOIN challenges."Challenge" c
60+
ON c."id"=rp.challenge_id
61+
LEFT JOIN challenges."ChallengeBilling" cb
62+
ON cb."challengeId"= c."id"
63+
LEFT JOIN"billing-accounts"."BillingAccount" ba
64+
ON ba."id"= COALESCE(
65+
NULLIF(rp.billing_account,'')::int,
66+
NULLIF(cb."billingAccountId",'')::int
67+
)
68+
LEFT JOIN"billing-accounts"."Client" cl
69+
ON cl."id"= ba."clientId"
70+
LEFT JOINprojects.projects proj
71+
ONproj.id= c."projectId"::bigint
72+
LEFT JOINmembers.member mem
73+
ON mem."userId"::text=rp.winner_id
74+
ORDER BY payment_dateDESC,rp.payment_created_atDESC;
Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,69 @@
1+
WITH typed_challengesAS (
2+
SELECT
3+
c.id,
4+
c.name
5+
FROM challenges."Challenge"AS c
6+
WHERE c."typeId"='929bc408-9cf2-4b3e-ba71-adfbf693046c'
7+
),
8+
submission_participantsAS (
9+
SELECT DISTINCTON (s."memberId",tc.id)
10+
s."memberId"::bigintAS member_id,
11+
COALESCE(NULLIF(TRIM(m.handle),''),m.handle)AS member_handle,
12+
tc.idAS challenge_id,
13+
s.placement
14+
FROM typed_challengesAS tc
15+
JOIN reviews."submission"AS s
16+
ON s."challengeId"=tc.id
17+
LEFT JOIN members."member"AS m
18+
ON m."userId"= s."memberId"::bigint
19+
ORDER BY
20+
s."memberId",
21+
tc.id,
22+
s.placement NULLS FIRST,
23+
s.idDESC
24+
),
25+
winner_participantsAS (
26+
SELECT DISTINCTON (cw."userId",tc.id)
27+
cw."userId"::bigintAS member_id,
28+
COALESCE(
29+
NULLIF(TRIM(cw.handle),''),
30+
NULLIF(TRIM(m.handle),''),
31+
m.handle
32+
)AS member_handle,
33+
tc.idAS challenge_id,
34+
cw.placement
35+
FROM typed_challengesAS tc
36+
JOIN challenges."ChallengeWinner"AS cw
37+
ON cw."challengeId"=tc.id
38+
LEFT JOIN members."member"AS m
39+
ON m."userId"= cw."userId"::bigint
40+
ORDER BY
41+
cw."userId",
42+
tc.id,
43+
cw.placement NULLS FIRST,
44+
cw.idDESC
45+
),
46+
combined_participantsAS (
47+
SELECT
48+
COALESCE(wp.member_id,sp.member_id)AS member_id,
49+
COALESCE(wp.member_handle,sp.member_handle)AS member_handle,
50+
COALESCE(wp.challenge_id,sp.challenge_id)AS challenge_id,
51+
COALESCE(wp.placement,sp.placement)AS placement
52+
FROM submission_participantsAS sp
53+
FULL OUTERJOIN winner_participantsAS wp
54+
ONwp.member_id=sp.member_id
55+
ANDwp.challenge_id=sp.challenge_id
56+
)
57+
SELECT
58+
cp.member_idAS"memberId",
59+
cp.member_handleAS"memberHandle",
60+
COUNT(*)::intAS"uniqueChallengesSubmitted",
61+
COUNT(*) FILTER (WHEREcp.placement=1)::intAS"placementsOfOne",
62+
COUNT(*) FILTER (WHEREcp.placement BETWEEN1AND5)::intAS"placementsOneThroughFive"
63+
FROM combined_participantsAS cp
64+
GROUP BY
65+
cp.member_id,
66+
cp.member_handle
67+
ORDER BY
68+
"uniqueChallengesSubmitted"DESC,
69+
"memberHandle"ASC;

‎src/reports/topcoder/topcoder-reports.controller.ts‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,6 +69,14 @@ export class TopcoderReportsController {
6969
returnthis.reports.getWeeklyMemberParticipation();
7070
}
7171

72+
@Get("/30-day-payments")
73+
@ApiOperation({
74+
summary:"Member payments for the last 30 days",
75+
})
76+
get30DayPayments(){
77+
returnthis.reports.get30DayPayments();
78+
}
79+
7280
@Get("/90-day-member-spend")
7381
@ApiOperation({
7482
summary:"Total gross amount paid to members in the last 90 days",

‎src/reports/topcoder/topcoder-reports.service.ts‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,23 @@ type MarathonMatchStatsRow = {
2525
marathon_submission_rate:string|number|null;
2626
};
2727

28+
typeThirtyDayPaymentRow={
29+
customer:string|null;
30+
project_id:string|null;
31+
project_name:string|null;
32+
billing_account_id:string|null;
33+
billing_account_name:string|null;
34+
challenge_id:string|null;
35+
challenge_name:string|null;
36+
challenge_created_at:Date|string|null;
37+
member_id:string|null;
38+
member_handle:string|null;
39+
payment_type:string|null;
40+
member_payment:string|number|null;
41+
fee:string|number|null;
42+
payment_date:Date|string|null;
43+
};
44+
2845
@Injectable()
2946
exportclassTopcoderReportsService{
3047
constructor(
@@ -411,6 +428,27 @@ export class TopcoderReportsService {
411428
}));
412429
}
413430

431+
asyncget30DayPayments(){
432+
constquery=this.sql.load("reports/topcoder/30-day-payments.sql");
433+
constrows=awaitthis.db.query<ThirtyDayPaymentRow>(query);
434+
returnrows.map((row)=>({
435+
customer:row.customer??null,
436+
projectId:row.project_id??null,
437+
projectName:row.project_name??null,
438+
billingAccountId:row.billing_account_id??null,
439+
billingAccountName:row.billing_account_name??null,
440+
challengeId:row.challenge_id??null,
441+
challengeName:row.challenge_name??null,
442+
challengeCreatedAt:this.normalizeDate(row.challenge_created_at),
443+
memberId:row.member_id??null,
444+
memberHandle:row.member_handle??null,
445+
paymentType:row.payment_type??null,
446+
memberPayment:this.toNullableNumber(row.member_payment),
447+
fee:this.toNullableNumber(row.fee),
448+
paymentDate:this.normalizeDate(row.payment_date),
449+
}));
450+
}
451+
414452
asyncgetRegistrantCountriesCsv(challengeId:string){
415453
constquery=this.sql.load("reports/topcoder/registrant-countries.sql");
416454
constrows=awaitthis.db.query<RegistrantCountriesRow>(query,[

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp