Write a query to calculate month-over-month growth rate in active users from a DailyActiveUsers table.

Answer

Compare each month's active user count to the previous month's using LAG(), then compute the percentage change.

Solution

WITH monthly AS (
  SELECT DATE_TRUNC('month', activity_date) AS month,
         COUNT(DISTINCT user_id) AS active_users
  FROM DailyActiveUsers
  GROUP BY DATE_TRUNC('month', activity_date)
)
SELECT month, active_users,
       LAG(active_users) OVER (ORDER BY month) AS prev_month_users,
       ROUND(100.0 * (active_users - LAG(active_users) OVER (ORDER BY month))
             / LAG(active_users) OVER (ORDER BY month), 2) AS mom_growth_pct
FROM monthly;

Step by Step

1. The monthly CTE first collapses daily activity into one row per calendar month, counting distinct active users with COUNT(DISTINCT user_id).
2. LAG(active_users) OVER (ORDER BY month) attaches the prior month's user count to each row.
3. Month-over-month growth is (current - previous) / previous, expressed as a percentage.

Alternative: Avoiding Repeated LAG() Calls with a Second CTE

WITH monthly AS (
  SELECT DATE_TRUNC('month', activity_date) AS month, COUNT(DISTINCT user_id) AS active_users
  FROM DailyActiveUsers GROUP BY DATE_TRUNC('month', activity_date)
),
with_prev AS (
  SELECT month, active_users, LAG(active_users) OVER (ORDER BY month) AS prev_month_users
  FROM monthly
)
SELECT month, active_users, prev_month_users,
       ROUND(100.0 * (active_users - prev_month_users) / prev_month_users, 2) AS mom_growth_pct
FROM with_prev;

Computing LAG() once in its own CTE and referencing the resulting column afterward is cleaner and less error-prone than repeating the same window function three times in one SELECT.

Key Exam Traps

• COUNT(user_id) vs COUNT(DISTINCT user_id): If a user is active on multiple days within the same month, plain COUNT(user_id) would count them repeatedly - COUNT(DISTINCT user_id) is essential to get a true monthly active user count.
• Division by zero or NULL: The very first month has no prior month, so prev_month_users is NULL and the growth calculation correctly returns NULL rather than erroring - worth stating explicitly that this is expected, not a bug.
• Repeating LAG() multiple times in one query is inefficient and risks inconsistent edits if the window definition ever changes - always factor it into its own CTE column when it's referenced more than once.