SELECT recorded_at::date as date,

symbol,

avg(price) as price

FROM osmosis.core.dim_prices

WHERE recorded_at > '2022-01-01'

GROUP BY 1,2

), list as (

SELECT a.*, b.PROJECT_NAME, c.price, a.balance/pow(10, a.decimal)*c.price as usd_amount

FROM osmosis.core.fact_daily_balances as a JOIN osmosis.core.dim_labels as b ON a.currency = b.address JOIN

prices as c ON a.date = c.date AND b.PROJECT_NAME = c.symbol

), tbl as (

SELECT date, address, sum(usd_amount) as total_usd_balance

FROM list

GROUP BY 1,2

), weekly_balances as (

SELECT date_trunc('Week', date) as week, address, avg(total_usd_balance) as usd_balance

FROM tbl

GROUP BY 1,2

), tbl2 as (

SELECT date_trunc('Week', a.block_timestamp) as week,

LIQUIDITY_PROVIDER_ADDRESS,

amount/pow(10, decimal) as adj_amount,

currency,

b.usd_balance,

CASE

WHEN b.usd_balance < 1000 THEN '1. Crab (<1000$)'

WHEN b.usd_balance < 10000 THEN '2. Fish (1000$ - 10K$)'

WHEN b.usd_balance < 100000 THEN '3. Dolphin (10K$ - 100K$)'

WHEN b.usd_balance < 1000000 THEN '4. Shark (100K$ - 1m$)'

ELSE '5. Whale (>1m$)'

END AS bin

FROM osmosis.core.fact_liquidity_provider_actions as a JOIN weekly_balances as b ON week = b.week AND a.LIQUIDITY_PROVIDER_ADDRESS = b.address

WHERE action = 'pool_joined'

SELECT week, bin, count(DISTINCT LIQUIDITY_PROVIDER_ADDRESS)