Month-Over-Month Retention Query | Dataford Interview Questions

JPMorganChase wants to measure early engagement among new checking account holders using Chase digital banking activity. Write a PostgreSQL query that calculates next-calendar-month retention for accounts that received an online deposit within their first seven days.

Requirements

Include checking accounts with at least one deposit event through the online channel from the opening timestamp through seven days after opening, inclusive.
Group eligible accounts by the month in which the account was opened.
Define a retained account as an eligible account with any event during the immediately following calendar month.
Return the cohort month, retention month, eligible account count, retained account count, and retention rate. Round the rate to four decimal places and avoid division by zero.

Schema

checking_accounts

Column	Type	Description
account_idPK	INTEGER	Unique checking account identifier
opened_at	TIMESTAMP	Account opening timestamp
account_type	VARCHAR(20)	Account product type

account_events

Column	Type	Description
event_idPK	INTEGER	Unique event identifier
account_id	INTEGER	Associated account identifier
event_at	TIMESTAMP	Event timestamp
event_type	VARCHAR(30)	Event category
channel	VARCHAR(20)	Event channel

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