Retention for Linked Accounts | Dataford Interview Questions

Write a query to calculate the month-over-month retention rate of users who linked their bank accounts in their first week at Truebill.

Use the users and bank_account_links tables. Treat the first week as the seven-day period beginning at signup, and measure retention by the number of months since each user's first qualifying link.

Output

One row per month number since the user's first qualifying link.
Columns: month_number, active_users, prior_month_users, and retention_rate.
Include month zero, use NULL for its retention rate, and sort by month_number ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Truebill user identifier
signup_at	TIMESTAMP	Timestamp when the user signed up

bank_account_links

Column	Type	Description
link_idPK	INT	Unique bank account link event identifier
user_id	INT	User associated with the link event
linked_at	TIMESTAMP	Timestamp when the bank account was linked

Your question is Retention for Linked Accounts. 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