adriaparcerisasGnosis 3
Updated 2023-03-15
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
info as (
select
trunc(block_timestamp,'day') as date,
count(distinct from_address) as daily_users,
sum(daily_users) over (order by date) as cum_users,
count(distinct tx_hash) as daily_transactions,
sum(daily_transactions) over (order by date) as cum_transactions,
sum(tx_fee) as daily_fees,
sum(daily_fees) over (order by date) as cum_fees,
avg(tx_fee) as avg_fee_per_tx
from gnosis.core.fact_transactions where block_timestamp>CURRENT_DATE-30
group by 1
order by 1 asc
),
final as (
SELECT
date,
cum_users as total_users,
LAG(cum_users,1) IGNORE NULLS OVER (ORDER BY date) as last_users,
((cum_users-last_users)/cum_users)*100 as users_24h_growth,
LAG(cum_users,7) IGNORE NULLS OVER (ORDER BY date) as last_users2,
((cum_users-last_users2)/cum_users)*100 as users_7d_growth,
LAG(cum_users,30) IGNORE NULLS OVER (ORDER BY date) as last_users3,
((cum_users-last_users3)/cum_users)*100 as users_30d_growth,
cum_transactions as total_transactions,
LAG(cum_transactions,1) IGNORE NULLS OVER (ORDER BY date) as last_transactions,
((cum_transactions-last_transactions)/cum_transactions)*100 as txs_24h_growth,
LAG(cum_transactions,7) IGNORE NULLS OVER (ORDER BY date) as last_transactions2,
((cum_transactions-last_transactions2)/cum_transactions)*100 as txs_7d_growth,
LAG(cum_transactions,30) IGNORE NULLS OVER (ORDER BY date) as last_transactions3,
((cum_transactions-last_transactions3)/cum_transactions)*100 as txs_30d_growth
from info
)
SELECT
total_users as "Total users last 30D",
Run a query to Download Data