RECORDED_AT::date as date,

address,

avg(PRICE) as AVG_Price

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

where RECORDED_AT >= current_date - 1

group by 1,2

),

balances as (

select

a.date,

a.ADDRESS,

BALANCE/pow(10,decimal) * AVG_Price as Balance_USD,

CASE

when Balance_USD < 50 then 'Tier 1: 0-50$'

when Balance_USD >= 50 and Balance_USD < 500 then 'Tier 2: 50-500$'

when Balance_USD >= 500 and Balance_USD < 5000 then 'Tier 3: 500-5K$'

when Balance_USD >= 5000 and Balance_USD < 50000 then 'Tier 4: 5K-50K$'

else 'Tier 5: Whale'

end as status

from osmosis.core.fact_daily_balances a join Price b on a.CURRENCY = b.address

where a.DATE >= current_date - 1

and BALANCE_TYPE = 'liquid'

select

BLOCK_TIMESTAMP::date as date,

status,

count(distinct TX_ID) as "# TXs",

sum("# TXs") over(partition by status order by BLOCK_TIMESTAMP::date) as "Total # TXs"

from osmosis.core.fact_liquidity_provider_actions join balances on LIQUIDITY_PROVIDER_ADDRESS = ADDRESS

where BLOCK_TIMESTAMP >= CURRENT_DATE - 30 and BLOCK_TIMESTAMP < CURRENT_DATE

and TX_STATUS = 'SUCCEEDED'

and ACTION = 'pool_joined'

group by 1,2

order by status asc