Cohort Retention Analysis in SQL
Build retention tables from event data.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Build retention tables from event data. Database and SQL dialect: [DATABASE_AND_DIALECT]. Table and column definitions (events or orders with user ID and timestamp): [SCHEMA]. Definition of an active user or repeat purchase: [ACTIVE_DEFINITION]. Cohort period (weekly or monthly) and time range: [COHORT_PERIOD].
Follow this worked pattern on a made-up events table.
Step 1, first activity per user: SELECT user_id, MIN(DATE_TRUNC('month', event_time)) AS cohort_month FROM events GROUP BY user_id;
Step 2, activity by month: SELECT user_id, DATE_TRUNC('month', event_time) AS active_month FROM events GROUP BY 1, 2;
Step 3, join and count: months_since = difference in months between active_month and cohort_month; count distinct users by cohort_month and months_since.
Step 4, retention rate = users active in month n / users in the cohort at month 0.
Result sketch: cohort Jan, size 400; month 1: 140 (35.0 percent); month 2: 110 (27.5 percent).
Write the full query set for my schema with common table expressions, handle partial months (exclude the current incomplete period), time zones, test and internal users, and users with no events. Add a pivot of the final table, a way to check that cohort sizes equal total distinct users, and notes on interpretation (survivorship, seasonality, definition choices). Provide a version for rolling retention.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.