select

block_timestamp :: date as day,

(

select

min(PROJECT_NAME)

from

osmosis.core.dim_labels

where

ADDRESS = CURRENCY

) as token,

count (

DISTINCT LIQUIDITY_PROVIDER_ADDRESS

) as wallet,

sum(

case when ACTION = 'pool_joined' then AMOUNT / pow(10, decimal) when ACTION = 'pool_exited' then -1 * AMOUNT / pow(10, decimal) else 0 end

) as amount_lp

from

osmosis.core.fact_liquidity_provider_actions

where

TX_STATUS = 'SUCCEEDED'

and ACTION in('pool_joined')

group by

1,

2)

count(wallet) as address,

sum (amount_lp) as sum_amount_lp,

case when amount_lp < 1 then '1.Shimp' when amount_lp >= 1

and amount_lp < 10 then '2.Crab' when amount_lp >= 10

and amount_lp < 100 then '3.Octopus' when amount_lp >= 100

and amount_lp < 1000 then '4.Fish' when amount_lp >= 1000

and amount_lp < 10000 then '5.Shark' when amount_lp >= 10000 then '6.Whale' end as type

from

lst_LPers