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 for Monthly Retention
00:00
5 left

SQL for Monthly Retention

MediumSQL · PostgreSQL

Problem

Write a SQL query to calculate monthly retention for customers acquired through marketing campaigns.

Use the provided campaign, acquisition, customer, and activity data. Treat a customer as retained in a month when they have at least one activity during that calendar month on or after acquisition.

Output

  1. One row per campaign, acquisition cohort month, and activity month.
  2. Return campaign_id, cohort_month, activity_month, cohort_customers, retained_customers, and retention_rate.
  3. Include months from each cohort month through the latest activity month, including zero-retention months. Sort by campaign_id, cohort_month, and activity_month.

Output details

  • Represent cohort_month and activity_month as the first calendar day of each month.
  • Count each customer at most once per campaign and cohort month for both cohort_customers and retained_customers.
  • Round retention_rate to exactly 4 decimal places, including trailing zeroes.
  • Sort campaign_id, cohort_month, and activity_month in ascending order.

Schema

customers
ColumnTypeDescription
customer_idPKINTUnique customer identifier
customer_nameVARCHAR(100)Customer display name
campaigns
ColumnTypeDescription
campaign_idPKINTUnique marketing campaign identifier
campaign_nameVARCHAR(150)Marketing campaign name
channelVARCHAR(50)Campaign acquisition channel
campaign_acquisitions
ColumnTypeDescription
acquisition_idPKINTUnique acquisition record identifier
campaign_idINTReferenced marketing campaign
customer_idINTReferenced acquired customer
acquired_atTIMESTAMPTimestamp when the customer was acquired
customer_activity
ColumnTypeDescription
activity_idPKINTUnique activity identifier
customer_idINTCustomer performing the activity
activity_atTIMESTAMPTimestamp of customer activity
activity_typeVARCHAR(50)Type of customer activity
Tablescustomerscampaignscampaign_acquisitionscustomer_activity
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the four 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.