from ethereum.core.fact_hourly_token_prices

where symbol = 'WAVAX'

and hour >= current_date - {{N_days}}

group by 1)

,

transaction as ( select date(block_timestamp) as date,project_name, tx_hash, tx_fee, tx_fee*avg_price as tx_fee_usd, gas_used

from avalanche.core.fact_transactions a left outer join price b on a.block_timestamp::date = b.date

join avalanche.core.dim_labels d on a.to_address = d.address

where block_timestamp >= current_date - {{N_days}})

,

final as ( select date, project_name, count(DISTINCT(tx_hash)) as total_tx, sum(tx_fee) as total_fee_avax, avg(tx_fee) as avg_fee,

median(tx_fee) as median_fee, max(tx_fee) as max_fee, sum(tx_fee_usd) as total_fee_usd,

median(tx_fee_usd) as median_fee_usd, max(tx_fee_usd) as max_fee_usd, avg(tx_fee_usd) as avg_fee_usd, avg(gas_used) as avg_gas_used,

sum(total_fee_avax) over (partition by project_name order by date asc) as cum_fee_avax, sum(total_fee_usd) over (partition by project_name order by date asc) as cum_fee_usd

from transaction

group by 1,2 )

select date, count(DISTINCT(project_name)) as total_project, avg(total_tx) as avg_tx, avg(avg_fee) as avg_fee_avax,

avg(avg_fee_usd) as avg_fees_usd, median(median_fee_usd) as median_fees_usd, max(max_fee_usd) as max_fees_usd,

median(median_fee) as median_fee_avax

from final

group by 1