project_name,

address,

avg(price) AS pricedaily

FROM osmosis.core.dim_prices INNER JOIN osmosis.core.dim_labels

ON project_name = symbol

GROUP BY 1,2,3

),

add as (select LIQUIDITY_PROVIDER_ADDRESS as LP_add,--credit to kianrequiem

SUM(amount/pow(10, decimal)*pricedaily) AS join_amount

from osmosis.core.fact_liquidity_provider_actions

inner join price on address = currency and block_timestamp::date = price_day

where pool_id in ('498', '2', '584', '611', '3', '481', '585', '773', '10')

and action = 'pool_joined'

group by 1

),

remove as (select LIQUIDITY_PROVIDER_ADDRESS as LP_remove,

SUM(amount/pow(10, decimal)*pricedaily) AS remove_amount

from osmosis.core.fact_liquidity_provider_actions

inner join price on address = currency and block_timestamp::date = price_day

where pool_id in ( '498', '2', '584', '611', '3', '481', '585', '773', '10')

and action = 'pool_exited'

group by 1

),

sum as (select LP_add, join_amount - remove_amount as Liquidity,

case when Liquidity<=1000 then 'Shrimp'

when Liquidity>1000 and Liquidity<=10000 then 'Fish'

when Liquidity>10000 and Liquidity<=100000 then 'Dolphin'

when Liquidity>100000 and Liquidity<=1000000 then 'Shark'

else 'Whale'

end as Liquidity_category

from add inner join remove on LP_add = LP_remove

where liquidity_category != 'Others'

)