address,avg(PRICE) as price_usd

from osmosis.core.dim_prices a join osmosis.core.dim_labels b on project_name = symbol

group by day, address), daily_banalces as (

select DATE as day, a.ADDRESS,

BALANCE/pow(10, DECIMAL)*price_usd as liquidity_usd,

case WHEN liquidity_usd < 10 THEN ' $0 to $10'

WHEN liquidity_usd < 100 THEN ' $10 to $100'

WHEN liquidity_usd < 1000 THEN ' $100 to $1,000'

WHEN liquidity_usd < 10000 THEN ' $1,000 to $10,000'

wHEN liquidity_usd < 100000 THEN ' $10,000 to $100,000'

WHEN liquidity_usd < 1000000 THEN ' $100,000 to $1,000,000'

ELSE '$1,000,000 and above'

end as address_type

from osmosis.core.fact_daily_balances a join daily_prices b on a.date=b.day and a.currency=b.address), address_type_lps as (

select date(a.BLOCK_TIMESTAMP) as day,

liquidity_provider_address,

b.address_type, amount/pow(10, decimal)*price_usd as amount_usd

from osmosis.core.fact_liquidity_provider_actions a

join daily_banalces b on a.liquidity_provider_address=b.address and date(a.BLOCK_TIMESTAMP)=b.day

join daily_prices c on date(a.BLOCK_TIMESTAMP)=c.day and c.address = a.CURRENCY

where action in ('pool_joined'))

select date_trunc('month', day) as wk, address_type,

count(*) as cnt,sum(amount_usd) as amount

from address_type_lps

group by wk,address_type