Hosein-maleki-9555Daily total user and Volume on overtime markets in past two weeks
    Updated 2022-08-24
    with main_tb as (
    with tokens_tb as (
    select distinct token_ADDRESS,SYMBOL,DECIMALS
    from optimism.core.fact_hourly_token_prices
    where token_ADDRESS in ('0x7f5c764cbc14f9669b88837ca1490cca17c31607','0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9','0xda10009cbd5d07dd0cecc66161fc93d7c9000da1','0x94b008aa00579c1307b0ef2c499ad98a8ce58e58')
    and HOUR::DATE >=CURRENT_DATE-14
    )
    select date_trunc('day',block_timestamp)as date ,tokens_tb.SYMBOL as tokens_tb , sum(RAW_AMOUNT/pow(10,tokens_tb.DECIMALS)) as volume
    from optimism.core.fact_token_transfers join tokens_tb on optimism.core.fact_token_transfers.CONTRACT_ADDRESS=tokens_tb.token_ADDRESS
    where ORIGIN_FROM_ADDRESS=FROM_ADDRESS
    and ORIGIN_TO_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'
    and block_timestamp::date >=CURRENT_DATE-14
    group by date,tokens_tb.symbol
    order by date asc
    ),
    daily_users_tb as (
    select date_trunc('day',block_timestamp)as date ,count( distinct from_address ) as total_users
    from optimism.core.fact_token_transfers
    where BLOCK_TIMESTAMP::date >= CURRENT_DATE-14
    and ORIGIN_TO_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'
    group by date
    order by date asc
    )

    select date,sum(volume) as total_volume,daily_users_tb.total_users
    from main_tb
    join daily_users_tb on date = date
    group by date,total_users
    order by date asc
    Run a query to Download Data