Skip to content

: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.

45.4 KB
Raw Blame
import re
import uuid
from datetime import datetime, timedelta
from typing import Optional

import time_machine
from posthog.test.base import (
    BaseTest,
    ClickhouseTestMixin,
    _create_action,
    _create_event,
    _create_person,
    also_test_with_materialized_columns,
    flush_persons_and_events,
    snapshot_clickhouse_insert_cohortpeople_queries,
)

from rest_framework.exceptions import ValidationError

from posthog.hogql.constants import MAX_SELECT_COHORT_CALCULATION_LIMIT

from posthog.clickhouse.client import sync_execute
from posthog.models.team import Team
from posthog.test.persons import create_person, delete_person, update_person

from products.actions.backend.facade.testing import create_action
from products.cohorts.backend.models.cohort import Cohort
from products.cohorts.backend.models.sql import GET_COHORTPEOPLE_BY_COHORT_ID, GET_STATIC_COHORTPEOPLE_BY_COHORT_ID


def get_person_ids_by_cohort_id(team_id: int, cohort_id: int) -> list[str]:
    cohort = Cohort.objects.get(pk=cohort_id)
    if cohort.is_static:
        results = sync_execute(GET_STATIC_COHORTPEOPLE_BY_COHORT_ID, {"team_id": team_id, "cohort_id": cohort_id})
    else:
        results = sync_execute(
            GET_COHORTPEOPLE_BY_COHORT_ID, {"team_id": team_id, "cohort_id": cohort_id, "version": cohort.version}
        )
    return [str(row[0]) for row in results]


