SELECT

tx_from as address,

count(distinct tx_id) as Transactions,

count(distinct date(block_timestamp)) as Active_days,

min(date(block_timestamp)) as First_transaction,

max(date(block_timestamp)) as Last_Transaction,

datediff('day', Last_Transaction, getdate()) as days_last_active,

datediff('day', First_transaction, getdate()) as age_today

FROM osmosis.core.fact_transactions

group by 1

),

new_users AS (

SELECT

First_transaction,

count(distinct address) as "New Users",

sum("New Users") over (order by First_transaction) as "Cumulative New"

FROM Osmosis_users

GROUP BY 1

),

active_users AS (

SELECT

date(block_timestamp) as date,

count(distinct tx_from) as "Distinct Wallets",

count(distinct tx_id) as Transactions

FROM osmosis.core.fact_transactions

WHERE tx_status = 'SUCCEEDED'

GROUP BY 1

SELECT

*

FROM active_users

LEFT JOIN new_users ON First_transaction = date

ORDER BY 1 DESC