, percentile_cont(.50) within group (order by blockTime) over (partition by NULL) as medianBlockTime

, percentile_cont(.50) within group (order by tps) over (partition by NULL) as medianTPS

from (

select *

, TIMESTAMPDIFF(MILLISECOND, coalesce(lag(timestamp) over (order by block_number), timestamp)::timestamp, timestamp::timestamp) / 1000 as blockTime

, case when TIMESTAMPDIFF(MILLISECOND, coalesce(lag(timestamp) over (order by block_number), timestamp)::timestamp, timestamp::timestamp) = 0 then 3 else

txs / (TIMESTAMPDIFF(MILLISECOND, coalesce(lag(timestamp) over (order by block_number), timestamp)::timestamp, timestamp::timestamp) / 1000) END as tps

from (

select DISTINCT sum(helper) over (partition by block_number) as txs

, block_number

, timestamp

--, TIMESTAMPDIFF(MILLISECOND, coalesce(lag(timestamp) over (partition by block_number order by block_number), timestamp)::timestamp, timestamp::timestamp) as time_diff

from (

select tx_hash, timestamp, block_number, 1 as helper from external.tokenflow_starknet.decoded_events

where timestamp > current_Timestamp() - interval '4 hour'

and chain_id = 'mainnet'

group by tx_hash, timestamp, block_number

order by timestamp DESC

)

)