Business context
Meta’s Product Growth team monitors the AARRR funnel for new users across surfaces such as Instagram Reels and Facebook Groups. The activation sequence is signup, profile completion, first post, and activity during the first seven days.
Task
Write a PostgreSQL query that calculates the funnel for users who signed up from January 1 through January 7, 2025. A user is 7-day active if they have at least one activity record on a date from signup date plus 1 day through signup date plus 7 days, inclusive.
Requirements
- Return one row for each funnel stage in sequence:
signup, profile_complete, first_post, and 7_day_active.
- Count distinct users at each stage. A first post qualifies only when it occurs at or after profile completion.
- Include the percentage of signup users reaching each stage, rounded to two decimal places.
- Preserve users who do not complete later stages, and order results by funnel sequence.
Output details
- Return columns in order
stage, users, and signup_percentage.
- Treat signup timestamps from
TIMESTAMPTZ '2025-01-01 00:00:00+00' inclusive through TIMESTAMPTZ '2025-01-08 00:00:00+00' exclusive.
- Use the earliest
profile_completed event and earliest post_created event for each user.
- Treat a zero signup count as producing a
NULL signup_percentage.
- Count activity only when
activity_id is non-NULL.