select min(block_timestamp::date) as first_in, from_address, pool_name

from thorchain.liquidity_actions

where lp_action = 'add_liquidity'

group by 2,3

),

first_out as (

select min(block_timestamp::date) as first_out,

from_address, pool_name

from thorchain.liquidity_actions

where lp_action = 'remove_liquidity'

group by 2,3

),

first_out_liq as (

select sum(rune_amount_usd + asset_amount_usd) as total_liq_remove, from_address, pool_name,

date_trunc('day', block_timestamp::date) as dt

from thorchain.liquidity_actions

where lp_action = 'remove_liquidity'

group by 2,3,4

),

first_out_final as (

select first_out, ou.from_address, ou.pool_name, total_liq_remove

from first_out ou

left join first_out_liq li on

ou.from_address = li.from_address

and ou.pool_name = li.pool_name

and ou.first_out = li.dt

),

final_time as (

select first_in, fir_in.pool_name, fir_in.from_address, first_out, total_liq_remove,

DATEDIFF(day, first_in, first_out) as duration_stay_in_pool

from first_in fir_in