with table1 as (
select
pool_address,
pool_name
from ethereum.dex_liquidity_pools
where platform LIKE'sushiswap'
), table2 as (
select
pool_name,
ifnull(sum(amount_usd),0) as total_balance
from ethereum.erc20_balances
join table1
on ethereum.erc20_balances.user_address = table1.pool_address
where balance_date = date_trunc('day',getdate())
and user_address in (select pool_address from table1)
group by pool_name
order by 2 desc limit 10)
select
table2.pool_name,
0.003*sum(amount_usd) as sum_07_day_fees
from ethereum.dex_swaps
join table2
on ethereum.dex_swaps.pool_name = table2.pool_name
where block_timestamp > getdate() - interval'7 days' and platform LIKE 'sushiswap'
group by 1