Prompt Almanac

Create advanced Works with: Any AI

SQL Cohort Retention Query Walkthrough With a Worked Example

Structured brief

Build retention tables step by step.

Open in ChatGPTOpen in Claude

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

Rate this prompt

Loading ratings…

Teach me to build a cohort retention analysis in SQL. Database system: [DATABASE]. Tables and columns (users with signup date, events with user and date): [SCHEMA]. Period, monthly or weekly: [PERIOD].

Build the query in stages with common table expressions, explaining each: assign each user to a cohort by signup month, compute activity months for each event, calculate months since signup, count distinct active users per cohort and period, and divide by cohort size for retention percentages. Show the expected output of each stage on a tiny invented dataset of three users: January cohort has 3 users, 2 active in month 1 (66.7 percent) and 1 in month 2 (33.3 percent); in another example a cohort of 200 with 120 active in month 1 is 60 percent and 90 in month 2 is 45 percent. Handle pitfalls: events before signup, time zones, duplicate events, partial months, an incomplete latest period, and whether an event on the signup day counts as month zero.

Provide the final query, the shape of the result table, and how to pivot into a triangle (rows cohorts, columns months since signup, the latest incomplete diagonal greyed out). Validate: cohort sizes sum to the total users, and month zero retention is 100 percent by construction. Small cohorts are noisy (a cohort of 20 moves 5 points when one user returns), so compare cohorts of at least 100 users. Add three-sentence reading guidance and two questions to ask of the table. Mark dialect-specific functions [VERIFY].

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.

Related prompts