select

tx_id,

msg_type,

split(ATTRIBUTE_VALUE,'uatom')[0]::numeric/1e6 as amount

from cosmos.core.fact_msg_attributes

where msg_type IN ('delegate', 'unbond')

and attribute_key = 'amount'

and ATTRIBUTE_VALUE ilike '%uatom%'

and tx_succeeded = 'TRUE'

)

, delegateTb as (

select

date_trunc('{{Frequency}}', t1.block_timestamp)::date as date,

COUNT(DISTINCT t1.tx_id) as delegate_cnt,

COUNT(DISTINCT TX_FROM) as delegater_cnt,

sum(amount) as delegate_vol,

sum(delegate_cnt) over (order by date) as cum_delegate_cnt,

sum(delegate_vol) over (order by date) as cum_delegate_vol

from cosmos.core.fact_transactions t1 JOIN msgTb t2 USING(tx_id)

where t2.msg_type = 'delegate'

and t1.tx_succeeded = TRUE

AND t1.block_timestamp::date >= CURRENT_DATE - INTERVAL '{{Past_MONTH}} MONTH'

GROUP by 1

order by 1

)

, undelegateTb as (

select

date_trunc('{{Frequency}}', t1.block_timestamp)::date as date,

COUNT(DISTINCT t1.tx_id) as undelegate_cnt,

COUNT(DISTINCT TX_FROM) as undelegater_cnt,

sum(amount) as undelegate_vol,

sum(undelegate_cnt) over (order by date) as cum_undelegate_cnt,

sum(undelegate_vol) over (order by date) as cum_undelegate_vol

from cosmos.core.fact_transactions t1 JOIN msgTb t2 USING(tx_id)

where t2.msg_type = 'unbond'