:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
from posthog.clickhouse.cluster import ON_CLUSTER_CLAUSE
from posthog.clickhouse.table_engines import CollapsingMergeTree
from posthog.models.person.sql import PERSON_STATIC_COHORT_TABLE
def COHORTPEOPLE_TABLE_ENGINE():
return CollapsingMergeTree("cohortpeople", ver="sign")
CREATE_COHORTPEOPLE_TABLE_SQL = lambda on_cluster=True: """
CREATE TABLE IF NOT EXISTS cohortpeople {on_cluster_clause}
(
person_id UUID,
cohort_id Int64,
team_id Int64,
sign Int8,
version UInt64
) ENGINE = {engine}
Order By (team_id, cohort_id, person_id, version)
{storage_policy}
""".format(
on_cluster_clause=ON_CLUSTER_CLAUSE(on_cluster),
engine=COHORTPEOPLE_TABLE_ENGINE(),
storage_policy="",
)
TRUNCATE_COHORTPEOPLE_TABLE_SQL = f"TRUNCATE TABLE IF EXISTS cohortpeople {ON_CLUSTER_CLAUSE()}"
GET_COHORT_SIZE_SQL = """
SELECT count(DISTINCT person_id)
FROM cohortpeople
WHERE team_id = %(team_id)s AND cohort_id = %(cohort_id)s AND version = %(version)s
"""
# Continually ensure that all previous version rows are deleted and insert persons that match the criteria
# optimize_aggregation_in_order = 1 is necessary to avoid oom'ing for our biggest clients
RECALCULATE_COHORT_BY_ID = """
INSERT INTO cohortpeople
SELECT id, %(cohort_id)s as cohort_id, %(team_id)s as team_id, 1 AS sign, %(new_version)s AS version
FROM (
{cohort_filter}
) as person
SETTINGS optimize_aggregation_in_order = 1, join_algorithm = 'auto'
"""
GET_COHORTPEOPLE_BY_COHORT_ID = """
SELECT DISTINCT person_id
FROM cohortpeople
WHERE team_id = %(team_id)s AND cohort_id = %(cohort_id)s AND version = %(version)s
ORDER BY person_id
"""
GET_STATIC_COHORTPEOPLE_BY_COHORT_ID = f"""
SELECT person_id
FROM {PERSON_STATIC_COHORT_TABLE}
WHERE team_id = %(team_id)s AND cohort_id = %(cohort_id)s
GROUP BY person_id, cohort_id, team_id
"""
STALE_COHORTPEOPLE = f"""
SELECT team_id, count() AS stale_people_count FROM cohortpeople
WHERE team_id IN %(team_ids)s AND cohort_id = %(cohort_id)s AND version < %(version)s
GROUP BY team_id
HAVING stale_people_count > 0
"""