address,

avg(PRICE) as price_usd

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

group by 1, 2

),

daily_banalces as (select DATE as day,--credit to farhadi.navid@gmail.com

a.ADDRESS,

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

case when balance_usd<=1000 then 'Shrimp'

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

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

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

else 'Whale'

end as address_type

from osmosis.core.fact_daily_balances a join price b on a.date=b.day and a.currency=b.address

)

select date_trunc('day', day) as date,

address_type,

count(*) as lp_providers,

sum(amount_usd) as amount

from (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 price c on date(a.BLOCK_TIMESTAMP)=c.day and c.address = a.CURRENCY

where action in ('pool_joined')

)

group by 1, 2