tubaecciProfits 3
Updated 2025-01-06
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 Profits 2 @ https://flipsidecrypto.xyz/studio/queries/08b8e543-9558-4498-9c62-be4abeecda3a
-- forked from Profits @ https://flipsidecrypto.xyz/studio/queries/ea81bd2b-e51d-4a31-83bf-c442f88d32e4
WITH revenues AS(
WITH optimism AS(
WITH op AS(
SELECT
block_timestamp,
to_address,
tx_hash,
tx_fee
FROM optimism.core.fact_transactions
WHERE block_timestamp >= TIMESTAMP '2024-01-01'
)
SELECT
DATE_TRUNC ('{{period}}', block_timestamp) AS date,
'optimism' AS chain,
ROUND(SUM(tx_fee), 0) AS revenue_eth,
ROUND(SUM(tx_fee * price), 0) AS revenue_usd,
COUNT(*) AS transactions
FROM op AS a
LEFT JOIN optimism.price.ez_prices_hourly AS p ON DATE_TRUNC('hour', block_timestamp) = p.hour AND symbol = 'ETH'
GROUP BY 1
)
SELECT *
FROM optimism
),
costs AS(
WITH optimism AS(
WITH op AS(
SELECT
block_timestamp,
to_address,
tx_hash,
l1_fee