with act1 as(

with tab1 as (

select date_trunc('month',RECORDED_AT)as month,SYMBOL,avg(PRICE)as t_price

from osmosis.core.dim_prices

group by 1,2),

tab2 as (

select ADDRESS,PROJECT_NAME

from osmosis.core.dim_labels)

select month,ADDRESS,t_price

from tab1

left join tab2

on tab1.SYMBOL=tab2.PROJECT_NAME),

act2 as (

select ADDRESS,CURRENCY,avg(BALANCE/pow(10,decimal))as avg_wallet

from osmosis.core.fact_daily_balances

group by 1,2)

select act2.ADDRESS as lpers, avg(avg_wallet) as avg_balance

, case

when avg_balance>=1 and avg_balance<=100 then '$ 1~100'

when avg_balance>100 and avg_balance<=1000 then '$ 100~1K'

when avg_balance>1000 and avg_balance<=10000 then '$ 1K~10K'

when avg_balance>10000 and avg_balance<=100000 then '$ 10K~100K'

when avg_balance>100000 and avg_balance<=1000000 then '$ 100K~1M'

when avg_balance>1000000 then '$ UP TO 1M' end as dis_bal

from act1

left join act2

on act1.ADDRESS=act2.CURRENCY

where avg_wallet is not null

and avg_wallet>=1

group by 1),

task2 as (

select date_trunc('month',BLOCK_TIMESTAMP) as month,LIQUIDITY_PROVIDER_ADDRESS,TX_ID

from osmosis.core.fact_liquidity_provider_actions

where ACTION='pool_joined'

and TX_STATUS='SUCCEEDED'