freemartianBedrock Daily TX & Fee
Updated 2023-08-30
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
›
⌄
-- forked from Bedrock @ https://flipsidecrypto.xyz/edit/queries/11a7bf75-809c-4e71-94b5-e49e35e670d2
with dates as (
select
timestampdiff('day', '2023-06-06 16:00:00.000', current_date) as X_days_after_bedrock,
'2023-06-06 16:00:00.000' as Bedrock_live,
current_date - (2*X_days_after_bedrock) as X_days_before_bedrock
)
SELECT
COUNT(tx_hash) AS "Number of Transactions",
SUM(tx_fee) AS "Total Fee",
AVG(tx_fee) AS "Average Fee",
-- MAX(tx_fee) AS "Maximum Fee Paid",
-- MIN(tx_fee) AS "Minimum Fee Paid",
(CASE
WHEN block_timestamp < '2023-06-06 16:00:00.000' THEN 'Before Bedrock'
when block_timestamp > '2023-06-06 16:00:00.000' then 'After Bedrock' END
) AS "Label",
date_trunc('day', block_timestamp) as time
FROM optimism.core.fact_transactions
inner join dates d
WHERE block_timestamp BETWEEN X_days_before_bedrock and current_date
GROUP BY "Label", time
ORDER BY "Label"
Run a query to Download Data