select

date_trunc('month', block_timestamp) as date,

count(distinct signer_id) as user_count,

count(distinct tx_hash) as tx_count,

sum(amount) as total_vol

from

near.gov.fact_staking_actions

where

action in ('staking', 'unstaking')

group by

date

),

monthly_changes as (

select

date,

user_count,

tx_count,

total_vol,

lag(user_count, 1) over (order by date) as previous_month_user_count,

lag(tx_count, 1) over (order by date) as previous_month_tx_count,

lag(total_vol, 1) over (order by date) as previous_month_total_vol

from

monthly_activity

where date >= '2020-10-01'

date,

-- User Count Metrics

user_count,

previous_month_user_count,

user_count - coalesce(previous_month_user_count, 0) as user_count_change,

case when coalesce(previous_month_user_count, 0) = 0 then 0