Interview Question Bank · LEFT JOIN · MAX · date arithmetic
2. Churned accounts and their last activity
EasySaaSQuestion #110
Concept: LEFT JOIN · MAX · date arithmeticshow
To find each account's most recent activity, join the activity table and take MAX of its timestamp per account. Use a LEFT JOIN so accounts with no activity at all still appear (with NULL). Subtracting two dates in Postgres gives whole days, so cast a timestamp to ::date first.
Example (different data):
SELECT m.name, MAX(v.visited_at)::date AS last_visit FROM gym_members m LEFT JOIN gym_visits v ON v.member_id = m.id WHERE m.cancelled GROUP BY m.id, m.name;
Your task
Tallyboard's retention team is doing a post-mortem on lost accounts: how long had each one gone quiet before it churned?
For every churned account, return name, churned_on, last_activity_date (date of its latest event in saas_events, NULL if it never had one) and days_silent (churned_on minus last_activity_date). Sort by churned_on, then name.
Starting Postgres in your browser…
Checking your access…