with a as(

select address

,sum(balance/ pow(10,6)) as old_balance

from osmosis.core.fact_daily_balances

where CURRENCY = 'uosmo'

and date::date = '2022-01-01'

group by 1

order by 2 desc

limit 100

)

,

aa as (

select row_number() over(order by old_balance desc) as old_rank

, * from a

,

b as(

select address

,sum(balance/ pow(10,6)) as new_balance

from osmosis.core.fact_daily_balances

where CURRENCY = 'uosmo'

-- and address in (select address from a)

and date::date = CURRENT_DATE-1

group by 1

order by 2 desc

)

,

bb as(

select row_number() over(order by new_balance desc) as new_rank

, * from b

)

,

final as(

select aa.address

,old_balance