saeedmznA vs B - fees paid for failed transactions
    Updated 2022-07-03
    with averageBNB_price as (select
    avg(TOKEN0_PRICE) as bnb_price
    from flipside_prod_db.uniswapv3.swaps
    where POOL_NAME ilike '%BNB-WETH%'
    and BLOCK_TIMESTAMP::date >= CURRENT_DATE -7
    and TOKEN0_SYMBOL = 'WBNB'
    and TOKEN1_SYMBOL = 'WETH'
    ),
    ethprice as (
    select date_trunc(day,HOUR) as date ,
    avg(PRICE) as eth_price
    from ethereum.core.fact_hourly_token_prices
    where SYMBOL = 'WETH'
    and date_trunc(day,HOUR) >= CURRENT_DATE -7
    group by 1
    ),
    prices as (
    select date , eth_price , bnb_price
    from averageBNB_price join ethprice
    group by 1,2,3
    ),
    BSC as (
    select date_trunc(day,BLOCK_TIMESTAMP) as date ,
    -- STATUS,
    sum (TX_FEE ) as fee ,
    sum (TX_FEE*bnb_price) as fee_USD
    from bsc.core.fact_transactions join prices on date =BLOCK_TIMESTAMP::date
    where BLOCK_TIMESTAMP::date >= CURRENT_DATE -7
    and STATUS != 'SUCCESS'
    group by 1
    ),
    arb_tx as(
    select date_trunc(day,BLOCK_TIMESTAMP) as date ,
    tx_hash ,
    STATUS,
    tokenflow_eth.hextoint(tx_json:receipt:effectiveGasPrice)*pow(10,-18)*tokenflow_eth.hextoint(tx_json:receipt:gasUsed) as tx_fee
    Run a query to Download Data