raw as (

select

block_timestamp::date 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 > 767429

group by

date

),

ordinals 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,

outputs[0]:scriptPubKey:address as minter,

hex

from

bitcoin.core.fact_transactions

where

block_number > 767429

and hex like '%0063036f726401%'

)

block_timestamp::date as "Date",

count(distinct block_number) as "Blocks",