bachiovetime profit1
    Updated 2022-08-26
    with table_1 as (select origin_from_address,
    sum(raw_amount/1e18) as usd_deposit
    from optimism.core.fact_token_transfers
    where block_timestamp >= current_date - 14
    and raw_amount > 0
    and contract_address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' --sUSD
    and to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1' --overtime markets
    and from_address != '0x0000000000000000000000000000000000000000'
    group by 1),
    --and tx_hash = '0x2072d57af9eb255993f0307d9713a2518ba7686cee15a47d6af1ba07031fa865'

    table_2 as (select origin_from_address,
    sum(raw_amount/1e18) as usd_withdrew
    from optimism.core.fact_token_transfers
    where block_timestamp >= current_date - 14
    and raw_amount > 0
    and contract_address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' --sUSD
    and from_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1' --overtime markets
    and from_address != '0x0000000000000000000000000000000000000000'
    group by 1)

    select ifnull(a.origin_from_address, b.origin_from_address) as users,
    ifnull(usd_deposit,0)-ifnull(usd_withdrew,0) as net_profit,
    row_number () over (order by net_profit desc) as count
    from table_1 a
    left join table_2 b on a.origin_from_address = b.origin_from_address
    order by 2 desc
    --limit 5
    Run a query to Download Data