with

base as (select hour::date as date,

avg (price) as price

from ethereum.core.fact_hourly_token_prices

where symbol ilike 'wETH'

group by 1)

, fin as (select

date_trunc('week', block_timestamp)::date as date ,

label ,

sum (tx_fee*price) as total_fee_usd

from Ethereum.core.fact_transactions

join base on block_timestamp::Date = date

join ethereum.core.dim_labels on TO_ADDRESS = address

where block_timestamp >= CURRENT_DATE - 90

and label is not null

and not LABEL_TYPE = 'token'

group by 1,2)

select

date ,

label ,

total_fee_usd ,

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

from fin

qualify rank <=5