select *

, AVG("Daily TXs") OVER (ORDER BY day ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as "TX 7D Rolling"

, percentile_cont(.50) within group (order by "Daily TXs") over (partition by NULL) as "Median TXs"

from (

select day, medianBlockTime_day as "Daily Block Time", medianTPS_day as "Daily TPS", medianBlockTime as "Median Block Time", medianTPS as "Median TPS"

, sum(txs) as "Daily TXs"

from (

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

, percentile_cont(.50) within group (order by blockTime) over (partition by day) as medianBlockTime_day

, percentile_cont(.50) within group (order by tps) over (partition by day) as medianTPS_day

from (

select *

, date_trunc('day', timestamp) as day

, 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 '30 day'

and chain_id = 'mainnet'

group by tx_hash, timestamp, block_number

order by timestamp DESC

)

)