Weekly Engagement Tiers — Solution & ExplanationProblems / Weekly Engagement Tiers / EditorialApproach

Per-user counts. GROUP BY user_id collapses the event log to one row per user.

Bucket with CASE. Evaluate thresholds top-down: >= 10 → power, >= 3 → casual, else light. Because CASE stops at the first true branch, descending thresholds make the ranges exclusive without writing BETWEEN.

Count the buckets. Group by the tier label; COUNT(*) now counts users. Order alphabetically.

Key InsightSegmentation reports are two stacked aggregations with a *labeling step* in between. The subtle part is that after the first GROUP BY, the row grain has changed from events to users — so the second COUNT(*) means "users", and no DISTINCT is needed anywhere.Hints

Three layers: count events per user, label each count with a tier, count users per label. The middle layer is just a CASE expression.

Write the CASE branches from the highest threshold down — WHEN count >= 10 first, then >= 3, ELSE light. Ordering the branches low-to-high with >= would swallow everyone into the first branch.

The final GROUP BY is over the derived tier label, and COUNT(*) there counts users (one row per user survived the first aggregation), not events.

Solve Weekly Engagement Tiers yourself →