address,

avg(price) as price

from osmosis.core.dim_prices a

join osmosis.core.dim_labels b

on project_name = symbol

where price > 0

group by 1,2),

base2 as (select a.address,

(sum(balance) * avg(price))/pow(10,decimal) as balance_in_usd

from osmosis.core.fact_daily_balances a

join base b

on a.currency = b.address and date_trunc('day', date) = b.day

where day = (select max(date_trunc('day', date)) from osmosis.core.fact_daily_balances)

group by 1,decimal),

base3 as (select *,

case

when balance_in_usd < 5000 then 'a. Fish : Balance < 5k USD'

when balance_in_usd < 50000 then 'b. Dolphin : 5k < Balance < 50k USD'

when balance_in_usd < 100000 then 'c. Whale : 50k < Balance < 100k USD'

else 'd. Humpback Whale : Balance > 100k USD'

end as categories

from base2),

base4 as (select a.address,

b.liquidity_provider_address as address2,

case

when address2 is null then 'Never LP-ed'

else categories

end as categories,

count(distinct(tx_id)) as tx_count,

1 as numbering

from base3 a

left join osmosis.core.fact_liquidity_provider_actions b