SELECT

tx_from as address,

count(distinct tx_id) as tx_counts,

min(date(block_timestamp)) as first_tx,

max(date(block_timestamp)) as last_tx,

count(distinct date(block_timestamp)) as days_active, -- how many days was the user online

count(distinct date_trunc('week', block_timestamp)) as weeks_active, -- how many distinct weeks

count(distinct date_trunc('month', block_timestamp)) as months_active, -- how many distinct months

datediff('day', first_tx, getdate()) as age_day, -- age by day

datediff('week', first_tx, getdate()) as age_week,

datediff('month', first_tx, getdate()) as age_month,

case when age_month <= months_active then 1 else 0 end as active_every_month,

case when age_week <= weeks_active then 1 else 0 end as active_every_week,

case when age_day <= days_active then 1 else 0 end as active_every_day,

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

FROM osmosis.core.fact_transactions

group by 1

SELECT

count(distinct address) as wallets,

sum(active_every_month) as monthly_active,

sum(active_every_week) as weekly_active,

sum(active_every_day) as daily_active

FROM stats