pool_name as pool1,

sum(asset_amount_usd) as tvl

from thorchain.pool_block_balances

where block_timestamp>='2022-03-01'

group by 1 order by 2 desc

limit 15),

tt as(

with t1 as(select

pool_name,

from_address as add1,

block_timestamp as x1

from thorchain.liquidity_actions

where LP_ACTION='add_liquidity'

and pool_name in (select pool1 from tx)

),

t2 as(select

pool_name,

from_address as add2,

block_timestamp as x2

from thorchain.liquidity_actions

where LP_ACTION='remove_liquidity')

select

t1.add1 as address,

t1.pool_name as pools,

abs(DATEDIFF(day, x1,x2)) as average_time

from t1 inner join t2 on t1.add1=t2.add2

select pools, avg(average_time) as liquidity_hold from tt group by 1