with transfer as (

select

To_ADDRESS,

From_ADDRESS,

RAW_AMOUNT/1e18 as token

from base.core.fact_token_transfers

where CONTRACT_ADDRESS = lower ('0x0bD4887f7D41B35CD75DFF9FfeE2856106f86670')

union all

select

DECODED_LOG:recipient as To_ADDRESS,

'0x0000000000000000000000000000000000000000' as From_ADDRESS,

DECODED_LOG:amount / 1e18 as token

from base.core.ez_decoded_event_logs

where TOPICS[0] ='0x9e530c1f4e73e1ae05ad4b0464ea7f76054a087e37490636b79173927d704416'

and EVENT_NAME = 'ClaimedA'

and CONTRACT_ADDRESS = '0x0bd4887f7d41b35cd75dff9ffee2856106f86670'

),

tb1 as (SELECT

To_ADDRESS,

sum(token) as volume_receive

from transfer

group by 1),

tb2 as (SELECT

From_ADDRESS,

sum(token) as volume_sent

from transfer

group by 1),

token_holdings as (select

tb1.to_address as user,

ifnull(volume_receive,0) - ifnull(volume_sent,0) as volume_hold

from tb1

left outer join tb2 on tb1.to_address=tb2.From_ADDRESS