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: Week 1 to Week 4 Retention
00:00
5 left

SQL: Week 1 to Week 4 Retention

MediumSQL · PostgreSQL

Problem

Meta's Product Growth team is evaluating early retention for an Instagram Reels feature launch. A user is considered active when they generate any event for the selected launch. Write a PostgreSQL query to find users active during week 1 who were also active during week 4.

Use launch 101, whose launch date is 2025-01-01. Week 1 is the half-open interval from launch date through launch date plus 7 days. Week 4 is the half-open interval from launch date plus 21 days through launch date plus 28 days.

Requirements

  1. Aggregate each user's event counts for weeks 1 and 4.
  2. Return only users with at least one event in both weeks.
  3. Include the user's ID, username, week 1 event count, and week 4 event count.
  4. Order results by user_id ascending.

Schema

users
ColumnTypeDescription
user_idPKINTUnique Meta user identifier
usernameVARCHAR(100)User display name
country_codeVARCHAR(2)Two-letter user country code
feature_launches
ColumnTypeDescription
launch_idPKINTUnique feature launch identifier
feature_nameVARCHAR(100)Meta feature or surface name
launch_dateDATEFeature launch date
feature_events
ColumnTypeDescription
event_idPKINTUnique feature event identifier
user_idINTUser who generated the event
launch_idINTAssociated feature launch
event_tsTIMESTAMPTimestamp when the event occurred
event_nameVARCHAR(50)Event type generated on the feature
Tablesusersfeature_launchesfeature_events
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.