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
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;