SQL Retention by Channel | Dataford Interview Questions

How would you write a SQL query to calculate the retention rate of users acquired through paid search versus organic search?

For this exercise, define retention as having recorded activity exactly 30 days after signup. Include only users whose acquisition source is paid search or organic search.

Output

One row per acquisition source, with acquisition_source, acquired_users, retained_users, and retention_rate_pct
Include both requested sources, including sources with no retained users
Sort paid search before organic search

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquisition_source	VARCHAR(50)	Marketing source that acquired the user
signup_date	DATE	Date the user signed up

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User associated with the activity
activity_date	DATE	Date the user was active
activity_type	VARCHAR(40)	Type of recorded user activity

Your question is SQL Retention by Channel. 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