SELECT
DATE_TRUNC('day', TO_TIMESTAMP_NTZ(CAST(TIMESTAMP AS NUMBER(38, 0)) / 1000)) as day,
blockchain,
count(*) AS n_actions,
count(distinct(trailheadid)) AS n_active_trails,
count(distinct(lower(address))) AS n_users
FROM bi_analytics.bronze.arprograms_hike
WHERE timestamp IS NOT NULL
GROUP BY day, blockchain