Get startedGet started for free

MAU monitor (III)

Carol is very pleased with your last query, but she's requested one change: She prefers to have the month-on-month (MoM) MAU growth rate over a raw delta of MAUs. That way, the MAU monitor can have more complex triggers, like raising a yellow flag if the growth rate is -2% and a red flag if the growth rate is -5%.

Write a query that returns a table of months and each month's MoM MAU growth rate to finalize the MAU monitor.

This exercise is part of the course

Analyzing Business Data in SQL

View Course

Exercise instructions

  • Select the month and its MoM MAU growth rate.
  • Order by month in ascending order.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

WITH mau AS (
  SELECT
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    COUNT(DISTINCT user_id) AS mau
  FROM orders
  GROUP BY delivr_month),

  mau_with_lag AS (
  SELECT
    delivr_month,
    mau,
    GREATEST(
      LAG(mau) OVER (ORDER BY delivr_month ASC),
    1) AS last_mau
  FROM mau)

SELECT
  -- Calculate the MoM MAU growth rates
  ___,
  ROUND(
    ___,
  2) AS growth
FROM mau_with_lag
-- Order by month in ascending order
ORDER BY ___;
Edit and Run Code