select

distinct date_trunc('month', block_timestamp) as time,

pool_name,

from_address

from thorchain.liquidity_actions

qualify row_number() over (partition by pool_name, from_address order by time) = 1

),

stake_unit as (

select

pool_name,

from_address,

sum(case when lp_action = 'add_liquidity' then stake_units else -stake_units end) as current_stake_units

from thorchain.liquidity_actions

group by 1, 2

),

addresses_with_stake_units as (

select

first_deposit_transactions.*,

current_stake_units,

ratio_to_report(current_stake_units) over (partition by first_deposit_transactions.pool_name) as percent_of_pool

from first_deposit_transactions

inner join stake_unit on (stake_unit.pool_name = first_deposit_transactions.pool_name and first_deposit_transactions.from_address = stake_unit.from_address)

where current_stake_units >= 0

select

time,

count_if(current_stake_units > 0) * 100 / count(*) as retention_rate

from addresses_with_stake_units

group by 1