Rodolfo-LimaAVG Gas Fee TX - Optimism
Updated 2022-09-27
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 TX_GAS AS (
SELECT
HOUR_TIME::DATE AS DATE,
--BLOCK_TIMESTAMP::MINUTE AS MINUTE,
AVG(GAS_PRICE) AS AVG_GAS_PRICE, --IN GWEI
AVG(GAS_LIMIT) AS AVG_GAS_LIMIT, -- UNIT OF GAS
AVG(GAS_USED) AS AVG_GAS_USED, -- UNIT OF GAS
AVG(TX_FEE) AS AVG_TX_FEE, -- ETH VALUE
AVG(TX_FEE_USD) AS AVG_TX_FEE_USD --USD VALUE
FROM
(SELECT
DATE_TRUNC('hour',TX.BLOCK_TIMESTAMP) AS HOUR_TIME,
TX.TX_HASH,
TX.GAS_PRICE,
TX.GAS_USED,
TX.GAS_LIMIT,
TX.TX_FEE,
TX.STATUS,
P.PRICE,
P.PRICE * TX.TX_FEE AS TX_FEE_USD
FROM
optimism.core.fact_transactions TX
INNER JOIN ethereum.core.fact_hourly_token_prices P ON DATE_TRUNC('hour',TX.BLOCK_TIMESTAMP) = P.HOUR
WHERE P.SYMBOL = 'WETH'
ORDER BY 1)
WHERE TX_HASH IS NOT NULL
AND STATUS = 'SUCCESS'
GROUP BY 1
ORDER BY 1 ASC)
SELECT
GAS.DATE,
D.DAY_OF_WEEK_NAME,
-- Gas price
AVG_GAS_PRICE, --IN GWEI
AVG_GAS_LIMIT, -- UNIT OF GAS
Run a query to Download Data