Cohort

"""
Cohort: SQL Retention Calculator & Dynamic Triangle Matrix Engine
"""
from typing import Dict, Any, List
from pydantic import BaseModel

class CohortRow(BaseModel):
    cohort_month: str
    cohort_size: int
    retention_percentages: List[float]

class CohortRetentionEngine:
    @staticmethod
    def generate_bigquery_sql() -> str:
        return """
        WITH user_activity AS (
          SELECT user_id, DATE_TRUNC(created_at, MONTH) as signup_month,
                 DATE_TRUNC(activity_date, MONTH) as activity_month
          FROM `analytics.events`
        )
        SELECT
          signup_month,
          COUNT(DISTINCT user_id) as cohort_size,
          DATE_DIFF(activity_month, signup_month, MONTH) as period,
          ROUND(COUNT(DISTINCT user_id) / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER(PARTITION BY signup_month ORDER BY activity_month), 4) as retention_rate
        FROM user_activity
        GROUP BY 1, activity_month
        ORDER BY 1, 3;
        """