select

RECEIVER

,sum(amount /pow(10,decimal)) as amount_in

from osmosis.core.fact_transfers

WHERE block_timestamp::date >='2021-01-01'

and transfer_type = 'OSMOSIS'

and CURRENCY = 'uosmo'

group by 1

order by 1

)

,send as (

select

sender

,sum(amount /pow(10,decimal)) as amount_out

from osmosis.core.fact_transfers

where block_timestamp::date >='2021-01-01'

and CURRENCY = 'uosmo'

and transfer_type = 'OSMOSIS'

group by 1

order by 1

),

Common as (

select sender as wallet , (amount_in - amount_out ) as amount

from recive join send on sender=RECEIVER

group by 1,2 having amount >0

),

holders as (

select RECEIVER as wallet ,

amount_in as amount

from recive

where RECEIVER not in (select sender from send )

UNION

select wallet , amount from Common

, lst_holder as (