sinahosseinzadehUntitled Query
    Updated 2022-08-08
    with pricet as (select hour::date as date,avg(price) as USDPrice from optimism.core.fact_hourly_token_prices where token_address ='0x4200000000000000000000000000000000000042'
    and hour >= '2022-07-01' and hour < '2022-08-01'
    group by 1),

    btc_price as (select
    hour::date as date, avg(price) as btcprice
    from ethereum.core.fact_hourly_token_prices
    where symbol = 'WBTC' and date >= '2022-07-01' and date < '2022-08-01'
    group by 1),

    eth_price as (
    select hour::date as date, avg(price) as ethprice
    from ethereum.core.fact_hourly_token_prices
    where symbol = 'WETH'
    and date >= '2022-07-01' and date < '2022-08-01'
    group by 1)

    select block_timestamp::date,
    status,
    btcprice,
    ethprice,
    USDPRice as OPPrice,
    count (distinct tx_hash) as Optimism_Transactions,
    sum (optimism_transactions) over (partition by status order by block_timestamp::date) as Cumulative_Optimism_Transactions,
    count (distinct from_address) as Optimism_Users,
    sum (tx_fee) as OP_Fee,
    sum (tx_fee*USDPrice) as USD_Fee
    from optimism.core.fact_transactions t1 join pricet t2 on t2.date = t1.block_timestamp::Date
    join btc_price t3 on t1.block_timestamp::date = t3.date
    join eth_price t4 on t1.block_timestamp::date = t4.date
    where block_timestamp >= '2022-07-01' and block_timestamp < '2022-08-01'
    group by 1,2,3,4,5
    order by 1
    Run a query to Download Data