Code Quiz

Cohort Retention Trend Bug

Spot the window-function mistake in a monthly cohort retention trend query.

Codesql

WITH monthly AS (
  SELECT cohort_month,
         activity_month,
         COUNT(DISTINCT user_id) AS active_users
  FROM user_activity
  GROUP BY cohort_month, activity_month
)
SELECT cohort_month,
       activity_month,
       active_users,
       ROUND(
         100.0 * active_users /
         FIRST_VALUE(active_users) OVER (
           ORDER BY activity_month
         ), 2) AS retention_pct
FROM monthly
ORDER BY cohort_month, activity_month;

What is the bug in this retention-percentage calculation?

← PreviousNesting Aggregates Inside Window Functions Next →Filtering On Window Function Results