from thorchain.liquidity_actions

where lp_action = 'add_liquidity' and from_address is not null

group by 1,2),

remove_lp as (select from_address, pool_name, sum(rune_amount_usd + asset_amount_usd) as total_removed

from thorchain.liquidity_actions

where lp_action = 'remove_liquidity' and from_address in (select from_address from add_lp)

group by 1,2),

final as (select a.from_address, a.pool_name, a.total_supplied, b.total_removed, CASE

when b.total_removed is null then 0

else b.total_removed end as rem_lp,

(a.total_supplied - rem_lp) as current_lp_position_usd

from add_lp a

left outer join remove_lp b on a.from_address = b.from_address and a.pool_name = b.pool_name),

lasts as (select from_address, count(distinct pool_name) as current_pool, sum(current_lp_position_usd) as total_volume_lp

from final

group by 1

having total_volume_lp > 1),

bruh as (select from_address, max(date(block_timestamp)) as last_lp_tx, (CURRENT_DATE - last_lp_tx) as Days_Since_Last_Tx

from thorchain.liquidity_actions

where from_address in (select from_address from lasts)

group by 1)

select days_since_last_tx, count(distinct from_address) as address_count

from bruh

group by 1