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.
Deze oefening maakt deel uit van de cursus
Analyzing Business Data in SQL
Oefeninstructies
- Select the month and its MoM MAU growth rate.
- Order by month in ascending order.
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
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 ___;