Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
SQL: Signup to 7-Day Active Funnel
00:00
5 left

SQL: Signup to 7-Day Active Funnel

MediumSQL · PostgreSQL

Problem

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

  1. Return one row for each funnel stage in sequence: signup, profile_complete, first_post, and 7_day_active.
  2. Count distinct users at each stage. A first post qualifies only when it occurs at or after profile completion.
  3. Include the percentage of signup users reaching each stage, rounded to two decimal places.
  4. 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.

Schema

users
ColumnTypeDescription
user_idPKINTEGERUnique Meta user identifier
signup_atTIMESTAMPTZUser signup timestamp
product_surfaceVARCHAR(40)Initial Meta product surface
user_events
ColumnTypeDescription
event_idPKINTEGERUnique event identifier
user_idINTEGERUser associated with the event
event_nameVARCHAR(40)Event type
event_atTIMESTAMPTZEvent timestamp
surfaceVARCHAR(40)Meta surface where the event occurred
user_daily_activity
ColumnTypeDescription
activity_idPKINTEGERUnique activity record identifier
user_idINTEGERUser associated with the activity
activity_dateDATEDate of qualifying activity
activity_typeVARCHAR(40)Activity category
Tablesusersuser_eventsuser_daily_activity
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the three tables are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.