ROW_NUMBER() OVER(PARTITION BY balance_date ORDER BY liquidity desc) as rank

from ethereum.erc20_balances

where label = 'sushiswap' and label_type = 'dex' and label_subtype = 'pool' and amount_usd is not null

and balance_date >= CURRENT_DATE - 30),

total as (select balance_date, (amount_usd*2) as total_liquidity

from ethereum.erc20_balances

where label = 'sushiswap' and label_type = 'dex' and label_subtype = 'pool'

and balance_date >= CURRENT_DATE - 30)

---guna case when instead for below

select a.balance_date, a.address_name, a.liquidity, b.total_liquidity, round(((a.liquidity/b.total_liquidity)*100),2) as percentage_top

from raw a

left join total b on a.balance_date = b.balance_date