kidaSwapper tool
    Updated 2022-08-11
    WITH swaps AS (
    SELECT
    block_timestamp,
    tx_hash,
    tx_signer as swapper,
    dex,
    ARRAY_SIZE(swaps) > 1 as is_complex,
    F.Value as swap_details,
    REPLACE(REGEXP_SUBSTR(F.Value, '\\d+.*for'), ' for') as token_in_ori,
    SPLIT(token_in_ori, ' ')[0]::int as sell_amount,
    SPLIT(token_in_ori, ' ')[1] as sell_denom,
    REPLACE(REGEXP_SUBSTR(F.Value, 'for([^,])*'), 'for ') as token_out_ori,
    SPLIT(token_out_ori, ' ')[0]::int as buy_amount,
    SPLIT(token_out_ori, ' ')[1] as buy_denom,
    REPLACE(REGEXP_SUBSTR(F.Value, 'total fee \\d+'), 'total fee')::int as fee_amount,
    F2.Value:pool_id as pool_id
    FROM (
    SELECT
    c.block_timestamp,
    c.tx_hash,
    PARSE_JSON(args):receiver_id as receiver_id,
    NVL(receiver_id, tx_receiver) as dex,
    tx_signer,
    REGEXP_SUBSTR_ALL(tx::string, 'Swapped([^"])*') as swaps,
    PARSE_JSON(REPLACE(PARSE_JSON(args):msg::string, '\\')):actions as actions
    FROM near.core.fact_actions_events_function_call c
    JOIN near.core.fact_transactions t
    ON c.tx_hash = t.tx_hash
    WHERE method_name = 'ft_transfer_call' AND CONTAINS(args, 'pool_id') AND NOT CONTAINS(tx::string, 'Smart contract panicked')
    ),
    LATERAL FLATTEN(input => swaps) F,
    LATERAL FLATTEN(input => actions) F2
    WHERE F.index = F2.index
    )

    SELECT
    Run a query to Download Data