Get startedGet started for free

MAU monitor (I)

Carol from the Product team noticed that you're working with a lot of user-centric KPIs for Bob's pitch deck. While you're at it, she says, you can help build an idea of hers involving a user-centric KPI. She wants to build a monitor that compares the MAUs of the previous and current month, raising a red flag to the Product team if the current month's active users are less than those of the previous month.

To start, write a query that returns a table of MAUs and the previous month's MAU for every month.

This exercise is part of the course

Analyzing Business Data in SQL

View Course

Exercise instructions

  • Select the month and the MAU.
  • Fetch the previous month's MAU.
  • 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)

SELECT
  -- Select the month and the MAU
  ___,
  ___,
  COALESCE(
    ___,
  0) AS last_mau
FROM mau
-- Order by month in ascending order
ORDER BY ___ ASC;
Edit and Run Code