ArioGas Guzzlers - FLOW - 3
    Updated 2022-11-16
    with Flow_Price as (
    select
    timestamp::date as date,
    avg(price_usd) as Flow_Price
    from flow.core.fact_prices
    where 1=1
    and symbol = 'FLOW'
    group by 1
    ),
    Transfer as (
    select
    block_timestamp::date as date,
    sum(amount * Flow_Price) as Transfer
    from flow.core.ez_token_transfers a join Flow_Price b on a.block_timestamp::date = b.date
    where block_timestamp >= CURRENT_DATE - 30
    and token_contract = 'A.1654653399040a61.FlowToken'
    group by 1
    ),
    tab3 as (
    select
    block_timestamp::date as date,
    sum((event_data:amount) * Flow_Price) as USD_Gas
    from flow.core.fact_events a join Flow_Price b on a.block_timestamp::Date = b.date
    where block_timestamp >= CURRENT_DATE - 30
    and event_type = 'FeesDeducted'
    and tx_succeeded = 'TRUE'
    group by 1
    )
    select
    a.date,
    a.USD_Gas/b.Transfer * 1e6 as average_per_1M
    from tab3 a join Transfer b on a.date = b.date


    Run a query to Download Data