with priceTb as (

SELECT

date(hour) as p_date,

avg(price) as avax_price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol = 'WAVAX'

GROUP by 1

select

date_trunc ({{Time_Interval}}, block_timestamp) as date,

initcap(name) as Contract_Name,

count (distinct tx_hash) as tx_cnt,

count (distinct from_address) as usr_cnt,

sum (tx_fee) as fees_vol,

sum (tx_fee * avax_price) as fees_vol_usd,

avg (tx_fee) as avg_fee,

avg (tx_fee * avax_price) as avg_fee_usd,

row_number() over (partition by date order by fees_vol desc) as rank

from avalanche.core.fact_transactions join avalanche.core.fact_event_logs using(tx_hash, block_timestamp)

join avalanche.core.dim_contracts on contract_address = address

JOIN priceTb on avalanche.core.fact_transactions.block_timestamp::date = priceTb.p_date

where block_timestamp BETWEEN '{{Since_date}}' and '{{End_date}}'

GROUP by 1,2

qualify rank <= 10