select

project_name,

sum(gas_used) as gas_spent

from avalanche.core.fact_transactions x

join avalanche.core.fact_decoded_event_logs z on x.tx_hash=z.tx_hash

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

where x.block_timestamp >= CURRENT_DATE - interval '{{period_of_days}} days'

and label_type!='dapp'

group by 1

order by 2 desc

limit 10

select

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

project_name,

count(distinct x.tx_hash) as n_transactions,

sum(n_transactions) over (partition by project_name order by date) as total_transactions,

count(distinct from_address) as users,

sum(users) over (partition by project_name order by date) as total_users,

sum(tx_fee) as avax_fees ,

avg(tx_fee) as avg_avax_fees ,

sum(gas_used) as gas_spent,

avg(gas_used) as avg_gas_spent,

sum(avax_fees) over (partition by project_name order by date asc) as cum_fees,

sum(gas_spent) over (partition by project_name order by date asc) as cum_gas

from avalanche.core.fact_transactions x

join avalanche.core.fact_decoded_event_logs z on x.tx_hash=z.tx_hash

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

where date >= CURRENT_DATE - interval '{{period_of_days}} days'

and project_name in (select distinct project_name from top10)

group by 1,2

order by 1 asc