first_swap as (

select

split(pool_name,'-')[0]::string as pool,

min(block_timestamp) as first_swap

from thorchain.swaps

group by 1

),

liq_24 as (

select

day,

pool,

first_swap,

asset_price_usd,

asset_liquidity*asset_price_usd as asset_liq,

rune_liquidity*rune_price_usd as rune_liq,

asset_liq+rune_liq as total_liq

from thorchain.daily_pool_stats join first_swap on split(pool_name,'-')[0]::string=pool

where timediff(week, first_swap, day) <= 7

select *,

rank() over(partition by pool order by day) as rank

from liq_24

order by total_liq desc