select 'usn' as contract_address, 'USN' as symbol, 18 as decimal union --stable

select 'dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near' as contract_address, 'USDT' as symbol, 6 as decimal union -- stable

select 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' as contract_address, 'USDC' as symbol, 6 as decimal union -- stable

select '6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near' as contract_address, 'DAI' as symbol, 18 as decimal union -- stable

select 'v3.oin_finance.near' as contract_address, 'nUSDO' as symbol, 8 as decimal union -- stable

select 'cusd.token.a11bd.near' as contract_address, 'cUSD' as symbol, 24 as decimal -- stable

),

raw as (

select t.tx_hash,

t.block_timestamp,

t.tx_signer,

t.transaction_fee / pow(10,24) as fee,

e.action_name, --if function_call table, all are FunctionCall

e.method_name,

try_parse_json(e.args) as p_args,

p_args:actions[0]:amount_in as amount_in_decimals,

p_args:actions[0]:min_amount_out as amount_out_decimals,

p_args:actions[0]:pool_id as pool_id,

p_args:actions[0]:token_in as address_in,

p_args:actions[0]:token_out as address_out

from near.core.fact_transactions t

left join near.core.fact_actions_events_function_call e

on t.tx_hash = e.tx_hash

where t.tx_receiver = 'v2.ref-finance.near'

and e.method_name = 'swap'

),

failed_swaps as (

select r.tx_hash,

case

when regexp_substr(status_value, 'Failure') = 'Failure' then 'Failure'

else 'Success'

end as status

from near.core.fact_receipts r