SELECT

date_trunc('day', hour) as p_date,

avg(close) as price

FROM

crosschain.price.fact_prices_ohlc_hourly

where

ASSET_ID = '1027'

group by

1

),

block_data AS (

SELECT

BLOCK_NUMBER,

BLOCK_TIMESTAMP,

(

(block_header_json:baseFeePerGas :: int) / POW(10, 18)

) * GAS_USED as eth_burned_per_block

FROM

ethereum.core.fact_blocks

WHERE

BLOCK_NUMBER >= 12965000 -- EIP-1559 implementation block

),

Daily_Data as(

SELECT

BLOCK_TIMESTAMP :: date as date,

SUM(eth_burned_per_block) AS ETH_Amount,

SUM(eth_burned_per_block) * price as usd_amount

FROM

block_data d

inner join price_tb p on p.p_date = d.BLOCK_TIMESTAMP :: date

GROUP BY

1,price

),

total_eth_burned as (

SELECT