select tx_hash from near.core.fact_receipts

where Parse_json(status_value):"Failure" is not null

),

contracts as (

select distinct t.tx_receiver from near.core.fact_actions_events_function_call fc

inner join near.core.fact_transactions t

on fc.tx_hash=t.tx_hash

),

dai as

(

select

r.block_timestamp,

r.tx_hash,

fc.method_name,

'dai' as coin,

split(r.logs[0],' ')[3] as from_account,

split(r.logs[0],' ')[5] as to_account,

split(r.logs[0],' ')[1]/10e17 as amount

from near.core.fact_receipts r

inner join near.core.fact_actions_events_function_call fc

on r.tx_hash=fc.tx_hash

where r.RECEIVER_ID='6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near'

and r.logs[0] like 'Transfer %'

and fc.tx_hash not in ( select * from failed)

qualify (row_number() over (partition by r.tx_hash order by r.tx_hash desc))=1

),

outt as

select block_timestamp::date as "date", from_account, sum(amount) as amount_out from dai

group by "date",from_account

),

inn as

select block_timestamp::date as "date", to_account, sum(amount) as amount_in from dai

group by "date",to_account