with transfer_in as (

select

RECEIVER,

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

from evmos.core.fact_transfers

where CURRENCY = 'aevmos'

and transfer_type = 'EVMOS'

group by 1

),

transfer_out as (

select sender,

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

from evmos.core.fact_transfers

where CURRENCY = 'aevmos'

and transfer_type = 'EVMOS'

group by 1

),

Common as (

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

from transfer_in join transfer_out on sender=RECEIVER

group by 1,2 having amount >0

),

all_holders as (

select RECEIVER as wallet ,

amount_in as amount

from transfer_in

where RECEIVER not in (select sender from transfer_out )

UNION

select wallet , amount from Common

),

final as (

select 'holder' as type , wallet , amount

from all_holders

select