damidezNew user
Updated 2024-10-03
99
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
›
⌄
WITH FirstTransaction AS (
SELECT
decoded_log:sender AS Sender,
MIN(Block_timestamp) AS First_Transaction_Date
FROM bsc.core.fact_decoded_event_logs
WHERE contract_address = '0x18b2a687610328590bc8f2e5fedde3b582a49cda'
AND event_name IN ('BetBull', 'BetBear')
AND block_timestamp >= '2021-08-26' -- Platform launch date
GROUP BY Sender
),
NewUsers AS (
SELECT
Sender,
First_Transaction_Date
FROM FirstTransaction
WHERE First_Transaction_Date >= '2024-01-01'
)
SELECT
DATE_TRUNC('week', First_Transaction_Date) AS Date,
COUNT(DISTINCT Sender) AS Daily_New_Users,
SUM(COUNT(DISTINCT Sender)) OVER (ORDER BY DATE_TRUNC('week', First_Transaction_Date)) AS Cumulative_Users
FROM NewUsers
GROUP BY DATE_TRUNC('week', First_Transaction_Date)
ORDER BY Date DESC;
QueryRunArchived: QueryRun has been archived