Maditotal_burnt_usd
Updated 2023-05-13Copy Reference Fork
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
›
⌄
WITH gas_eth AS (
WITH gas_us AS(
SELECT
BLOCK_TIMESTAMP,
GAS_USED as GAS_USED,
BLOCK_HEADER_JSON : base_fee_per_gas :: INTEGER AS base_fee_per_gas
FROM ethereum.core.fact_blocks
)
SELECT
date_trunc('days', BLOCK_TIMESTAMP) AS date,
ROUND(SUM(GAS_USED*base_fee_per_gas/ 1e18), 2) as burnt_gas
FROM gas_us
GROUP BY date
ORDER BY date DESC),
ethusd AS (
SELECT
date_trunc ('day', BLOCK_TIMESTAMP) as date,
AVG(AMOUNT_USD/AMOUNT) as eth_to_usd
FROM ethereum.core.ez_eth_transfers
GROUP BY date
ORDER BY date DESC)
SELECT
-- a.date as date,
-- a.burnt_gas as burnt_gas_eth,
sum(round(a.burnt_gas*b.eth_to_usd,2)) as total_gas_usd
FROM gas_eth a
LEFT JOIN ethusd b
ON a.date = b.date
--ORDER BY date DESC
Run a query to Download Data