zakkisyed#4 X World Games - Daily User acquisition over time
Updated 2022-10-14
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
›
⌄
-- This query finds the wallets first date of interaction with the games contracts and
-- 5. X World Games (BNB Chain)+22%
-- XVG Contract Address: 0x6b23c89196deb721e6fd9726e6c76e4810a464bc
-- 4. Monsterra (BNB Chain)+35%
-- MSTR: 0x2290C6bD9560E6498dFDf10F9eCB17997CA131f2
-- MAG: 0xd4c73fd18f732BC6EE9FB193D109B2eed815Df80
with user_data AS (
select distinct from_address as account, min(block_timestamp) as earliest_date -- choosing the earliest date the user interacted with the contract
from bsc.core.fact_transactions
where to_address = '0x6b23c89196deb721e6fd9726e6c76e4810a464bc'
group by 1
--limit 30
)
select count(*) as wallet_count, date_trunc(day,earliest_date) as dt,
sum(wallet_count) over (order by dt) as user_acquisition
from user_data
where earliest_date > current_date-{{DATE_RANGE}} --time range
group by 2
order by 1 desc
Run a query to Download Data