JonasoPIRATE staking: user
Updated 2024-09-18
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
AA as(
select block_timestamp, origin_from_address as user, to_address as pool, amount as amount, symbol, contract_address from ethereum.core.ez_token_transfers union all
select block_timestamp, origin_to_address as user, from_address as pool, 0 - amount as amount, symbol, contract_address from ethereum.core.ez_token_transfers ),
A as(
select *
from AA
where contract_address = '0x7613c48e0cd50e42dd9bf0f6c235063145f6f8dc'
and pool in ('0x6759acd57cb5ea451a3edf397734edddfc123049')
),
-- unique users
B as(
select date_trunc('day',time) as time, count(user) as user
from (select user, min(block_timestamp) as time from A group by 1)
group by 1 ),
-- active user + txs
C as(
select date_trunc('day',block_timestamp) as time, count(distinct user) as active_user, count(*) as txs
from A
group by 1),
-- total
D as(
select a.time,
active_user as "Active users",
txs as "Daily txs",
'|' "|",
user as "New users",
sum(user) over (order by a.time) - user as "Retained users",
sum(user) over (order by a.time) as "Total users"
from B as a
join C as b on a.time = b.time )
Auto-refreshes every 24 hours
QueryRunArchived: QueryRun has been archived