Self-Join SQL for User Cohorts | Dataford Interview Questions

CRED wants to evaluate referral-led user cohorts. Write a PostgreSQL query that self-joins users to connect each referrer with referred users, then measures early payment activity.

Requirements

Group results by the referrer's signup month using TO_CHAR.
Include referred users only when they signed up within 30 days of the referrer.
Count distinct referred users with at least one successful payment within 30 days of their own signup, calculate total successful payment value, and calculate average payment value per active referred user.
Retain referral cohorts with no qualifying successful payments, showing zero values.

Schema

users

Column	Type	Description
user_idPK	INT	Unique CRED user identifier
full_name	VARCHAR(100)	User's name
signup_date	DATE	Date the user joined CRED
referred_by_user_id	INT	Nullable self-reference to the referring user

payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
user_id	INT	User who made the payment
paid_at	DATE	Payment date
amount	NUMERIC(10,2)	Payment amount
status	VARCHAR(20)	Payment processing status

Your question is Self-Join SQL for User Cohorts. 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