binhachonLooking For Swap Arbitrage - Addresses with number of transactions where transaction ratio = 1
    Updated 2022-03-31
    with swap_transaction as (
    select
    tx_group_id,
    sender as address,
    0 as received_amount,
    asset_amount as sent_amount
    from algorand.asset_transfer_transaction
    where block_timestamp > '2022-01-01'
    and asset_transferred in (31566704, 312769)
    and tx_group_id is not null
    union all
    select
    tx_group_id,
    asset_receiver as address,
    asset_amount as received_amount,
    0 as sent_amount
    from algorand.asset_transfer_transaction
    where block_timestamp > '2022-01-01'
    and asset_transferred in (31566704, 312769)
    and tx_group_id is not null
    ),
    temp as (
    select
    address,
    sum(received_amount)/1e6 as received_amount,
    sum(sent_amount)/1e6 as sent_amount,
    count(distinct tx_group_id) as number_of_transactions,
    count_if(received_amount > 0) as number_of_receiving_transactions,
    count_if(sent_amount > 0) as number_of_sending_transactions
    from swap_transaction
    group by address
    ),
    overall_stats as (
    select
    *,
    received_amount/sent_amount as profit_ratio,
    Run a query to Download Data