saeedmznA vs B - fees paid for failed transactions
Updated 2022-07-03
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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