SELECT DISTINCT date_trunc('d', block_timestamp) as dates

FROM sei.core.fact_transactions

),

first_txn as (

SELECT tx_from,

min(block_timestamp) as first_txn

FROM sei.core.fact_transactions

GROUP BY 1

),

new_users as (

SELECT date_trunc('d', first_txn) as date,

zeroifnull(count(DISTINCT tx_from)) as n_new_users,

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

from dates a

LEFT JOIN first_txn b

on a.dates = date_trunc('d', b.first_txn)

GROUP by 1),

active_users as (

SELECT date_trunc('d', 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.core.FACT_TRANSACTIONS

where block_timestamp is not NULL

GROUP BY 1),

success_rate as (

SELECT date_trunc('d', 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.core.FACT_TRANSACTIONS

GROUP BY 1)