Drill 2 of 12 in SaaS questions
0

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…