info as (

select

trunc(x.block_timestamp,'day') as date,

count(distinct from_address) as daily_users,

sum(daily_users) over (order by date) as cum_users,

count(distinct x.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 avalanche.core.fact_transactions x

join avalanche.core.fact_event_logs y on x.tx_hash=y.tx_hash

join avalanche.core.dim_labels z on y.contract_address=z.address

where project_name='wyndblast'

and x.block_timestamp>CURRENT_DATE-30

group by 1

order by 1 asc

),

final as (

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