select

, 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, block_timestamp as timestamp, block_number, 1 as helper from optimism.core.fact_event_logs

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

group by tx_hash, timestamp, block_number

order by timestamp DESC

)

)

)