purifNew and returning users
Updated 2024-12-28
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
27
28
29
30
31
32
33
34
35
36
›
⌄
with activity as (
select date_trunc('day',block_timestamp) as day, from_address as user from berachain.testnet.fact_transactions
where (to_address=lower('0x5aD441790c3114e0AB27816abdB0c9693cd96399') or to_address=lower('0xd2fe6dc3fae1d60a20AeB4E8509FDF4740393150'))-- queue
UNION ALL
select date_trunc('day',block_timestamp) as day, from_address as user from berachain.testnet.fact_transactions
where (to_address=lower('0x6679732D6C09c56faB4cBf589E01F5e41A2d9e67') or to_address=lower('0x0E65eb38C95E664c202C0d194bf6Bd8a586BB1f0')) --factory
UNION ALL
select date_trunc('day',block_timestamp) as day, from_address as user from berachain.testnet.fact_transactions
where to_address=lower('0x12cF1dC4A8d66187202511a706E90Dfb7BE8a80C') --tools
),
unique_users as (
select count(distinct user) as total_unique from activity
),
new_wallets AS (
select first_transaction as day, count(user) as new_users from (
SELECT
user,
MIN(day) AS first_transaction
FROM activity
GROUP BY 1
)
GROUP BY 1
),
stats AS (
SELECT
day,
COUNT(DISTINCT user) AS unique_wallets
FROM activity
GROUP BY 1
)
select day, new_users, returning_users, total_unique
from unique_users, (
SELECT
s.day,
COALESCE(new_users,0) AS new_users,
QueryRunArchived: QueryRun has been archived