forked from CodebuffAI/codebuff
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcalculate-weekly-usage.ts
More file actions
70 lines (61 loc) · 2.59 KB
/
Copy pathcalculate-weekly-usage.ts
File metadata and controls
70 lines (61 loc) · 2.59 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
import { db } from '@codebuff/internal/db'
import * as schema from '@codebuff/internal/db/schema'
import { sql } from 'drizzle-orm'
async function calculateWeeklyUsage() {
console.log(
'Calculating credit usage in the last 7 days (active subscribers only)...\n',
)
const sevenDaysAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000)
try {
// Get total credits used across all users with active subscriptions
const totalResult = await db
.select({
totalCredits: sql<string>`SUM(${schema.message.credits})`,
})
.from(schema.message)
.leftJoin(schema.user, sql`${schema.message.user_id} = ${schema.user.id}`)
.where(sql`${schema.message.finished_at} >= ${sevenDaysAgo}`)
const totalCredits = parseInt(totalResult[0]?.totalCredits || '0')
console.log(
`\nTotal credits used in last 7 days: ${totalCredits.toLocaleString()}`,
)
// Get credits used per user with active subscription
const userResults = await db
.select({
userId: schema.message.user_id,
email: schema.user.email,
userCredits: sql<string>`SUM(${schema.message.credits})`,
})
.from(schema.message)
.leftJoin(schema.user, sql`${schema.message.user_id} = ${schema.user.id}`)
.where(sql`${schema.message.finished_at} >= ${sevenDaysAgo}`)
.groupBy(schema.message.user_id, schema.user.email)
.orderBy(sql`SUM(${schema.message.credits})` as any, 'desc' as any)
console.log('\nUsage by active subscribers:')
for (const result of userResults) {
const credits = parseInt(result.userCredits)
const user = result.email || result.userId || 'Anonymous'
console.log(`${user}: ${credits.toLocaleString()} credits`)
}
// Get credits used per day for active subscribers
const dailyResults = await db
.select({
date: sql<string>`DATE(${schema.message.finished_at})`,
dailyCredits: sql<string>`SUM(${schema.message.credits})`,
})
.from(schema.message)
.leftJoin(schema.user, sql`${schema.message.user_id} = ${schema.user.id}`)
.where(sql`${schema.message.finished_at} >= ${sevenDaysAgo}`)
.groupBy(sql`DATE(${schema.message.finished_at})`)
.orderBy(sql`DATE(${schema.message.finished_at})` as any)
console.log('\nDaily usage (active subscribers only):')
for (const result of dailyResults) {
const credits = parseInt(result.dailyCredits)
console.log(`${result.date}: ${credits.toLocaleString()} credits`)
}
} catch (error) {
console.error('Error calculating weekly usage:', error)
}
}
// Run the script
calculateWeeklyUsage()