WITH monthly_data AS (

SELECT

date_trunc('month', a.block_timestamp) AS month,

count(distinct b.proposer) as user_count

from flow.core.fact_events a

join flow.core.fact_transactions b

join flow.core.dim_contract_labels c

on a.tx_id = b.tx_id and a.event_contract = c.event_contract

where c.event_contract ilike '%dimension%' and a.block_timestamp::date > '2022-09-05'

and b.tx_succeeded = True group by 1

ORDER BY

1 ASC

),

monthly_diff AS (

SELECT

month,

user_count,

LAG(user_count) OVER (ORDER BY month) AS prev_month_value,

(user_count - LAG(user_count) OVER (ORDER BY month)) / LAG(user_count) OVER (ORDER BY month) AS percentage_diff

FROM

monthly_data

SELECT

month,

user_count,

zeroifnull(percentage_diff*100) as MoM_percentage,

case when MoM_percentage < 0 then 'MoM Neg (%)'

else 'MoM Pos (%)' end as type

FROM

monthly_diff

ORDER BY

month ASC;