SpecterTx Fee $USD and Average Fee copy
Updated 2024-10-05
999
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
›
⌄
-- forked from Sajjadiii / Tx Fee $USD and Average Fee @ https://flipsidecrypto.xyz/Sajjadiii/q/-mkUjKSFGO1O/tx-fee-usd-and-average-fee
-- ('aurora' , 'sei' , 'optimism' , 'aptos' , 'arbitrum' , 'blast' , 'base' )
with klay_price AS (
SELECT hour
,price
FROM kaia.price.ez_prices_hourly
WHERE symbol = 'KLAY'
AND hour::date > current_date - interval '6 Month'
),
eth_price AS (
SELECT hour
,price
FROM ethereum.price.ez_prices_hourly
WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
AND hour::date > current_date - interval '6 Month'
),
apt_price AS (
SELECT hour
,price
FROM aptos.price.ez_prices_hourly
WHERE token_address = '0x1::aptos_coin::AptosCoin'
AND hour::date > current_date - interval '6 Month'
)
SELECT date_trunc('month', block_timestamp) AS date
,'Aurora' AS blockchain
,sum(tx_fee * price) AS tx_fee
,avg(tx_fee * price) AS avg_tx_fee
FROM aurora.core.fact_transactions a
JOIN eth_price b on date_trunc('hour', block_timestamp) = b.hour
WHERE block_timestamp::date > current_date - interval '6 Month'
AND status = 'SUCCESS'
QueryRunArchived: QueryRun has been archived