Events per Active User — Solution & ExplanationProblems / Events per Active User / EditorialApproach

Truncate to the day. DATE_TRUNC('day', event_time) collapses timestamps to a per-day bucket; grouping by the raw timestamp would make near-singleton groups.

Two counts, one pass. COUNT(*) (events) and COUNT(DISTINCT user_id) (users) coexist in the same aggregation — no self-join or second scan required.

Depth ratio. total_events * 1.0 / active_users with ROUND(..., 2); the 1.0 guards against integer division.

Key InsightCOUNT(*) and COUNT(DISTINCT ...) answer different questions about the *same* group — volume versus reach — and their ratio is often more informative than either alone: a day with rising events but flat users means the same people digging deeper, not growth. Recognizing that both live in one GROUP BY keeps a two-metric report to a single scan.Hints

Both metrics come from the same GROUP BY over the truncated day — one is COUNT(*), the other COUNT(DISTINCT user_id). No join needed.

The ratio mixes the two counts. Multiply by 1.0 (or cast) before dividing so integer division doesn't floor 2.50 down to 2.

DATE_TRUNC('day', event_time) — not the raw timestamp — must be both the SELECT expression and the GROUP BY key, otherwise every event lands in its own group.

Solve Events per Active User yourself →