SELECT

BLOCK_NUMBER,

COALESCE(SUM(tx_fee), 0) AS tx_fee

FROM

bsc.core.fact_transactions

GROUP BY

BLOCK_NUMBER

),

BlockTotals AS (

SELECT

BLOCK_NUMBER,

SUM(

COALESCE(BLOCK_HEADER_JSON['baseFeePerGas'], 0) * BLOCK_HEADER_JSON['gasUsed']

) / 1e18 AS burned

FROM

bsc.core.fact_blocks

GROUP BY

BLOCK_NUMBER

)

SELECT

COALESCE(SUM(tt.tx_fee), 0) AS total_tx_fee,

COALESCE(SUM(bt.burned), 0) AS total_burned

FROM

TransactionTotals tt

LEFT JOIN

BlockTotals bt ON tt.BLOCK_NUMBER = bt.BLOCK_NUMBER