SELECT tx_from,

min(block_timestamp) as first_txn

FROM sei_testnet.core.FACT_TRANSACTIONS

GROUP BY 1

),

new_users as (

SELECT date_trunc('{{interval}}', first_txn) as date,

count(DISTINCT tx_from) as n_new_users,

sum(n_new_users)over(ORDER BY date) as cum_new_users

from first_txn

GROUP by 1),

active_users as (

SELECT date_trunc('{{interval}}', block_timestamp) as date,

count(DISTINCT tx_id) as n_txn,

sum(n_txn)over(order BY date) as cum_txn,

count(DISTINCT tx_from) as n_user,

sum(n_user)over(order BY date) as cum_user

FROM sei_testnet.core.FACT_TRANSACTIONS

where block_timestamp is not NULL

GROUP BY 1),

success_rate as (

SELECT date_trunc('{{interval}}', block_timestamp) as date,

count(DISTINCT CASE when tx_succeeded = 'false' then tx_id end) as n_false_txn,

count(DISTINCT CASE when tx_succeeded = 'true' then tx_id end) as n_true_txn,

sum(n_false_txn)over(order BY date) as cum_false_txn,

sum(n_true_txn)over(order BY date) as cum_true_txn

FROM sei_testnet.core.FACT_TRANSACTIONS

GROUP BY 1)

SELECT a.date,

n_txn,

cum_txn,

n_user,

cum_user,

n_new_users,