0xasmrCopy of Untitled Query
Updated 2022-09-20
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
›
⌄
WITH burn_raw AS(
SELECT
block_timestamp,
date_trunc('hour', block_timestamp) AS hour,
gas_used*block_header_json['base_fee_per_gas']/POW(10, 18) AS burn_fee
FROM ethereum.core.fact_blocks
WHERE block_timestamp >= '2021-08-05'
),
burn_raw_usd AS(
SELECT
b.block_timestamp,
p.price AS eth_price,
b.burn_fee
FROM burn_raw b
INNER JOIN ethereum.core.fact_hourly_token_prices p
ON b.hour = p.hour
WHERE p.symbol = 'WETH'
),
daily_burn_fee AS(
SELECT
date_trunc('day', block_timestamp) AS date,
SUM(burn_fee*eth_price) AS daily_burn_fee_usd,
SUM(burn_fee) AS daily_burn_fee_eth
FROM burn_raw_usd
GROUP BY 1
)
SELECT
date,
daily_burn_fee_usd,
SUM(daily_burn_fee_usd) OVER(ORDER BY date) AS cum_burn_fee_usd,
daily_burn_fee_eth,
SUM(daily_burn_fee_usd) OVER(ORDER BY date) AS cum_burn_fee_eth
FROM daily_burn_fee
ORDER BY 1
Run a query to Download Data