raw as (

select

date_trunc('hour', block_timestamp) as date,

count(distinct block_number) as total_blocks,

count(distinct tx_id) as total_transactions,

sum(size) as total_size

from

bitcoin.core.fact_transactions

where

block_number >= 840000

group by

date

),

runes as (

select

block_timestamp,

block_number,

tx_id,

fee, -- BTC

input_value,

output_value,

size, -- Byte

virtual_size, -- vByte

fee * pow(10, 8) / virtual_size as fee_rate, -- sat/vB

hex

from

bitcoin.core.fact_transactions transactions,

lateral flatten (input => outputs) outputs_flattened

where

block_number >= 840000

and hex like '%0063036f726401%'

and outputs_flattened.value:scriptPubKey:hex like '6a5d%'

)