Retention by Channel SQL | Dataford Interview Questions

Write a SQL query to find the retention rate of Square merchants month-over-month, grouped by the marketing channel they originated from.

Assume a merchant is active in a month when at least one activity record exists. Treat a missing originating channel as Unknown, and calculate retention as the percentage of merchants active in the prior month who are active again in the current month.

Output

One row per current month and originating channel with prior-month merchants.
Columns: activity_month, marketing_channel, retained_merchants, prior_month_merchants, and retention_rate.
Sort by activity_month, then marketing_channel ascending.

Schema

merchants

Column	Type	Description
merchant_idPK	INT	Unique Square merchant identifier
marketing_channel	VARCHAR(50)	Marketing channel through which the merchant originated

merchant_activity

Column	Type	Description
activity_idPK	INT	Unique merchant activity record identifier
merchant_id	INT	Merchant associated with the activity
activity_date	DATE	Date on which the merchant was active

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