FROM thorchain.liquidity_actions

where lp_action LIKE 'add_liquidity'

GROUP by 1,2),

thor_remove as (SELECT from_address as wallet, pool_name as pool, sum(-1 * stake_units) as lp_amount2

FROM thorchain.liquidity_actions

where lp_action LIKE 'remove_liquidity'

GROUP by 1,2),

total as (SELECT thor_add.wallet, thor_add.pool, sum(lp_amount1+lp_amount2) as total_lp

FROM thor_add, thor_remove

where thor_add.wallet = thor_remove.wallet and thor_add.pool = thor_remove.pool

GROUP by 1,2),

percentage as (SELECT pool_name, avg((rune_amount_usd + asset_amount_usd)/stake_units) as proportion

FROM thorchain.liquidity_actions

WHERE block_timestamp > CURRENT_DATE - 1

GROUP by 1),

liqui_amount as (SELECT total.wallet, total.pool, total_lp * proportion as liquidity

FROM total, percentage where total.pool = percentage.pool_name),

sum_table as (SELECT from_address, min(block_timestamp::date) as min_day

FROM thorchain.liquidity_actions

GROUP BY 1),

final as (SELECT wallet, pool, liquidity, DATEDIFF('day', min_day, CURRENT_DATE) as lp_duration

FROM liqui_amount, sum_table where liqui_amount.wallet = sum_table.from_address)

SELECT pool, avg(lp_duration) as avg_lp_duration,

case when liquidity < 5000 then 'Small_group'

when liquidity >= 5000 and liquidity < 10000 then 'Middle_group'

when liquidity >=10000 and liquidity < 15000 then 'Large_group' else 'Very_large_group' end as type

FROM final

GROUP BY 1,3