with txs AS (

SELECT

x.BLOCK_TIMESTAMP as BLOCK_TIMESTAMP,

x.TX_ID as tx,

x.PROPOSER as user,

y.EVENT_TYPE as type,

y.EVENT_CONTRACT

FROM flow.core.fact_transactions x JOIN flow.core.fact_events y ON x.TX_ID = y.TX_ID

WHERE x.TX_SUCCEEDED = 'true'

Date_trunc('month', BLOCK_TIMESTAMP) as date,

REVERSE(SUBSTRING(REVERSE(EVENT_CONTRACT), 1, CHARINDEX('.', REVERSE(EVENT_CONTRACT))-1)) as contract,

count( DISTINCT tx) as "Number of monthly txns",

count(DISTINCT user) as "Number of monthly active users"

from txs

GROUP BY 1, 2

ORDER BY 1