with lp as ( select count(distinct tx_id) as add_count, from_address from thorchain.liquidity_actions where pool_name = 'ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044' and lp_action = 'add_liquidity' group by 2 ), lp_amount_add as ( select sum(rune_amount + asset_amount) as lp_amount_add, from_address from thorchain.liquidity_actions where pool_name = 'ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044' and lp_action = 'add_liquidity' and from_address in (select from_address from lp where add_count = 1 ) group by 2 ), lp_amount_remove as ( select sum(rune_amount + asset_amount) as lp_amount_remove, from_address from thorchain.liquidity_actions where pool_name = 'ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044' and lp_action = 'remove_liquidity' and from_address in (select from_address from lp where add_count = 1 ) group by 2 ), lp_amount as ( select lp_amount_add, lp_amount_remove, ad.from_address from lp_amount_add ad left join lp_amount_remove re on ad.from_address = re.from_address ), first_add as ( select min(date_trunc('day', block_timestamp::date)) as first_add, from_address from thorchain.liquidity_actions where pool_name = 'ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044' and lp_action = 'add_liquidity' group by 2 ), last_out as ( select max(date_trunc('day', block_timestamp::date)) as last_remove, from_address from thorchain.liquidity_actions where pool_name = 'ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044' and lp_action = 'remove_liquidity' group by 2 ), final as ( select lp_amount_add, lp_amount_remove, (lp_amount_add - lp_amount_remove) as current_lp, abs(current_lp) as amount_profit, first_add, last_remove, DATEDIFF('day', first_add, last_remove) as duration_add, (amount_profit / lp_amount_add / duration_add )*100 as return_per_day, ad.from_address from lp_amount am left join first_add ad on am.from_address = ad.from_address left join last_out ou on am.from_address = ou.from_address where last_remove is not null and current_lp < 0 and duration_add > 0 ) select avg(return_per_day) * 12 as avg_apy, case when duration_add < 30 then '1 month' when duration_add >= 30 and duration_add < 60 then '2 months' when duration_add >= 60 and duration_add < 90 then '3 months' when duration_add >= 90 and duration_add < 120 then '4 months' when duration_add >=120 and duration_add < 150 then '5 months' when duration_add >= 150 and duration_add < 180 then '6 months' when duration_add >= 180 and duration_add < 210 then '7 months' when duration_add >= 210 and duration_add < 240 then '8 months' when duration_add >= 240 and duration_add < 270 then '9 months' when duration_add >= 270 and duration_add < 300 then '10 months' when duration_add >= 300 and duration_add < 330 then '11 months' when duration_add >= 330 and duration_add < 360 then '12 months' when duration_add >= 360 then '>1 year' end as duration_period from final group by 2

Realized APY - ETH & THOR pool performance