Month-Over-Month Merchant Retention | Dataford Interview Questions

Shopify wants to monitor whether active merchants continue using Shopify across consecutive months. Write a PostgreSQL query that calculates month-over-month retention for each month with a preceding month of activity.

Requirements

Treat an active merchant as a merchant with at least one non-null activity event during the month and status = 'active'.
For each month after the first observed month, return the previous month's active merchant count, the count retained into the current month, and the retention rate.
Deduplicate multiple activity events from the same merchant in the same month. Round the retention rate to six decimal places and order results chronologically.

Schema

merchants

Column	Type	Description
merchant_idPK	INT	Unique Shopify merchant identifier
shop_domain	VARCHAR(255)	Shopify shop domain
status	VARCHAR(20)	Merchant account status
created_at	DATE	Date the merchant account was created

merchant_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
merchant_id	INT	Referenced merchant identifier
activity_type	VARCHAR(50)	Type of merchant activity
occurred_at	TIMESTAMP	Timestamp when the activity occurred

Your question is Month-Over-Month Merchant Retention. 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