KaskoazulRef_finance user stats (bots)
    Updated 2023-05-07
    with 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
    left join near.core.fact_transactions t
    on r.tx_hash = t.tx_hash
    left join near.core.fact_actions_events_function_call e
    on r.tx_hash = e.tx_hash
    where t.tx_receiver = 'v2.ref-finance.near'
    and e.method_name = 'swap'
    and status = 'Failure'
    ),