SELECT

block_number,

miner

FROM ethereum.core.fact_blocks

WHERE block_number BETWEEN 21846077 AND (21846077 + 10)

),

transaction AS (

SELECT

block_number,

from_address,

to_address,

value_precise

FROM ethereum.core.fact_transactions

WHERE origin_function_signature = '0x'

AND block_number BETWEEN 21846077 AND (21846077 + 10)

),

TransactionDetails AS (

SELECT

ft.block_number,

((ft.EFFECTIVE_GAS_PRICE - (CAST(GET(fb.block_header_json, 'baseFeePerGas') AS FLOAT) / 1e9)) * ft.gas_used) / 1e9 AS tips

FROM ethereum.core.fact_transactions ft

JOIN ethereum.core.fact_blocks fb ON ft.block_number = fb.block_number

WHERE ft.block_number BETWEEN 21846077 AND (21846077 + 10)

),

MEV_per_block AS (

SELECT

b.block_number,

t.value_precise AS mev_reward_for_block

FROM blocks b

JOIN transaction t ON b.block_number = t.block_number AND t.from_address = b.miner

),

Tips_per_block AS (

SELECT

block_number,

SUM(tips) as block_tips