class TestCohort(ClickhouseTestMixin, BaseTest):
    def calculate_cohort_hogql_test_harness(self, cohort: Cohort, pending_version: int):
        from unittest.mock import patch

        # First run: with hogql cohort calculation disabled
        version_without_hogql = pending_version * 2 + 2

        with patch("posthoganalytics.feature_enabled", return_value=False):
            with self.capture_queries_startswith(("INSERT", "insert")) as queries_without_hogql:
                cohort.calculate_people_ch(version_without_hogql)

            results_without_hogql = self._get_cohortpeople(cohort)

            # Check LIMIT in queries
            for query in queries_without_hogql:
                if "LIMIT" in query:
                    assert all(
                        limit == str(MAX_SELECT_COHORT_CALCULATION_LIMIT) for limit in re.findall(r"LIMIT (\d+)", query)
                    )

        # Second run: with hogql cohort calculation enabled
        version_with_hogql = version_without_hogql + 1

        with patch("posthoganalytics.feature_enabled", return_value=True):
            with self.capture_queries_startswith(("INSERT", "insert")) as queries_with_hogql:
                cohort.calculate_people_ch(version_with_hogql)

            results_with_hogql = self._get_cohortpeople(cohort)

            # Check LIMIT in queries
            for query in queries_with_hogql:
                if "LIMIT" in query:
                    assert all(
                        limit == str(MAX_SELECT_COHORT_CALCULATION_LIMIT) for limit in re.findall(r"LIMIT (\d+)", query)
                    )

        # Assert the sets of person_ids are the same
        self.assertCountEqual(results_without_hogql, results_with_hogql)

        # Return the latest version
        return version_with_hogql

    def _get_cohortpeople(self, cohort: Cohort, *, team_id: Optional[int] = None):
        team_id = team_id or cohort.team_id
        return sync_execute(
            GET_COHORTPEOPLE_BY_COHORT_ID,
            {
                "team_id": team_id,
                "cohort_id": cohort.pk,
                "version": cohort.version,
            },
        )

    def test_insert_by_distinct_id_or_email(self):
        create_person(team_id=self.team.pk, distinct_ids=["1"])
        create_person(team_id=self.team.pk, distinct_ids=["123"])
        create_person(team_id=self.team.pk, distinct_ids=["2"])
        # Team leakage
        team2 = Team.objects.create(organization=self.organization)
        create_person(team=team2, distinct_ids=["1"])

        cohort = Cohort.objects.create(team=self.team, groups=[], is_static=True)
        cohort.insert_users_by_list(["1", "123"])
        cohort.refresh_from_db()
        results = get_person_ids_by_cohort_id(self.team.pk, cohort.id)
        self.assertEqual(len(results), 2)
        self.assertEqual(cohort.is_calculating, False)

        # test SQLi
        create_person(team_id=self.team.pk, distinct_ids=["'); truncate person_static_cohort; --"])
        cohort.insert_users_by_list(["'); truncate person_static_cohort; --", "123"])
        results = sync_execute(
            "select count(1) from person_static_cohort where team_id = %(team_id)s",
            {"team_id": self.team.pk},
        )[0][0]
        self.assertEqual(results, 3)

        #  If we accidentally call calculate_people it shouldn't erase people
        self.calculate_cohort_hogql_test_harness(cohort, 0)
        results = get_person_ids_by_cohort_id(self.team.pk, cohort.id)
        self.assertEqual(len(results), 3)

        # if we add people again, don't increase the number of people in cohort
        cohort.insert_users_by_list(["123"])
        results = get_person_ids_by_cohort_id(self.team.pk, cohort.id)
        self.assertEqual(len(results), 3)

    def test_insert_cohort_hogql_query_with_distinct_id(self):
        from products.cohorts.backend.models.util import insert_cohort_query_actors_into_ch

        p1 = create_person(team_id=self.team.pk, distinct_ids=["user1"])
        p2 = create_person(team_id=self.team.pk, distinct_ids=["user2"])
        create_person(team_id=self.team.pk, distinct_ids=["user3"])

        # Create a cohort with a HogQL query that returns distinct_id
        cohort = Cohort.objects.create(
            team=self.team,
            is_static=True,
            query={
                "kind": "HogQLQuery",
                "query": "SELECT distinct_id FROM raw_person_distinct_ids WHERE distinct_id IN ['user1', 'user2']",
            },
        )

        insert_cohort_query_actors_into_ch(cohort, team=self.team)
        cohort.refresh_from_db()

        results = get_person_ids_by_cohort_id(self.team.pk, cohort.id)
        self.assertEqual(len(results), 2)
        result_ids = {str(r) for r in results}
        self.assertIn(str(p1.uuid), result_ids)
        self.assertIn(str(p2.uuid), result_ids)
        self.assertEqual(cohort.is_calculating, False)

    @snapshot_clickhouse_insert_cohortpeople_queries
    def test_cohortpeople_basic(self):
        create_person(
            team_id=self.team.pk,
            distinct_ids=["1"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )
        create_person(
            team_id=self.team.pk,
            distinct_ids=["2"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        cohort1 = Cohort.objects.create(
            team=self.team,
            groups=[
                {
                    "properties": [
                        {"key": "$some_prop", "value": "something", "type": "person"},
                        {
                            "key": "$another_prop",
                            "value": "something",
                            "type": "person",
                        },
                    ]
                }
            ],
            name="cohort1",
        )

        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        results = self._get_cohortpeople(cohort1)
        self.assertEqual(len(results), 2)

    def test_cohortpeople_action_basic(self):
        action = _create_action(team=self.team, name="$pageview")
        create_person(
            team_id=self.team.pk,
            distinct_ids=["1"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="1",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(hours=12),
        )

        create_person(
            team_id=self.team.pk,
            distinct_ids=["2"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="2",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(hours=12),
        )

        cohort1 = Cohort.objects.create(team=self.team, groups=[{"action_id": action.id, "days": 1}], name="cohort1")
        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        results = self._get_cohortpeople(cohort1)
        self.assertEqual(len(results), 2)

        cohort2 = Cohort.objects.create(team=self.team, groups=[{"action_id": action.id, "days": 1}], name="cohort2")
        self.calculate_cohort_hogql_test_harness(cohort2, 0)

        results = self._get_cohortpeople(cohort2)
        self.assertEqual(len(results), 2)

    def _setup_actions_with_different_counts(self):
        action = _create_action(team=self.team, name="$pageview")
        create_person(
            team_id=self.team.pk,
            distinct_ids=["1"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="1",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=1, hours=12),
        )
        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="1",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=0, hours=12),
        )

        create_person(
            team_id=self.team.pk,
            distinct_ids=["2"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="2",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=1, hours=12),
        )

        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="2",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=0, hours=12),
        )

        create_person(
            team_id=self.team.pk,
            distinct_ids=["3"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="3",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=0, hours=12),
        )

        create_person(
            team_id=self.team.pk,
            distinct_ids=["4"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        create_person(
            team_id=self.team.pk,
            distinct_ids=["5"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )
        return action

    def test_cohortpeople_action_count(self):
        action = self._setup_actions_with_different_counts()

        # test operators
        cohort1 = Cohort.objects.create(
            team=self.team,
            groups=[{"action_id": action.id, "days": 3, "count": 2, "count_operator": "gte"}],
            name="cohort1",
        )
        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        results = self._get_cohortpeople(cohort1)
        self.assertEqual(len(results), 2)

        cohort2 = Cohort.objects.create(
            team=self.team,
            groups=[{"action_id": action.id, "days": 3, "count": 1, "count_operator": "lte"}],
            name="cohort2",
        )
        self.calculate_cohort_hogql_test_harness(cohort2, 0)

        results = self._get_cohortpeople(cohort2)
        self.assertEqual(len(results), 1)

        cohort3 = Cohort.objects.create(
            team=self.team,
            groups=[{"action_id": action.id, "days": 3, "count": 1, "count_operator": "eq"}],
            name="cohort3",
        )
        self.calculate_cohort_hogql_test_harness(cohort3, 0)

        results = self._get_cohortpeople(cohort3)
        self.assertEqual(len(results), 1)

    def test_cohortpeople_deleted_person(self):
        create_person(
            team_id=self.team.pk,
            distinct_ids=["1"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )
        p2 = create_person(
            team_id=self.team.pk,
            distinct_ids=["2"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        cohort1 = Cohort.objects.create(
            team=self.team,
            groups=[
                {
                    "properties": [
                        {"key": "$some_prop", "value": "something", "type": "person"},
                        {
                            "key": "$another_prop",
                            "value": "something",
                            "type": "person",
                        },
                    ]
                }
            ],
            name="cohort1",
        )

        self.calculate_cohort_hogql_test_harness(cohort1, 0)
        delete_person(p2)
        self.calculate_cohort_hogql_test_harness(cohort1, 0)

    def test_cohortpeople_prop_changed(self):
        with time_machine.travel((datetime.now() - timedelta(days=3)).strftime("%Y-%m-%d"), tick=False):
            p1 = create_person(
                team_id=self.team.pk,
                distinct_ids=["1"],
                properties={"$some_prop": "something", "$another_prop": "something"},
            )
            p2 = create_person(
                team_id=self.team.pk,
                distinct_ids=["2"],
                properties={"$some_prop": "something", "$another_prop": "something"},
            )

            cohort1 = Cohort.objects.create(
                team=self.team,
                groups=[
                    {
                        "properties": [
                            {
                                "key": "$some_prop",
                                "value": "something",
                                "type": "person",
                            },
                            {
                                "key": "$another_prop",
                                "value": "something",
                                "type": "person",
                            },
                        ]
                    }
                ],
                name="cohort1",
            )

        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        with time_machine.travel((datetime.now() - timedelta(days=2)).strftime("%Y-%m-%d"), tick=False):
            p2.version = 1
            p2.properties = {"$some_prop": "another", "$another_prop": "another"}
            update_person(p2)

        self.calculate_cohort_hogql_test_harness(cohort1, 1)

        results = self._get_cohortpeople(cohort1)

        self.assertEqual(len(results), 1)
        self.assertEqual(results[0][0], p1.uuid)

    def test_cohort_change(self):
        p1 = create_person(
            team_id=self.team.pk,
            distinct_ids=["1"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )
        p2 = create_person(
            team_id=self.team.pk,
            distinct_ids=["2"],
            properties={"$some_prop": "another", "$another_prop": "another"},
        )

        cohort1 = Cohort.objects.create(
            team=self.team,
            groups=[
                {
                    "properties": [
                        {"key": "$some_prop", "value": "something", "type": "person"},
                        {
                            "key": "$another_prop",
                            "value": "something",
                            "type": "person",
                        },
                    ]
                }
            ],
            name="cohort1",
        )
        self.calculate_cohort_hogql_test_harness(cohort1, 0)
        results = self._get_cohortpeople(cohort1)

        self.assertEqual(len(results), 1)
        self.assertEqual(results[0][0], p1.uuid)

        cohort1.groups = [
            {
                "properties": [
                    {"key": "$some_prop", "value": "another", "type": "person"},
                    {"key": "$another_prop", "value": "another", "type": "person"},
                ]
            }
        ]
        cohort1.save()

        self.calculate_cohort_hogql_test_harness(cohort1, 1)

        results = self._get_cohortpeople(cohort1)
        self.assertEqual(len(results), 1)
        self.assertEqual(results[0][0], p2.uuid)

    def test_cohortpeople_with_valid_other_cohort_filter(self):
        create_person(team_id=self.team.pk, distinct_ids=["1"], properties={"foo": "bar"})
        create_person(team_id=self.team.pk, distinct_ids=["2"], properties={"foo": "non"})

        cohort0: Cohort = Cohort.objects.create(
            team=self.team,
            groups=[{"properties": [{"key": "foo", "value": "bar", "type": "person"}]}],
            name="cohort0",
        )
        self.calculate_cohort_hogql_test_harness(cohort0, 0)

        cohort1: Cohort = Cohort.objects.create(
            team=self.team,
            groups=[{"properties": [{"key": "id", "type": "cohort", "value": cohort0.id}]}],
            name="cohort1",
        )

        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        res = self._get_cohortpeople(cohort1)
        self.assertEqual(len(res), 1)

    @snapshot_clickhouse_insert_cohortpeople_queries
    def test_cohortpeople_with_not_in_cohort_operator(self):
        _create_person(
            distinct_ids=["1"],
            team_id=self.team.pk,
            properties={"$some_prop": "something1"},
        )
        person2 = _create_person(
            distinct_ids=["2"],
            team_id=self.team.pk,
            properties={"$some_prop": "something2"},
        )

        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="1",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=10),
        )
        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="2",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=20),
        )

        flush_persons_and_events()

        cohort0: Cohort = Cohort.objects.create(
            team=self.team,
            groups=[{"properties": [{"key": "$some_prop", "value": "something1", "type": "person"}]}],
            name="cohort0",
        )
        self.calculate_cohort_hogql_test_harness(cohort0, 0)

        cohort1 = Cohort.objects.create(
            team=self.team,
            filters={
                "properties": {
                    "type": "AND",
                    "values": [
                        {
                            "event_type": "events",
                            "key": "$pageview",
                            "negation": False,
                            "time_interval": "year",
                            "time_value": 2,
                            "type": "behavioral",
                            "value": "performed_event",
                        },
                        {
                            "key": "id",
                            "negation": True,
                            "type": "cohort",
                            "value": cohort0.pk,
                        },
                    ],
                }
            },
            name="cohort1",
        )

        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        self.assertEqual([str(row[0]) for row in self._get_cohortpeople(cohort1)], [str(person2.uuid)])

    @snapshot_clickhouse_insert_cohortpeople_queries
    def test_cohortpeople_with_not_in_cohort_operator_for_behavioural_cohorts(self):
        _create_person(
            distinct_ids=["1"],
            team_id=self.team.pk,
            properties={"$some_prop": "something"},
        )
        person2 = _create_person(
            distinct_ids=["2"],
            team_id=self.team.pk,
            properties={"$some_prop": "something"},
        )

        _create_event(
            event="signup",
            team=self.team,
            distinct_id="1",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=10),
        )
        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="1",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=10),
        )
        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="2",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(days=20),
        )
        flush_persons_and_events()

        cohort0: Cohort = Cohort.objects.create(
            team=self.team,
            groups=[
                {
                    "properties": [
                        {
                            "event_type": "events",
                            "key": "signup",
                            "negation": False,
                            "time_interval": "day",
                            "time_value": 15,
                            "type": "behavioral",
                            "value": "performed_event_first_time",
                        },
                    ]
                }
            ],
            name="cohort0",
        )
        self.calculate_cohort_hogql_test_harness(cohort0, 0)

        cohort1 = Cohort.objects.create(
            team=self.team,
            filters={
                "properties": {
                    "type": "AND",
                    "values": [
                        {
                            "event_type": "events",
                            "key": "$pageview",
                            "negation": False,
                            "time_interval": "year",
                            "time_value": 2,
                            "type": "behavioral",
                            "value": "performed_event",
                        },
                        {
                            "key": "id",
                            "negation": True,
                            "type": "cohort",
                            "value": cohort0.pk,
                        },
                    ],
                }
            },
            name="cohort1",
        )

        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        self.assertEqual([str(row[0]) for row in self._get_cohortpeople(cohort1)], [str(person2.uuid)])

    def test_cohortpeople_with_nonexistent_other_cohort_filter(self):
        create_person(team_id=self.team.pk, distinct_ids=["1"], properties={"foo": "bar"})
        create_person(team_id=self.team.pk, distinct_ids=["2"], properties={"foo": "non"})

        cohort1: Cohort = Cohort.objects.create(
            team=self.team,
            groups=[{"properties": [{"key": "id", "type": "cohort", "value": 666}]}],
            name="cohort1",
        )

        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        res = self._get_cohortpeople(cohort1)
        self.assertEqual(len(res), 0)

    def test_clickhouse_empty_query(self):
        cohort2 = Cohort.objects.create(
            team=self.team,
            groups=[{"properties": [{"key": "$some_prop", "value": "nomatchihope", "type": "person"}]}],
            name="cohort1",
        )

        self.calculate_cohort_hogql_test_harness(cohort2, 0)
        self.assertFalse(Cohort.objects.get().is_calculating)

    def test_query_with_multiple_new_style_cohorts(self):
        action1 = create_action(
            team=self.team,
            name="action1",
            steps_json=[
                {
                    "event": "$autocapture",
                    "url": "https://posthog.com/feedback/123",
                    "url_matching": "exact",
                }
            ],
        )

        # satiesfies all conditions
        p1 = create_person(
            team_id=self.team.pk,
            distinct_ids=["p1"],
            properties={"name": "test", "email": "[email protected]"},
        )
        _create_event(
            team=self.team,
            event="$autocapture",
            properties={"$current_url": "https://posthog.com/feedback/123"},
            distinct_id="p1",
            timestamp=datetime.now() - timedelta(days=2),
        )
        _create_event(
            team=self.team,
            event="$pageview",
            properties={},
            distinct_id="p1",
            timestamp=datetime.now() - timedelta(days=1),
        )

        # doesn't satisfy action
        create_person(
            team_id=self.team.pk,
            distinct_ids=["p2"],
            properties={"name": "test", "email": "[email protected]"},
        )
        _create_event(
            team=self.team,
            event="$autocapture",
            properties={"$current_url": "https://posthog.com/feedback/123"},
            distinct_id="p2",
            timestamp=datetime.now() - timedelta(weeks=3),
        )
        _create_event(
            team=self.team,
            event="$pageview",
            properties={},
            distinct_id="p2",
            timestamp=datetime.now() - timedelta(days=1),
        )

        # satisfies special condition (not pushed down person property in OR group)
        p3 = create_person(
            team_id=self.team.pk,
            distinct_ids=["p3"],
            properties={"name": "special", "email": "[email protected]"},
        )
        _create_event(
            team=self.team,
            event="$autocapture",
            properties={"$current_url": "https://posthog.com/feedback/123"},
            distinct_id="p3",
            timestamp=datetime.now() - timedelta(days=2),
        )

        cohort2 = Cohort.objects.create(
            team=self.team,
            filters={
                "properties": {
                    "type": "AND",
                    "values": [
                        {
                            "key": action1.id,
                            "event_type": "actions",
                            "time_value": 2,
                            "time_interval": "week",
                            "value": "performed_event_first_time",
                            "type": "behavioral",
                        },
                        {
                            "key": "email",
                            "value": "[email protected]",
                            "type": "person",
                        },  # this is pushed down
                    ],
                }
            },
            name="cohort2",
        )

        cohort1 = Cohort.objects.create(
            team=self.team,
            filters={
                "properties": {
                    "type": "AND",
                    "values": [
                        {
                            "type": "OR",
                            "values": [
                                {
                                    "key": "$pageview",
                                    "event_type": "events",
                                    "time_value": 1,
                                    "time_interval": "day",
                                    "value": "performed_event",
                                    "type": "behavioral",
                                },
                                {
                                    "key": "$pageview",
                                    "event_type": "events",
                                    "time_value": 2,
                                    "time_interval": "week",
                                    "value": "performed_event",
                                    "type": "behavioral",
                                },
                                {
                                    "key": "name",
                                    "value": "special",
                                    "type": "person",
                                },  # this is NOT pushed down
                            ],
                        },
                        {
                            "type": "AND",
                            "values": [{"key": "id", "value": cohort2.pk, "type": "cohort"}],
                        },
                    ],
                }
            },
            name="cohort1",
        )

        self.calculate_cohort_hogql_test_harness(cohort2, 0)
        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        result = self._get_cohortpeople(cohort1)
        self.assertCountEqual([p1.uuid, p3.uuid], [r[0] for r in result])

    def test_update_cohort(self):
        create_person(
            team_id=self.team.pk,
            distinct_ids=["1"],
            properties={"$some_prop": "something"},
        )
        create_person(
            team_id=self.team.pk,
            distinct_ids=["2"],
            properties={"$another_prop": "something"},
        )
        create_person(
            team_id=self.team.pk,
            distinct_ids=["3"],
            properties={"$another_prop": "something"},
        )

        cohort1 = Cohort.objects.create(
            team=self.team,
            groups=[{"properties": [{"key": "$some_prop", "value": "something", "type": "person"}]}],
            name="cohort1",
        )

        self.calculate_cohort_hogql_test_harness(cohort1, 0)

        # Should only have p1 in this cohort
        results = self._get_cohortpeople(cohort1)
        self.assertEqual(len(results), 1)

        cohort1.groups = [{"properties": [{"key": "$another_prop", "value": "something", "type": "person"}]}]
        cohort1.save()
        self.calculate_cohort_hogql_test_harness(cohort1, 1)

        # Should only have p2, p3 in this cohort
        results = self._get_cohortpeople(cohort1)
        self.assertEqual(len(results), 2)

        cohort1.groups = [{"properties": [{"key": "$some_prop", "value": "something", "type": "person"}]}]
        cohort1.save()
        self.calculate_cohort_hogql_test_harness(cohort1, 2)

        # Should only have p1 again in this cohort
        results = self._get_cohortpeople(cohort1)
        self.assertEqual(len(results), 1)

    def test_cohort_versioning(self):
        create_person(
            team_id=self.team.pk,
            distinct_ids=["1"],
            properties={"$some_prop": "something"},
        )
        create_person(
            team_id=self.team.pk,
            distinct_ids=["2"],
            properties={"$another_prop": "something"},
        )
        create_person(
            team_id=self.team.pk,
            distinct_ids=["3"],
            properties={"$another_prop": "something"},
        )

        # start the cohort at some later version
        cohort1 = Cohort.objects.create(
            team=self.team,
            groups=[{"properties": [{"key": "$some_prop", "value": "something", "type": "person"}]}],
            name="cohort1",
        )

        version = self.calculate_cohort_hogql_test_harness(cohort1, 5)

        cohort1.pending_version = version
        cohort1.version = version
        cohort1.save()

        # Should have p1 in this cohort even if version is different
        results = self._get_cohortpeople(cohort1)
        self.assertEqual(len(results), 1)

    def test_calculate_people_ch_in_multiteam_project(self):
        # Create another team in the same project
        team2 = Team.objects.create(organization=self.organization, project=self.team.project)

        # Create people in team 1
        _person1_team1 = _create_person(
            team_id=self.team.pk,
            distinct_ids=["person1"],
            properties={"$some_prop": "else"},
        )
        person2_team1 = _create_person(
            team_id=self.team.pk,
            distinct_ids=["person2"],
            properties={"$some_prop": "something"},
        )
        # Create people in team 2 with same property
        person1_team2 = _create_person(
            team_id=team2.pk,
            distinct_ids=["person1_team2"],
            properties={"$some_prop": "something"},
        )
        _person2_team2 = _create_person(
            team_id=team2.pk,
            distinct_ids=["person2_team2"],
            properties={"$some_prop": "else"},
        )
        # Create cohort in team 2 (but same project as team 1)
        shared_cohort = Cohort.objects.create(
            team=team2,
            groups=[{"properties": [{"key": "$some_prop", "value": "something", "type": "person"}]}],
            name="shared cohort",
        )
        # Calculate cohort
        self.calculate_cohort_hogql_test_harness(shared_cohort, 0)

        # Verify shared_cohort is now calculated for both teams
        results_team1 = self._get_cohortpeople(shared_cohort, team_id=self.team.pk)
        results_team2 = self._get_cohortpeople(shared_cohort, team_id=team2.pk)

        self.assertCountEqual([r[0] for r in results_team1], [person2_team1.uuid])
        self.assertCountEqual([r[0] for r in results_team2], [person1_team2.uuid])

    def test_cohortpeople_action_all_events(self):
        # Create an action that matches all events (no specific event defined)
        action = create_action(team=self.team, name="all events", steps_json=[{"event": None}])

        # Create two people
        create_person(
            team_id=self.team.pk,
            distinct_ids=["1"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        create_person(
            team_id=self.team.pk,
            distinct_ids=["2"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        # Create different types of events for both people
        _create_event(
            event="$pageview",
            team=self.team,
            distinct_id="1",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(hours=12),
        )

        _create_event(
            event="$autocapture",
            team=self.team,
            distinct_id="2",
            properties={"attr": "some_val"},
            timestamp=datetime.now() - timedelta(hours=12),
        )

        # Create a cohort based on the "all events" action
        cohort = Cohort.objects.create(
            team=self.team, groups=[{"action_id": action.id, "days": 1}], name="cohort_all_events"
        )
        cohort.calculate_people_ch(pending_version=0)

        # Both people should be in the cohort since they both performed some event
        results = self._get_cohortpeople(cohort)
        self.assertEqual(len(results), 2)

        # Create a person with no events
        create_person(
            team_id=self.team.pk,
            distinct_ids=["3"],
            properties={"$some_prop": "something", "$another_prop": "something"},
        )

        # Recalculate cohort
        cohort.calculate_people_ch(pending_version=1)

        # Should still only have 2 people since person 3 has no events
        results = self._get_cohortpeople(cohort)
        self.assertEqual(len(results), 2)

    @also_test_with_materialized_columns(person_properties=["organization_id"])
    def test_recalculate_cohort_with_list_of_values(self):
        # Create a specific UUID that we'll use both in the person and cohort filter
        matching_uuid = str(uuid.uuid4())

        # Create a person with the matching organization_id
        matching_person = _create_person(
            distinct_ids=["matching_user"],
            team_id=self.team.pk,
            properties={"organization_id": matching_uuid},
        )

        # Create a cohort with the specific filter structure provided
        cohort = Cohort.objects.create(
            team=self.team,
            name="property list cohort",
            filters={
                "properties": {
                    "type": "OR",
                    "values": [
                        {
                            "type": "OR",
                            "values": [
                                {
                                    "key": "organization_id",
                                    "type": "person",
                                    "value": [
                                        matching_uuid,  # Include our matching UUID
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                    ],
                                    "negation": False,
                                    "operator": "exact",
                                }
                            ],
                        }
                    ],
                }
            },
        )

        # Capture the SQL insert statements when the cohort is calculated
        with self.capture_queries_startswith(("INSERT INTO cohortpeople", "insert into cohortpeople")) as queries:
            self.calculate_cohort_hogql_test_harness(cohort, 0)

        # Assert at least one query was captured
        self.assertTrue(len(queries) > 0, "No queries were captured during cohort calculation")

        # Check that we don't have an excessive number of replaceRegexpAll and JSONExtractRaw functions
        for query in queries:
            # Count instances of replaceRegexpAll and JSONExtractRaw
            replace_regexp_count = query.lower().count("replaceregexpall")
            json_extract_raw_count = query.lower().count("jsonextractraw")

            # Ensure we don't have 11 or more instances of either function
            self.assertLess(replace_regexp_count, 3, "Too many replaceRegexpAll instances found in query")
            self.assertLess(json_extract_raw_count, 3, "Too many JSONExtractRaw instances found in query")

        # Verify that the person with the matching organization_id is in the cohort
        results = self._get_cohortpeople(cohort)
        self.assertEqual(len(results), 1, "Expected one person to be in the cohort")
        self.assertEqual(
            str(results[0][0]), str(matching_person.uuid), "Expected the matching person to be in the cohort"
        )

    @also_test_with_materialized_columns(person_properties=["organization_id"], is_nullable=["organization_id"])
    def test_recalculate_cohort_empty_string_property(self):
        # Create a person with an empty organization_id
        matching_person = _create_person(
            distinct_ids=["matching_user"],
            team_id=self.team.pk,
            properties={"organization_id": ""},
        )

        # Create a cohort with the specific filter structure provided
        cohort = Cohort.objects.create(
            team=self.team,
            name="property list cohort",
            filters={
                "properties": {
                    "type": "OR",
                    "values": [
                        {
                            "type": "OR",
                            "values": [
                                {
                                    "key": "organization_id",
                                    "type": "person",
                                    "value": [
                                        "",  # Include our matching UUID
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                        str(uuid.uuid4()),
                                    ],
                                    "negation": False,
                                    "operator": "exact",
                                }
                            ],
                        }
                    ],
                }
            },
        )

        # Capture the SQL insert statements when the cohort is calculated
        with self.capture_queries_startswith(("INSERT INTO cohortpeople", "insert into cohortpeople")) as queries:
            self.calculate_cohort_hogql_test_harness(cohort, 0)

        # Assert at least one query was captured
        self.assertTrue(len(queries) > 0, "No queries were captured during cohort calculation")

        # Check that we don't have an excessive number of replaceRegexpAll and JSONExtractRaw functions
        for query in queries:
            # Count instances of replaceRegexpAll and JSONExtractRaw
            replace_regexp_count = query.lower().count("replaceregexpall")
            json_extract_raw_count = query.lower().count("jsonextractraw")

            # Ensure we don't have 11 or more instances of either function
            self.assertLess(replace_regexp_count, 3, "Too many replaceRegexpAll instances found in query")
            self.assertLess(json_extract_raw_count, 3, "Too many JSONExtractRaw instances found in query")

        # Verify that the person with the matching organization_id is in the cohort
        results = self._get_cohortpeople(cohort)
        self.assertEqual(len(results), 1, "Expected one person to be in the cohort")
        self.assertEqual(
            str(results[0][0]), str(matching_person.uuid), "Expected the matching person to be in the cohort"
        )

    def test_recalculate_cohort_with_missing_filter(self):
        # Create a cohort with the specified OR filter structure
        cohort = Cohort.objects.create(
            team=self.team,
            name="behavioral or filter cohort",
            filters={
                "properties": {
                    "type": "OR",
                    "values": [
                        {
                            "type": "OR",
                            "values": [
                                {
                                    "key": "aim_purchase",
                                    "type": "behavioral",
                                    "value": "performed_event",
                                    "negation": False,
                                    "event_type": "events",
                                }
                            ],
                        }
                    ],
                }
            },
        )

        with self.assertRaises(ValidationError):
            self.calculate_cohort_hogql_test_harness(cohort, 0)

    def test_cohort_with_inclusion_and_exclusion_and_nested_negation(self):
        # Create two users with different properties
        p1 = _create_person(
            team_id=self.team.pk, distinct_ids=["user1"], properties={"email": "exclude1", "in_cohort_1": "yes"}
        )
        p2 = _create_person(
            team_id=self.team.pk, distinct_ids=["user2"], properties={"email": "exclude2", "in_cohort_1": "yes"}
        )
        _create_person(
            team_id=self.team.pk, distinct_ids=["user3"], properties={"email": "include", "in_cohort_1": "yes"}
        )
        _create_person(team_id=self.team.pk, distinct_ids=["user4"], properties={"in_cohort_1": "yes"})
        flush_persons_and_events()

        cohort_1 = Cohort.objects.create(
            team=self.team,
            name="cohort_1",
            groups=[{"properties": [{"key": "in_cohort_1", "value": "yes", "type": "person", "operator": "exact"}]}],
        )

        cohort_2 = Cohort.objects.create(
            team=self.team,
            name="cohort_2",
            filters={
                "properties": {
                    "type": "AND",
                    "values": [
                        {
                            "type": "AND",
                            "values": [
                                {"key": "email", "operator": "is_not", "value": "exclude1", "type": "person"},
                                {"key": "email", "operator": "is_not", "value": "exclude2", "type": "person"},
                            ],
                        },
                        {
                            "type": "OR",
                            "values": [
                                {"key": "id", "value": cohort_1.pk, "type": "cohort"},
                            ],
                        },
                    ],
                }
            },
        )

        # Create third cohort that includes cohort_1 and excludes cohort_2
        cohort_3 = Cohort.objects.create(
            team=self.team,
            filters={
                "properties": {
                    "type": "AND",
                    "values": [
                        {"key": "id", "value": cohort_1.pk, "type": "cohort"},
                        {"key": "id", "value": cohort_2.pk, "type": "cohort", "negation": True},
                    ],
                }
            },
            name="cohort_3",
        )
        self.calculate_cohort_hogql_test_harness(cohort_1, 0)
        self.calculate_cohort_hogql_test_harness(cohort_2, 0)
        self.calculate_cohort_hogql_test_harness(cohort_3, 0)

        results = self._get_cohortpeople(cohort_3)
        self.assertEqual(len(results), 2)
        self.assertCountEqual([x[0] for x in results], [p1.uuid, p2.uuid])