select date_trunc(day, block_timestamp) as date,

count(*)/1440/count(distinct block_timestamp::date) as tx_per_min,

count(distinct tx_id) as tx_count

from sei.core.fact_transactions

where block_timestamp::date < CURRENT_DATE

and tx_succeeded = TRUE

and block_timestamp::date >= '2023-07-01'

and block_timestamp::date < CURRENT_DATE

and block_timestamp::date not in ('2023-07-21')

group by 1

, diff AS (

SELECT

date,

tx_per_min,

case when date >= '2023-08-15' then 'Public Launched'

else 'Other' end as type,

LAG(tx_per_min) OVER (ORDER BY date) AS prev_month_value,

(tx_per_min - LAG(tx_per_min) OVER (ORDER BY date)) / LAG(tx_per_min) OVER (ORDER BY date) AS percentage_diff

FROM

total

SELECT

date,

tx_per_min as "STPM",

type,

zeroifnull(percentage_diff*100) as WoW_percentage,

case when WoW_percentage < 0 then 'DoD Neg (%)'

else 'DoD Pos (%)' end as wow_type

FROM

diff

ORDER BY