with call as (

-- /extended/v1/microblock

-- returns latest microblocks and associated metadata

select

live.udf_api(

'GET',

'https://lingering-frequent-uranium.stacks-mainnet.quiknode.pro/0f69bc55d00c3ae9bc85679fd2ee81c835c40abe/extended/v1/microblock?limit=200',

{ },

{ }

) as res

),

transform as (

SELECT

VALUE:block_height :: INT as block_height,

VALUE:parent_burn_block_time_iso :: TIMESTAMP_NTZ as block_timestamp,

VALUE:microblock_sequence :: INT as microblock_sequence,

VALUE:canonical :: BOOLEAN as canonical,

VALUE:block_hash :: STRING as block_hash,

VALUE:parent_block_hash :: STRING as parent_block_hash,

ARRAY_SIZE(VALUE:txs :: ARRAY) as tx_count,

VALUE as block_header

from

call,

lateral flatten(input => res:data:results :: array)

concat_ws('-', block_height, microblock_sequence) as microblock_block_height,

block_height,

block_timestamp,

microblock_sequence,

canonical,

block_hash,

parent_block_hash,

tx_count,

avg(tx_count) over (