select t.tx_hash,

t.block_timestamp,

t.tx_signer,

--t.tx,

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

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

e.method_name,

--e.deposit / pow(10,24) as deposit,

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,

regexp_substr(r.status_value, 'Failure') as reg_failure

from near.core.fact_transactions t

left join near.core.fact_actions_events_function_call e

on t.tx_hash = e.tx_hash

left join near.core.fact_receipts r

on t.tx_hash = r.tx_hash

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

and e.method_name = 'swap'

and reg_failure is null

),

first_swaps as (

select tx_signer as user,

case

when length (user) = 64 then 'implicit'

else 'human-readable'

end as type_account,

min (block_timestamp) as first_swap

from raw

group by 1,2