with users as (select

TX_HASH, actions:predecessor_id as TX_SIGNER , BLOCK_TIMESTAMP

from

near.core.fact_receipts

where

BLOCK_TIMESTAMP::date > '2022-12-18'

and RECEIVER_ID = 'nethmap.near'

and ACTIONS:receipt:Action:actions[0]:FunctionCall:method_name::string = 'set'

)

, receipts as (

select a.*, a.actions:predecessor_id as TX_SIGNER

from near.core.fact_receipts a JOIN users b on (a.actions:predecessor_id = b.TX_SIGNER or a.RECEIVER_ID= b.TX_SIGNER)

where

a.BLOCK_TIMESTAMP::date> '2022-12-18'

,fail as (

select * from near.core.fact_receipts

where tx_hash in (select tx_hash from receipts )

and STATUS_VALUE:Failure is not null

),

success_receipts as (

select * from receipts where tx_hash not in (select tx_hash from fail )

, call as (

select a.*, b.RECEIVER_ID,b.TX_SIGNER

from near.core.fact_actions_events_function_call a JOIN success_receipts b on a.tx_hash = b.tx_hash

where a.BLOCK_TIMESTAMP::date> '2022-12-18'

and METHOD_NAME = 'setup'

and ARGS:eth_address is not null

)

,eth as (

select TX_SIGNER , ARGS:eth_address as eth_addres from call

where RECEIVER_ID =TX_SIGNER