datavortexarbittrum
Updated 2024-11-23
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 DailyFees AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS transaction_day,
SUM(tx_fee) AS daily_fees_in_eth,
COUNT(DISTINCT TX_HASH) AS daily_transactions
FROM arbitrum.core.fact_transactions
WHERE
status = 'SUCCESS'
AND block_timestamp >= DATE_TRUNC('month', CURRENT_DATE)
AND block_timestamp < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'
GROUP BY transaction_day
),
ETHPrice AS (
SELECT
DATE_TRUNC('day', hour) AS price_day,
MEDIAN(price) AS eth_price
FROM arbitrum.price.ez_prices_hourly
WHERE
symbol = 'ETH'
AND hour >= DATE_TRUNC('month', CURRENT_DATE)
AND hour < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'
GROUP BY price_day
)
SELECT
df.transaction_day,
df.daily_transactions,
df.daily_fees_in_eth,
df.daily_fees_in_eth * ep.eth_price AS daily_fees_in_usd,
df.daily_fees_in_eth / df.daily_transactions AS avg_fee_per_transaction_in_eth,
ROUND((df.daily_fees_in_eth / df.daily_transactions) * ep.eth_price, 4) AS avg_fee_per_transaction_in_usd
FROM
DailyFees df
JOIN
ETHPrice ep
ON
QueryRunArchived: QueryRun has been archived