SQL for Retention or Conversion | Dataford Interview Questions

Salesforce Operations wants to monitor month-over-month adoption of Salesforce Data Cloud across customer segments. Write a PostgreSQL query that calculates monthly user retention by segment for February and March 2025.

Requirements

Treat a user as active in a month when at least one activity record exists for that month.
Calculate retained users, defined as users active in both the current month and the immediately preceding month within the same segment.
Return the prior-month active-user count and retention rate as a percentage. Return NULL when the prior-month count is zero.
Exclude activities outside January through March 2025 and label users with a missing segment as Uncategorized.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
signup_date	DATE	Date the user signed up
segment	VARCHAR(30)	Customer segment

data_cloud_activity

Column	Type	Description
activity_idPK	INTEGER	Unique activity record
user_id	INTEGER	User associated with the activity
activity_month	DATE	Month of activity, stored as a month-start date

Your question is SQL for Retention or Conversion. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem