Meta's Product Growth team uses the AARRR funnel to monitor activation and retention across Instagram Reels, Facebook Groups, and Instagram Save activity. Write a PostgreSQL query that calculates the daily DAU/MAU ratio for the fixed 30-day period from 2025-03-01 through 2025-03-30.
Requirements
- Count distinct active users with at least one valid activity event per calendar day as
dau.
- Count distinct active users with activity during the trailing 30-day period ending on each calendar day as
mau.
- Return every calendar date in the reporting window, including dates with zero DAU.
- Calculate
dau_mau_ratio as DAU divided by MAU, rounded to four decimal places. Avoid division by zero.
Output details
- Return columns in this order:
activity_date, dau, mau, dau_mau_ratio.
- Order rows by
activity_date ascending.
- Count activity only for users whose
account_status is active.
- Restrict activity events to dates from
2025-03-01 through 2025-03-30 when calculating trailing 30-day metrics.
- Treat each trailing window as inclusive of its calendar date and the preceding 29 dates.
- Return
NULL for dau_mau_ratio when mau is zero.