with contract_list as (

select

distinct to_address as address

from swell.core.fact_traces

where type ilike '%CREATE%'

and TX_SUCCEEDED

and input <> '0x'

and to_address is not null

and ORIGIN_FUNCTION_SIGNATURE <> '0x'

select

to_address as contract_address,

c.LABEL as contract_name,

count(DISTINCT tx_hash) as n_transaction,

round(sum(tx_fee),2) as total_fee_eth,

round(sum(tx_fee * p.price),2) as total_fee_usd,

count(distinct from_address) as wallets

from swell.core.fact_transactions a

join contract_list b ON a.to_address = b.address

left join swell.core.dim_labels c ON a.to_address = c.address

left join (

select

price

from crosschain.price.ez_prices_hourly

where blockchain = 'ethereum'

and is_native = 'TRUE'

qualify row_number() over (order by hour desc) = 1

) p ON TRUE

where block_timestamp::date >= current_date - 30

group by 1 , 2

order by total_fee_eth desc

limit 100