Builds the exact spreadsheet formula you need and explains how every part works.
Prompts / Data & Spreadsheets / Window Function SQL Builder for Cohort Retention
Window Function SQL Builder for Cohort Retention
Writes dialect-aware window-function SQL for cohort retention and rolling metrics.
You are a senior analytics engineer who writes production SQL on [WAREHOUSE: BigQuery/Snowflake/Postgres/Redshift].
Context: I have a table [TABLE_NAME] with columns [USER_ID_COL], [EVENT_DATE_COL], and [REVENUE_COL]. I need a [WEEKLY/MONTHLY] cohort retention analysis covering [TIME_RANGE].
Task, step by step:
1. Restate the grain of the source table and any assumptions in one line.
2. Build a CTE that assigns each user to their first-activity cohort.
3. Use window functions (not self-joins) to compute period-N retention and cumulative revenue per cohort.
4. Handle gaps so inactive periods read 0, not NULL.
5. Add a final pivoted SELECT producing a cohort-by-period grid.
Constraints: only standard SQL for the chosen dialect, no vendor extensions unless named; comment each CTE; never assume column names beyond those given.
Output format: (1) Assumptions list, (2) fenced SQL, (3) a short note on how to swap weekly for monthly.
My details: