## Monthly Cohort Retention A residential building-services company tracks every time a tenant interacts with its mobile app (for example, calling an elevator, scheduling a package pickup, or paying rent). You need to compute a **monthly cohort retention** table so the product team can see how engagement decays over a tenant's lifetime. Group every tenant into a **signup cohort** defined by the calendar month of their first-ever activity. Then, for each cohort, measure how many of those tenants were still active in each subsequent month relative to their signup month. ### Tables **`users`** | column | type | description | |---------------|-------------|----------------------------------------------| | `user_id` | `BIGINT` | Primary key, unique tenant identifier | | `signup_date` | `DATE` | Date the tenant registered | **`activity`** | column | type | description | |-----------------|-------------|-----------------------------------------------------| | `user_id` | `BIGINT` | Foreign key to `users.user_id` | | `activity_ts` | `TIMESTAMP` | Timestamp of the in-app action (UTC, no DST issues) | | `activity_type` | `TEXT` | Action label (e.g. `elevator_call`, `rent_payment`) | A tenant may have many rows in `activity`. A tenant may also have zero activity rows. ### Definitions - **Cohort month**: the first day of the calendar month of the tenant's **earliest** `activity_ts` (use the earliest activity, NOT `signup_date`). Truncate to the month, e.g. an activity on `2025-03-14` belongs to cohort `2025-03-01`. - **Activity month**: the first day of the calendar month of any given `activity_ts`. - **Month number** (`month_number`): the integer number of whole months between the cohort month and the activity month. Month 0 is the cohort month itself, month 1 is the next calendar month, and so on. Compute it as `(activity_year - cohort_year) * 12 + (activity_month - cohort_month)`. - A tenant is **retained** in a given month number if they have **at least one** activity row whose activity month maps to that month number. Count each tenant at most once per `(cohort_month, month_number)` pair. - **Retention rate** = (retained tenants in that month number) / (cohort size). Cohort size is the number of distinct tenants in that cohort (their count at month 0). ### Required Output One row per `(cohort_month, month_number)` combination that has at least one retained tenant, with columns in this exact order: | column | type | description | |-------------------|-----------|----------------------------------------------------------| | `cohort_month` | `DATE` | First day of the cohort month | | `cohort_size` | `INTEGER` | Distinct tenants in this cohort (value at month 0) | | `month_number` | `INTEGER` | Months since cohort month (0, 1, 2, ...) | | `retained_users` | `INTEGER` | Distinct retained tenants in this `(cohort, month)` | | `retention_rate` | `NUMERIC` | `retained_users / cohort_size`, rounded to 4 decimals | Order the result by `cohort_month` ascending, then `month_number` ascending. Tenants with no activity rows do not belong to any cohort and should not appear.