HadisehOvertime Markets - Volume 1
    Updated 2022-08-26
    with t1 as (select DISTINCT ORIGIN_FROM_ADDRESS as unique_users
    from optimism.core.fact_token_transfers
    where BLOCK_TIMESTAMP::date > '2022-08-10'
    and ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
    ),
    t2 as(
    select tx_hash
    from optimism.core.fact_token_transfers
    where FROM_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
    and ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
    and contract_address != '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'
    and ORIGIN_FROM_ADDRESS in ( select unique_users from t1 )
    and TO_ADDRESS in ( select unique_users from t1 )
    and BLOCK_TIMESTAMP::date > '2022-08-10'),
    t3 as (
    select sum(TX_JSON:receipt:logs[0]:decoded:inputs:value/ pow(10,18))as value, tx_json:receipt:logs[1]:address as game_name,
    CASE
    when TX_JSON:receipt:logs[0]:address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'
    then 'susd' else null
    end as currency
    from optimism.core.fact_transactions
    where TX_JSON:receipt:logs[0]:address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'
    and tx_hash in (SELECT tx_hash from t2)
    and BLOCK_TIMESTAMP::date > '2022-08-10'
    group by currency, game_name order by value desc),

    t4 as (
    select sum(TX_JSON:receipt:logs[0]:decoded:inputs:value/ pow(10,6))as value,
    tx_json:receipt:logs[10]:address as game_name,
    CASE when TX_JSON:receipt:logs[0]:address = '0x7f5c764cbc14f9669b88837ca1490cca17c31607'
    then 'usdc' else null
    end as currency
    from optimism.core.fact_transactions
    where BLOCK_TIMESTAMP::date > '2022-08-10'
    Run a query to Download Data