DATE(block_timestamp) AS dayz,

tx_hash,

pool_name,

concat(symbol_in, '-', symbol_out) as swap_pairs,

lp_fee,

lp_fee_usd,

from optimism.velodrome.ez_swaps),

token_price as (select

DATE(hour) as dt,

symbol,

AVG(price) as average_price

from optimism.core.fact_hourly_token_prices

group by 1, 2 ),

details as (select

*,

lp_fee * average_price as fee_usd

from swap_trans

left join token_price ON dayz = dt AND lp_fee_symbol = symbol )

select

lp_fee_symbol,

sum(fee_usd) as fee_collected,

sum(fee_collected) over () as total_fee_collected

from details

group by 1

having fee_collected is not null

order by 2 DESC