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.

103 KB
Raw Blame
"""Pick the recordings worth watching in one experiment's exposed sessions.

The recordings tab can list a variant's sessions but says nothing about which of the thousands carry
signal, so picking what to watch is sampling rather than targeting. This module answers that with
watch cards: bounded groups of recordings, each one a sentence a reader can act on — "this variant did
this event clearly more than the others, here are recordings of it happening".

**Recordings are the deliverable.** A card's count is a count of watchable recordings, checked
against replay existence before the card is returned; a finding that no recording can back is
dropped, because a promise the playlist can't keep reads as the feature being broken. The event
comparison underneath is the picking mechanism, not the product. A card also names a few of its
recordings to start with, each carrying the signals that earn it the place, because narrowing
thousands of recordings to twenty still leaves twenty that a list renders identically and the
recordings list orders by its own sort rather than by the order they arrive in.

**It reports no effect size, on purpose, and this is the constraint the file serves.** The results
tab publishes one already, from a real statistics engine: per person, over the whole run window,
with sample-ratio checks and credible intervals behind it. This reads one session per person over a
window clamped to hours or days. Measured on a production experiment the two agreed on direction
for every shared event and on no number at all — 7.5x there against 41x here for the same event —
because the later sessions where people convert are outside what this reads. So the three rules
that follow are not stylistic:

  1. Cards carry a direction and a band, never a rate, a ratio or a person count. Whatever we
     called it, a precise number next to an event name is an effect size, and would be read
     against the one the results tab computes.
  2. A card on one of the experiment's own metric events names the metric it belongs to and says
     nothing about how that metric moved. Metric events are ranked like any other event rather
     than held out, because on a UI experiment the events closest to the change are usually the
     ones a metric already counts: measured on two production experiments, the largest separation
     in the whole project was a metric event both times, and holding them out left the shelf
     ranking incidental events or nothing at all. What must not happen is a second, differently
     computed answer to the question the results tab answers, and rule 1 is what prevents that.
  3. An event no other variant can fire is the variant's own rendering rather than something a
     person chose to do, so it is separated onto its own shelf and capped. A variant that ships a
     new element instruments that element, and such an event separates the variants perfectly, so
     without the split it outranks every real behavioral difference: measured on a production
     experiment, the two strongest findings were a callout variant's own impression and dismissal
     events, at a thousand times the separation of anything a person actually did differently.

**Population.** The exposed population the experiment's results count, resolved through the same
exposure linkage the tab's recordings list joins (`replay_linkage.exposed_persons_select`): one
row per exposed distinct id, carrying the person, the variant the analysis attributes them, and
their first exposure time. The variant comes from that attribution, never from an event in a
session, so this surface and the results tab bucket a person the same way. Sessions enter the
comparison through the person, whether or not any exposure event was captured with a session id
(server-fired exposures carry none); the two rules below say which people and which session.

**Who is compared.** The people who most recently entered the experiment, newest first exposure
first, until MAX_DELTA_SCAN_PERSONS people are in or the stretches of time their first sessions
can fall in add up to MAX_DELTA_SCAN_DAYS. Everyone first exposed at or after the resulting cutoff
is compared. Selection is on first exposure time, which happens before anyone sees their variant,
so the randomization keeps the variants balanced over the compared people. Selecting on recent
activity instead would select on something that happens after treatment and can depend on the
variant: a variant that brings people back more gets more of its people into the window, and the
people who met the change and did not come back are never compared. First exposure time also
spans the whole run rather than a trailing window, so an experiment that stopped enrolling weeks
ago still compares its last enrollees, in the sessions where they met the change. While an
experiment runs, the newest FIRST_SESSION_SETTLING_HOURS of enrollment is held back, because those
people are still inside the session the comparison would read. The one case this selection does
not cover is a rollout split that changed inside the compared stretch: the newest enrollees are
then almost all in one variant, and walking further back for the thin variant would compare people
exposed weeks apart, which is the confound the selection removes. That case reads as too early
rather than being widened; the results tab's exposure chart is where a changed split shows.

**One person, one session, from the moment they were exposed.** The sessions a variant is exposed
in are not a fair denominator: a variant that stops re-evaluating the flag once a user has acted
contributes fewer later sessions, and those missing sessions are the quiet ones. Measured on a
production experiment this reached 3.7x more exposed sessions in one variant off near-identical
people, which pushed nine in ten event names to one side of the comparison: arithmetic reading as
behavior. Counting each exposed person once fixes the denominator, and it makes the rows
independent, which is what the ranking's noise test below assumes. The numerator needs the same
treatment: on that experiment one variant's people averaged seven covered sessions each against
the other's two, so they had seven chances to have done anything rather than two. So a person is
read from one session: the one holding their earliest event at or after their first exposure,
among their events up to FIRST_SESSION_HORIZON_HOURS after it, which is the session where they met
the change and the same amount of behavior on both sides. Only rows at or after the first exposure
count: what a person did in the exposure session before the flag was evaluated is the same in every
variant, so keeping it only dilutes the comparison. A person with no session in that horizon is
not compared. A card's *recordings*, by contrast, come from any of a compared person's sessions in
the horizon that contain the event — the statistics need fairness, the watchlist needs the behavior
on screen.

**Ranking.** Each variant is compared against all the others pooled, so a five-variant experiment needs no
pairing and costs the same one scan as two variants. Rates are compared on the log of their ratio, and
an event earns a card only once the *conservative* end of that ratio is still a real difference.
Without that test the list ranks rarity: on a production A/A pair — two variants of one experiment
rendering identically — the raw ratio produced a full page of confident findings, every one of them
noise, while the same data under this test produced nothing. The same conservative end picks the
band a card is reported in, so a difference that only cleared the floor because the sample is large
reports as slight, whatever its point estimate.

**Cost.** The scan cannot prune by event name — the whole point is that it does not know which
events matter yet. So time is the only thing bounding it, and the time it reads is the union of the
compared people's `[first exposure, first exposure + horizon]` stretches, never more than
MAX_DELTA_SCAN_DAYS of events. The union, not a contiguous window, is what makes stragglers cheap:
an experiment that enrolled most people in one week and a few stragglers weeks later reads the
stragglers' days plus the bulk week, and skips the empty weeks between. A first query picks the
people from the population alone, as per-minute counts of first exposures, so the scan reads events
once and joins only the chosen people's distinct ids. The population read has its own bounds,
inherited from the linkage: an exposure scan pruned on the exposure event name, or the
preaggregated exposures table on precomputing teams, and the linkage's memory ceiling where it
carries one (activation mode). The follow-up queries that back cards with recordings filter by
event name, so they prune on the events table's primary key and stay cheap. It is still the
heaviest read on the tab, which is why the caller is expected to load it on demand.
"""

import json
import math
import hashlib
from dataclasses import dataclass, replace
from datetime import datetime, timedelta
from typing import Optional

from django.utils import timezone

from rest_framework.exceptions import ValidationError

from posthog.schema import EventsNode

from posthog.hogql import ast
from posthog.hogql.constants import HogQLGlobalSettings
from posthog.hogql.database.database import Database
from posthog.hogql.modifiers import create_default_modifiers_for_team
from posthog.hogql.query import execute_hogql_query

from posthog.clickhouse.query_tagging import Feature, Product, tag_queries
from posthog.dataclasses import frozen
from posthog.enums import LabeledStrEnum
from posthog.models.team.team import Team
from posthog.models.user import User
from posthog.session_recordings.queries.session_replay_events import SessionReplayEvents
from posthog.utils import get_safe_cache, pluralize, safe_cache_set

from products.access_control.backend.facade.property_access import sort_restricted_properties
from products.access_control.backend.property_access_control import (
    get_restricted_properties_with_group_type_index_for_team,
)
from products.experiments.backend.hogql_queries import MULTIPLE_VARIANT_KEY
from products.experiments.backend.hogql_queries.exposure_query_logic import (
    get_exposure_event_and_property,
    get_multiple_variant_handling_from_experiment,
    resolve_default_exposure_event,
)
from products.experiments.backend.metric_events import (
    MetricEventSource,
    SharedHogQLDatabase,
    build_source_condition,
    resolve_metric_events,
)
from products.experiments.backend.models.experiment import Experiment, metric_display_rank
from products.experiments.backend.replay_linkage import (
    ExperimentExposureLinkage,
    exposed_persons_select,
    resolve_exposure_linkage,
)
from products.experiments.backend.session_exposure import never_session_linked_events

# The most days of events one comparison reads. Tighter than MAX_BUCKET_SCAN_DAYS. The bucket scan
# filters by event name in its WHERE, so ClickHouse prunes on the events table's primary key; this
# one compares every event name there is and has no such predicate, so time is all that stands
# between it and the team's whole history.
MAX_DELTA_SCAN_DAYS = 14
# Ceiling on the exposed people one comparison covers, most recent first exposure first. Bounds
# the aggregation state rather than the rows read. The cut is on first exposure time across all
# variants at once, which is what keeps the variants covered over the same stretch of enrollment,
# for the reason the module docstring gives under "Who is compared".
MAX_DELTA_SCAN_PERSONS = 20_000
# How far past a person's first exposure their events are read. Their compared session is the one
# holding their earliest event in this stretch, so the bound keeps "first session after exposure"
# meaning the session where they met the change, and it caps the per-(person, session) aggregation
# state for people with many sessions. Stretches are clamped at the window end, so a person first
# exposed inside the horizon of an experiment's end date is read over what is left of it: still
# the session they met the change in, with only its tail missing.
FIRST_SESSION_HORIZON_HOURS = 24
# How much of a running experiment's newest enrollment the comparison holds back. A person exposed
# minutes ago is still inside the session that would be read, so their event names are a partial
# record of it, and an experiment that enrolls a whole comparison within one hour would otherwise
# be read entirely from sessions in progress. Held back only while another two variants can still
# be compared without those people, because a shelf with nothing on it is worse than one read a
# little early.
FIRST_SESSION_SETTLING_HOURS = 1
# Ceiling on (event name x variant) rows one comparison ranks. Distinct event names per project are
# normally in the hundreds; a project that keys event names by id is what this is for.
MAX_DELTA_EVENT_ROWS = 10_000
# How many behavior cards one response carries. The shelf is scanned, not scrolled, and a long tail
# of near-identical differences is noise rather than depth.
MAX_BEHAVIOR_CARDS = 8
# How many events the variant's own rendering emits get a card. They confirm the change is live and
# say nothing about behavior, so a couple covers the question however many such events a variant
# instruments.
MAX_VARIANT_ONLY_CARDS = 2
# How many of the experiment's metric events get shortcut cards, in the order the experiment's own
# metrics page lists them, so the metric a reader thinks of first is the one they get. An event that
# already earned a comparison card is not repeated as a shortcut.
MAX_METRIC_CARD_EVENTS = 2
# How many recordings a card names to start with. Enough to offer a choice, few enough that the
# reader still opens one instead of reading a second list.
MAX_CARD_HIGHLIGHTS = 3
# Two cards sharing this much of their recordings are one playlist under two names, so the second
# one is dropped, e.g. if two cards share 18 of their 20 recordings.
DUPLICATE_CARD_OVERLAP = 0.8
# How far one count can carry a recording up the highlight order. Past a few occurrences a count
# measures how long the session is rather than what it shows, and raw totals hand every card the
# same longest session. Only the ranking is bounded; the reason still prints the true count.
HIGHLIGHT_COUNT_DAMPING = 3
# A session at or past this many of one friction signal, without a single rage click, is a client
# stuck in an error loop or automation, not a person to watch, so it is never offered as a
# highlight. It stays on its cards' playlists. A rage click exempts the session because it is the
# one signal only a person produces: as of August 2026, production sessions past this bound with a
# rage click show a median 23 active minutes across several distinct errors (a person suffering
# through them), while those without show under a minute of activity and one error repeating.
MAX_HIGHLIGHT_SIGNAL_COUNT = 100
# Recording candidates fetched per card before replay existence is checked, and how many survive
# onto the card. The margin absorbs sessions that were never recorded without a second round trip.
MAX_CARD_RECORDING_CANDIDATES = 60
MAX_CARD_RECORDINGS = 20
# An event has to have been done by this many people in the variant it is more common in before it can
# earn a card. The separation test below already drops rare events on its own; this only keeps the
# candidate set from being mostly rows that can never pass it.
MIN_SUPPORT_PERSONS = 10
# Below this many exposed people a variant is noise to compare, whatever the floor above allows. With
# fewer than two variants past it there is nothing to compare at all, which is reported as "too early"
# rather than as an empty shelf that would read as "the variants behaved identically".
MIN_VARIANT_PERSONS = 50
# Laplace-style smoothing on both shares before they are divided. Keeps "none in one variant" finite and
# pulls small counts toward no difference, so the ranking is not led by rare events.
RATIO_SMOOTHING = 0.5
# How many standard errors of separation a difference needs before it earns a card at all. This is a
# ranking floor, not a significance claim, and it is deliberately past the conventional 1.96: one
# comparison tests every event name in the project at once, so the loose threshold that reads as
# "95% sure" for a single question is wrong by hundreds of tries here. On a production A/A pair the
# 1.96 version still let a difference through, barely above the floor; at this width the same data
# fell short of the floor and produced no cards at all.
CONFIDENCE_Z = 2.58
# ...and how big the conservative end of the ratio still has to be, in log space: about 1.35x. A
# difference the reader cannot see while watching a recording is not worth a card telling them to.
MIN_LOG_RATIO_LOWER_BOUND = 0.3
# Where that conservative end earns a stronger word, in log space: about 3x and about 1.5x. Bands
# rather than the number itself, for the reason in the module docstring — the number is the part
# that reads as an effect size and collides with the one the results tab computes.
FAR_MORE_LOG_RATIO = 1.1
MORE_LOG_RATIO = 0.4
# When the other variants are this close to never firing an event at all, relative to how often
# they would have fired it at this variant's rate, they are not doing it less: they have no way to do
# it. A leak is tolerated rather than requiring a flat zero because an element one variant renders
# can still be reached from the others by a shared route, and because a person who saw two variants
# in a session the comparison kept carries one variant's events under the other's key.
VARIANT_ONLY_MAX_LEAKAGE = 0.02
# ...and how many people doing it the other variants had to be missing before their absence means
# anything. The comparison counts each person once, so this floor is an expected count of people
# who did the event, not of event occurrences. Below it, "nobody else did it" is what a handful of
# people looks like whatever the cause, so the card stays on the behavior shelf and the evidence
# floors decide whether it appears at all.
VARIANT_ONLY_MIN_EXPECTED = 10.0
# Per (team, experiment, window bucket, viewer restriction profile). The window moves with
# wall-clock time on a running experiment, so the key it is built from is quantized to this same
# interval — at any finer resolution the key would change faster than the entry expires and the
# TTL would never apply, leaving the heaviest read on the tab uncached in practice.
DELTA_CACHE_TTL = 15 * 60
# Kept beside the scan rather than with the viewset's other constants so the flag and what it gates
# can't drift.
EXPERIMENT_BEHAVIOR_COMPARISON_FLAG = "experiment-behavior-comparison"

# Events whose *name* carries no behavior, so a difference in how often people do them says nothing
# about what they did. Page views and autocaptures are the interesting ones to leave out: they are
# in almost every session in both variants, so they rank last anyway, but their names describe a
# mechanism rather than an action. What replaces them is a page-level comparison, which needs the
# project's path-cleaning rules and a second grouping key, so it is not this scan.
UNCOMPARABLE_EVENTS = frozenset(
    {
        "$pageview",
        "$pageleave",
        "$autocapture",
        "$web_vitals",
        "$feature_flag_called",
        "$experiment_exposure",
        "$feature_enrollment_update",
        "$identify",
        "$groupidentify",
        "$create_alias",
        "$merge_dangerously",
        "$set",
        "$snapshot",
        "$$heatmap",
        "survey shown",
    }
)

# What a card ranks its own recordings by, strongest kind first, as (event name, singular label).
# Counted per session rather than compared across variants: each is a property of the session rather
# than of the event that earned the card, so a recording keeps the same reason on every card it
# backs. Counted over the whole covered session, so the phrase still describes what the reader sees
# once the recording is open. One more signal rides alongside these without being one of them: how
# many times the session fired the card's own event, which is per card by construction and so
# computed at pick time rather than here.
HIGHLIGHT_SIGNALS: tuple[tuple[str, str], ...] = (
    ("$rageclick", "rage click"),
    ("$exception", "error"),
    ("$dead_click", "dead click"),
)

# Events whose card belongs on the friction shelf rather than the behavior one. Same pipeline and
# the same evidence bar — the split is presentation: a reader triages "the new variant breaks
# something" differently from "the new variant changes what people do". Derived from the highlight
# signals because they are the same three events by design: what the friction shelf cards and what
# a highlight reason counts as friction must never disagree.
FRICTION_EVENTS = frozenset(event for event, _singular in HIGHLIGHT_SIGNALS)


class SessionEventDeltasUnavailable(Exception):
    """The cards can't be computed for this experiment — a caller error, not a failure.
    Raised instead of returning an empty shelf, so "we declined to compare" never reads as
    "the variants behaved identically"."""


# The labels repeat the values because the published OpenAPI enum lists these exact pairs.
class ExperimentWatchCardStrength(LabeledStrEnum):
    """How far apart a variant and the rest are, conservatively, in bands rather than as a number."""

    # Nobody in the other variants did it, among the people compared. A fact about the compared set
    # rather than a ratio, and the one band that is exact.
    ONLY = "only", "only"
    FAR_MORE = "far_more", "far_more"
    MORE = "more", "more"
    SLIGHTLY_MORE = "slightly_more", "slightly_more"


# The labels repeat the values because the published OpenAPI enum lists these exact pairs.
class ExperimentWatchCardKind(LabeledStrEnum):
    # An event this variant did clearly more than the other variants together.
    BEHAVIOR = "behavior", "behavior"
    # Same evidence, but the event is an error/rage signal, so it reads as a defect lead.
    FRICTION = "friction", "friction"
    # An event only this variant can fire, because the variant is what renders it. Confirms the change is
    # live rather than saying anything about what people did with it.
    VARIANT_ONLY = "variant_only", "variant_only"
    # A shortcut to recordings around one of the experiment's own metric events. No comparison
    # claim: what happened to the metric is the results tab's answer.
    METRIC = "metric", "metric"


# The labels repeat the values because the published OpenAPI enum lists these exact pairs.
class ExperimentWatchEmptyReason(LabeledStrEnum):
    """Why a shelf carries no cards. Set exactly when the shelf is empty.

    Each reason asks something different of the reader, so the frontend and the MCP tool report
    the reason rather than an empty list.
    """

    # Fewer than two variants cleared MIN_VARIANT_PERSONS. A rollout split that changed during the
    # run lands here too, undiagnosed, because the exposure chart already shows it.
    TOO_EARLY = "too_early", "too_early"
    # The variants were compared and no event told them apart.
    NO_SEPARATION = "no_separation", "no_separation"
    # Events told the variants apart, but no recording behind them can be opened.
    NO_RECORDINGS = "no_recordings", "no_recordings"
    # The experiment has exposed people and none of them has a session the scan can see in the
    # window. Only the sessions are read over the window; who counts as exposed is read over the
    # whole run, so the exposures can predate the window, which is why the copy must date the claim
    # to the window rather than to the exposures. More time alone cannot fix it: more traffic
    # captured the same way produces more exposed people without sessions. The read stops at the
    # request, though, so people exposed less than a horizon ago can still return, and the copy
    # must not claim the day has passed.
    NO_SESSION_LINKED_EXPOSURES = "no_session_linked_exposures", "no_session_linked_exposures"


@dataclass(frozen=True)
class ExperimentWatchHighlight:
    """One recording a card names first, and everything it carries that earned it the place.

    The friction signals are session-level rather than per event, so a recording keeps them on
    every card it backs. That is deliberate: the reason has to survive a reader opening the
    recording, and "this session rage clicked six times" does, while anything scoped to the card's
    own event would contradict itself the moment the same session appeared under a second card.
    The one per-card part, "did this N times", survives the same trip because "this" reads against
    whichever card it is printed on.
    """

    session_id: str
    reason: str


@dataclass(frozen=True)
class _CardRecordings:
    """What one card's recordings lookup found: the recordings themselves, and which of them to
    open first."""

    session_ids: list[str]
    highlights: list[ExperimentWatchHighlight]


@dataclass(frozen=True)
class _MetricEvent:
    """One named event an experiment metric counts, and the name of the metric that owns it."""

    event: str
    metric_name: str


@dataclass(frozen=True)
class _CandidateRecording:
    """One session behind one (event, variant) pair: its session-level signal counts, and how often it
    fired the pair's own event."""

    session_id: str
    signals: dict[str, int]
    repetition: int


@dataclass(frozen=True)
class ExperimentWatchCard:
    """One group of recordings worth opening, and the one sentence that justifies it.

    Deliberately no rate, no ratio and no person count: a precise number next to an event name is
    an effect size, and the results tab publishes those for everything the experiment measures,
    computed over a different window and a different unit. The only number here is how many
    recordings the card can actually show.
    """

    kind: ExperimentWatchCardKind
    event: str
    # The variant whose recordings these are — for comparison cards, the variant that did the event more.
    variant: str
    # None on metric cards: they are shortcuts, not comparisons.
    strength: Optional[ExperimentWatchCardStrength]
    # The metric this card's event belongs to, on a shortcut card and on a comparison card alike.
    # Set means the results tab measures this event, so the card must be read as pointing there
    # rather than as a second answer.
    metric_name: Optional[str]
    recording_count: int
    session_ids: list[str]
    # Which of those recordings to open first, strongest signal first. Empty when none of them
    # carries one, which is itself worth showing: it says the card's recordings are unremarkable
    # apart from the event that earned the card.
    highlights: list[ExperimentWatchHighlight]


@dataclass(frozen=True)
class ExperimentWatchVariant:
    """One variant's compared population: exposed people, and the sessions they were seen in."""

    key: str
    persons: int
    sessions: int


@dataclass(frozen=True)
class ExperimentWatchResult:
    cards: list[ExperimentWatchCard]
    variants: list[ExperimentWatchVariant]
    multiple_variant_persons: int
    multiple_variant_handling: str
    metric_events: list[str]
    date_from: datetime
    date_to: datetime
    filter_test_accounts: bool
    # Always False. The population is person-scoped now, so no stamped-property stand-in exists to
    # fall back to; the field stays so the payload shape holds for existing readers.
    used_exposure_fallback: bool
    # True when more people were exposed than one comparison covers, so the most recently exposed
    # were compared. Named for the session ceiling it used to report, kept so the payload shape
    # holds for existing readers.
    sessions_truncated: bool
    events_truncated: bool
    min_variant_persons: int
    # Reported so a reader can tell a card carrying every recording of its event from one that ran
    # into the ceiling. A count sitting on the cap is a floor, and printed as a plain number beside
    # an event name it reads as a measurement of that event.
    max_card_recordings: int
    # How many cards the duplicate-recording-set rule removed from this viewer's shelf, reported
    # so the shelf-loaded telemetry can say how often DUPLICATE_CARD_OVERLAP fires on real shelves
    # before anyone tunes it. Set in `finalize_watch_cards` rather than here, because the shelf is
    # cached across viewers and the duplicate cut runs on the shelf a viewer actually gets.
    dropped_duplicate_cards: int
    too_early: bool
    # Settled per viewer in `finalize_watch_cards`: the recording access cut can empty a shelf the
    # scan built with findings on it.
    empty_reason: Optional[ExperimentWatchEmptyReason]


@dataclass(frozen=True)
class _Shelf:
    cards: list[ExperimentWatchCard]
    empty_reason: Optional[ExperimentWatchEmptyReason]


def all_card_session_ids(result: ExperimentWatchResult) -> list[str]:
    """Every recording the shelf would hand out, deduped — what the caller runs its per-recording
    access filter over."""
    return sorted({session_id for card in result.cards for session_id in card.session_ids})


def finalize_watch_cards(result: ExperimentWatchResult, accessible_session_ids: list[str]) -> ExperimentWatchResult:
    """The shelf this viewer gets: their recordings, no card restating another, highlights assigned.

    Applied on read rather than inside the scan, for the same reason the session buckets do it: the
    shelf is cached, and shared across viewers with the same property restrictions, so this cut is
    what keeps one viewer's entry from leaking another's denied recordings, and it honors a
    revocation that lands while an entry is still warm. A card that loses every recording is
    dropped rather than shown greyed-out, the same rule the scan applies to sessions replay never
    recorded, since either way there is nothing to watch behind it. `recording_count` is recomputed
    so it keeps meaning "recordings this card can show you".

    The cut is silent, as it is on the bucket and batch-context reads: the response must not say
    whether this filter removed anything, because that would tell the viewer recordings denied to
    them ran through this experiment, which is the fact the object-level control withholds.

    The duplicate cut and the highlight assignment run here rather than in the scan because both
    are statements about the shelf a viewer sees. Cutting a duplicate against recordings this
    viewer can't open would drop a card whose remaining recordings they can, and could leave two
    surviving cards showing the same ones; naming a card's first recordings before either cut lets
    a card that is no longer on the shelf keep its claim on one.
    """
    accessible = set(accessible_session_ids)
    cards = []
    for card in result.cards:
        session_ids = [session_id for session_id in card.session_ids if session_id in accessible]
        if session_ids:
            highlights = [highlight for highlight in card.highlights if highlight.session_id in accessible]
            cards.append(
                replace(card, recording_count=len(session_ids), session_ids=session_ids, highlights=highlights)
            )
    deduped = _drop_duplicate_recording_sets(cards)
    # Run again after the access cut, which can take the last finding off a shelf the scan built
    # with one.
    shelf = _findings_or_nothing(_assign_highlights(deduped), result.empty_reason)
    return replace(
        result,
        cards=shelf.cards,
        # A drop from a shelf that was then suppressed says nothing about DUPLICATE_CARD_OVERLAP.
        dropped_duplicate_cards=len(cards) - len(deduped) if shelf.cards else 0,
        empty_reason=shelf.empty_reason,
    )


def _has_finding(cards: list[ExperimentWatchCard]) -> bool:
    """A metric shortcut claims nothing about the variants, so it cannot hold a shelf up alone."""
    return any(card.kind != ExperimentWatchCardKind.METRIC for card in cards)


def _findings_or_nothing(
    cards: list[ExperimentWatchCard], empty_reason: Optional[ExperimentWatchEmptyReason]
) -> _Shelf:
    """The shelf, or nothing when only metric shortcuts are left on it.

    Shortcuts alone restate what the results tab already answers while reading as a finding.
    """
    if _has_finding(cards):
        return _Shelf(cards=cards, empty_reason=None)
    # No reason is set yet when the viewer's own recording access removed the findings. That case
    # must not be named: it would tell the viewer that recordings denied to them ran through this
    # experiment. "No recordings" is what they have either way.
    return _Shelf(cards=[], empty_reason=empty_reason or ExperimentWatchEmptyReason.NO_RECORDINGS)


def get_experiment_session_event_deltas(team: Team, user: User, experiment: Experiment) -> ExperimentWatchResult:
    """The recordings worth watching for this experiment, grouped into cards.

    The caller passes the result through `finalize_watch_cards` with the recordings this viewer may
    open, which is what the cached shelf is cut down to before it is returned.

    `user` is the viewer: exposure criteria can filter on arbitrary event properties, so the
    queries run under that user's property-level access control, as the experiment query runners
    do.

    Raises SessionEventDeltasUnavailable when nothing can be compared at all — an experiment that
    never launched, fewer than two variants, or an experiment the exposure linkage refuses (group
    aggregation, exposures still computing, a cohort still calculating).
    """
    # Exposure criteria over the events table is experiments' own logic and cost, so it bills to
    # experiments rather than to the tab it renders on.
    tag_queries(
        product=Product.EXPERIMENTS,
        feature=Feature.QUERY,
        team_id=team.pk,
        experiment_id=experiment.pk,
        experiment_query_surface="watch_shelf",
    )

    if experiment.start_date is None:
        raise SessionEventDeltasUnavailable("This experiment hasn't launched, so it has no exposed sessions yet.")

    if len(experiment.feature_flag.variants or []) < 2:
        raise SessionEventDeltasUnavailable("This experiment's feature flag defines fewer than two variants.")

    window_end = experiment.end_date or timezone.now()

    # Resolved once for the whole response, because each call reads the experiment's saved-metric
    # join again.
    metrics = resolve_metric_events(experiment)
    metric_event_names = _metric_event_names(metrics)
    # The same rollout resolution the analysis applies, resolved here for the cache key and the
    # exposure event's name; the population read inside the linkage resolves it again itself.
    default_exposure_event = resolve_default_exposure_event(team, experiment.start_date)
    multiple_variant_handling = get_multiple_variant_handling_from_experiment(experiment.exposure_criteria)

    cache_key = _cache_key(team, user, experiment, window_end, default_exposure_event)
    cached = get_safe_cache(cache_key)
    if cached is not None:
        return cached

    # The population the recordings list and the analysis count, refusals included. Runner work:
    # on precomputing teams this can run the preaggregation build synchronously, so it belongs
    # here, after the cache check and never inside an AST builder.
    try:
        linkage = resolve_exposure_linkage(team, experiment_id=experiment.pk, variant=None)
    except ValidationError as error:
        raise SessionEventDeltasUnavailable(_refusal_message(error))

    # The linkage's variant set drops experiment.excluded_variants: an excluded variant is not in
    # the analysis's population, so it must not appear on the shelf either.
    variant_keys = list(linkage.context.variants)
    if len(variant_keys) < 2:
        raise SessionEventDeltasUnavailable("This experiment's feature flag defines fewer than two variants.")

    # Metric events stay in — the module docstring's second rule. A card on one is labeled with its
    # metric instead of being held out of the ranking. The exposure event's own name is dropped:
    # its rate measures flag re-evaluation, not behavior. Criteria naming an action resolve to no
    # single event name, so nothing extra is dropped for them.
    exposure_event, _variant_property = get_exposure_event_and_property(
        linkage.context.feature_flag_key, experiment.exposure_criteria, default_exposure_event=default_exposure_event
    )
    excluded_events = sorted(UNCOMPARABLE_EVENTS | ({exposure_event} if exposure_event is not None else set()))

    modifiers = create_default_modifiers_for_team(team)
    setup = _QuerySetup(
        team=team,
        user=user,
        experiment=experiment,
        variant_keys=variant_keys,
        linkage=linkage,
        shared_hogql=SharedHogQLDatabase(
            # Postgres foreign-key lazy joins are the most expensive part of building the virtual
            # database and these queries only read events and replay summaries.
            database=Database.create_for(team=team, user=user, modifiers=modifiers, build_postgres_foreign_keys=False),
            modifiers=modifiers,
        ),
    )

    scan = _query_event_deltas(setup, excluded_events=excluded_events, window_end=window_end)

    variants = [
        ExperimentWatchVariant(key=key, persons=scan.persons.get(("", key), 0), sessions=scan.sessions.get(key, 0))
        for key in variant_keys
    ]
    compared_variant_keys = [variant.key for variant in variants if variant.persons >= MIN_VARIANT_PERSONS]
    too_early = len(compared_variant_keys) < 2

    # Empty variants read as too early, unless the people exposed have no sessions at all.
    no_comparison_reason = (
        ExperimentWatchEmptyReason.NO_SESSION_LINKED_EXPOSURES
        if scan.exposed_persons_without_session
        else ExperimentWatchEmptyReason.TOO_EARLY
    )
    shelf = (
        _Shelf(cards=[], empty_reason=no_comparison_reason)
        if too_early
        else _build_shelf(
            setup,
            scan=scan,
            metrics=metrics,
            compared_variant_keys=compared_variant_keys,
            never_linked=never_session_linked_events(team, frozenset(metric_event_names)),
        )
    )

    result = ExperimentWatchResult(
        cards=shelf.cards,
        variants=variants,
        multiple_variant_persons=scan.persons.get(("", MULTIPLE_VARIANT_KEY), 0),
        multiple_variant_handling=multiple_variant_handling.value,
        # Reported without the ones already left out for describing a mechanism: a reader pointed
        # at the results tab for `$pageview` would go looking for a row that was never coming
        # either way.
        metric_events=sorted(metric_event_names - UNCOMPARABLE_EVENTS),
        date_from=scan.enrollment.cutoff,
        date_to=scan.enrollment.enrolled_before,
        filter_test_accounts=linkage.population_filters_test_accounts,
        used_exposure_fallback=False,
        sessions_truncated=scan.enrollment.truncated,
        events_truncated=scan.events_truncated,
        min_variant_persons=MIN_VARIANT_PERSONS,
        max_card_recordings=MAX_CARD_RECORDINGS,
        # Settled per viewer in `finalize_watch_cards`; the cached shelf carries a placeholder.
        dropped_duplicate_cards=0,
        too_early=too_early,
        empty_reason=shelf.empty_reason,
    )
    safe_cache_set(cache_key, result, timeout=DELTA_CACHE_TTL)
    return result


def _refusal_message(error: ValidationError) -> str:
    """The linkage's human-readable refusal, unwrapped from DRF's detail structure."""
    detail = error.detail
    if isinstance(detail, list) and detail:
        return str(detail[0])
    return str(detail)


@frozen
class _TimeRange:
    start: datetime
    end: datetime


@frozen
class _EnrollmentMinute:
    """One minute of enrollment: when it began, and how many people of each variant were first
    exposed in it."""

    minute: datetime
    people_by_variant: dict[str, int]

    @property
    def people(self) -> int:
        return sum(self.people_by_variant.values())


@frozen
class _ComparedEnrollment:
    """The people one comparison covers, and the stretches of time their first sessions can fall in.

    Everyone first exposed at or after `cutoff` and before `enrolled_before` is compared, with no
    gaps between them: the walk admits a run of minutes, so the pair is a stretch of enrollment
    rather than a hull around scattered people. When nobody has been exposed both sit on the
    window end, so the compared span is empty rather than a claim about a stretch nobody was read
    in. The ranges are newest first, the order the walk admits them in, and disjoint; they reach up
    to the horizon past `enrolled_before`, because each compared person is read from the session
    they met the change in.
    """

    cutoff: datetime
    # One minute past the newest first exposure the comparison covers, so the people read are
    # exactly the ones counted below: the newest minutes are held back while an experiment runs,
    # and an exposure can land between the nomination query and the queries that follow it.
    enrolled_before: datetime
    persons: int
    # How many people each variant enrolled inside the compared stretch, whether or not they
    # turned out to have a session. What the settling holdback decides from, because a variant
    # whose people are exposed without a browser session enrolled them all the same.
    persons_by_variant: dict[str, int]
    ranges: tuple[_TimeRange, ...]
    # True when more people were exposed than one comparison covers, so the oldest enrollees were
    # left out.
    truncated: bool


@frozen
class SessionEventDeltaScan:
    """What the scan returned: per (event name, variant) the number of that variant's exposed people
    who did it in their first session after exposure, plus each variant's own totals under the empty
    event name."""

    persons: dict[tuple[str, str], int]
    sessions: dict[str, int]
    events_truncated: bool
    # Who the scan compared and over which stretches of time. Carried here rather than recomputed
    # by the caller: it is a property of the scan, and reporting the experiment's own window
    # instead would claim coverage that never happened.
    enrollment: _ComparedEnrollment
    # True when the comparison chose exposed people and none of them has a session the scan can
    # see within the horizon of their first exposure. More time alone cannot fix it: more traffic
    # captured the same way produces more exposed people without sessions.
    exposed_persons_without_session: bool


@dataclass(frozen=True)
class _QuerySetup:
    """Everything the scan and the recordings query share: the experiment's resolved exposure
    linkage and one HogQL database. The ast builders are methods so every use site gets a fresh
    tree — the HogQL resolver annotates nodes in place, so one instance can't appear in two
    clauses."""

    team: Team
    user: User
    experiment: Experiment
    variant_keys: list[str]
    linkage: ExperimentExposureLinkage
    shared_hogql: SharedHogQLDatabase

    def population(self) -> ast.SelectQuery:
        # One row per exposed distinct id: (distinct_id, person_id, variant, first_exposure_time).
        # MULTIPLE_VARIANT_KEY rows stay in so the caption can count the people the analysis set
        # aside; the comparison never reads them, because compared variants carry real keys only.
        return exposed_persons_select(self.linkage, include_multiple_variant=True)

    def compared_population(self, enrollment: _ComparedEnrollment) -> ast.SelectQuery:
        """The population narrowed to the people the comparison covers: everyone first exposed
        inside the compared enrollment. Wrapped rather than filtered inside the linkage's select,
        whose first_exposure_time is an aggregate of its own GROUP BY."""
        return ast.SelectQuery(
            select=[
                ast.Field(chain=["distinct_id"]),
                ast.Field(chain=["person_id"]),
                ast.Field(chain=["variant"]),
                ast.Field(chain=["first_exposure_time"]),
            ],
            select_from=ast.JoinExpr(table=self.population()),
            where=ast.And(
                exprs=[
                    ast.CompareOperation(
                        op=ast.CompareOperationOp.GtEq,
                        left=ast.Field(chain=["first_exposure_time"]),
                        right=ast.Constant(value=enrollment.cutoff),
                    ),
                    ast.CompareOperation(
                        op=ast.CompareOperationOp.Lt,
                        left=ast.Field(chain=["first_exposure_time"]),
                        right=ast.Constant(value=enrollment.enrolled_before),
                    ),
                ]
            ),
        )

    def population_joined(self, left_alias: str, *, enrollment: _ComparedEnrollment) -> ast.JoinExpr:
        """A join of the compared population onto `left_alias`, keyed on distinct id.

        GLOBAL for the same reason the recordings list joins this population GLOBAL: the subquery
        scans events over the whole experiment window (or the preaggregated table), and without it
        every shard would re-evaluate that scan independently.
        """
        return ast.JoinExpr(
            join_type="GLOBAL INNER JOIN",
            table=self.compared_population(enrollment),
            alias="population",
            constraint=ast.JoinConstraint(
                expr=ast.CompareOperation(
                    op=ast.CompareOperationOp.Eq,
                    left=ast.Field(chain=["population", "distinct_id"]),
                    right=ast.Field(chain=[left_alias, "distinct_id"]),
                ),
                constraint_type="ON",
            ),
        )

    def row_conditions(self, enrollment: _ComparedEnrollment) -> list[ast.Expr]:
        """The rows every query in this family reads: inside one of the compared stretches of
        time, carrying a session id because every query compares sessions, and within the horizon
        after the joined person's own first exposure.

        The stretches bound what ClickHouse reads; the per-person bound decides what counts. At most
        about fifteen stretches, because each disjoint one costs at least one horizon of the day
        budget.

        No test-account filter here: the population applies the experiment's test-account filters
        at the person level, so a per-row filter could only hide behavior of people the analysis
        counts. The recordings list skips re-applying them for the same reason
        (`linkage.population_filters_test_accounts`).
        """
        stretches: list[ast.Expr] = [
            ast.And(
                exprs=[
                    ast.CompareOperation(
                        op=ast.CompareOperationOp.GtEq,
                        left=ast.Field(chain=["timestamp"]),
                        right=ast.Constant(value=time_range.start),
                    ),
                    ast.CompareOperation(
                        op=ast.CompareOperationOp.LtEq,
                        left=ast.Field(chain=["timestamp"]),
                        right=ast.Constant(value=time_range.end),
                    ),
                ]
            )
            for time_range in enrollment.ranges
        ]
        return [
            ast.Or(exprs=stretches) if len(stretches) > 1 else stretches[0],
            ast.CompareOperation(
                # An SDK that sends `$session_id: null` leaves the string "null" in the column,
                # because the materialization keeps the raw JSON token. That string is a non-empty
                # value, so it needs its own exclusion. Without it, every such event of every
                # exposed person groups into one session whose earliest event can precede any real
                # session's, and the scan reads people from a session they never had. HogQL's own
                # property read of `$session_id` treats both values as absent.
                op=ast.CompareOperationOp.NotIn,
                left=ast.Field(chain=["$session_id"]),
                right=ast.Constant(value=["", "null"]),
            ),
            ast.CompareOperation(
                op=ast.CompareOperationOp.GtEq,
                left=ast.Field(chain=["timestamp"]),
                right=ast.Field(chain=["population", "first_exposure_time"]),
            ),
            ast.CompareOperation(
                op=ast.CompareOperationOp.Lt,
                left=ast.Field(chain=["timestamp"]),
                right=ast.ArithmeticOperation(
                    op=ast.ArithmeticOperationOp.Add,
                    left=ast.Field(chain=["population", "first_exposure_time"]),
                    right=ast.Call(name="toIntervalHour", args=[ast.Constant(value=FIRST_SESSION_HORIZON_HOURS)]),
                ),
            ),
        ]

    def run(self, query: ast.SelectQuery) -> list[tuple]:
        # Every query here aggregates over the exposed population, and the answer is cached for the
        # whole TTL, so a partial result would be served as complete and the ranking would read the
        # rows that never arrived as a difference between the variants. A "break" timeout profile
        # answers a query that hits the execution-time limit with exactly that, so the kill has to
        # throw instead. Same posture the recordings list takes on its evidence scan.
        #
        # The linkage carries a memory ceiling exactly where its population read needs explicit
        # bounding (activation mode); honored the way the recordings list honors it. None everywhere
        # else leaves the ceiling to the cloud config.
        settings = HogQLGlobalSettings(
            timeout_overflow_mode="throw",
            max_memory_usage=self.linkage.live_scan_max_memory_bytes,
        )
        response = execute_hogql_query(
            query,
            team=self.team,
            user=self.user,
            context=self.shared_hogql.fresh_context(self.team, self.user),
            modifiers=self.shared_hogql.modifiers,
            settings=settings,
        )
        return list(response.results or [])


def _cache_key(
    team: Team,
    user: User,
    experiment: Experiment,
    window_end: datetime,
    default_exposure_event: str,
) -> str:
    # The version segment must be bumped whenever ExperimentWatchResult or SessionEventDeltaScan
    # changes shape: entries are pickled, so a deploy would otherwise restore instances missing the
    # new fields.
    spec = json.dumps(
        [
            # Which event the default exposure resolved to. The $experiment_exposure rollout can
            # flip under a team, and cards computed on the other event are a different population.
            default_exposure_event,
            # The window end moves with wall-clock time on a running experiment, so it is quantized
            # to the cache's own TTL rather than to the minute: at minute resolution the key changes
            # every minute and the entry it would have reused is still warm, so every reload pays
            # for a full scan. The cost of the coarser bucket is that an answer can be one TTL
            # stale, which is what the TTL already promised.
            int(window_end.timestamp()) // DELTA_CACHE_TTL,
            # The experiment's metrics decide which cards carry a metric label and which events
            # get shortcut cards, its exposure criteria decide who is compared and how someone who
            # saw two variants is split, and the flag's variants decide what is compared. All of them are
            # editable while an entry is warm, and none can be re-applied on read, so an edit has
            # to miss the cache rather than be served the answer to the previous configuration.
            experiment.updated_at.isoformat(),
            experiment.feature_flag.updated_at.isoformat() if experiment.feature_flag.updated_at else None,
            # A saved metric is editable without touching the experiment row, and its events decide
            # metric labels and shortcut cards the same way an inline metric's do.
            sorted(updated.isoformat() for updated in experiment.saved_metrics.values_list("updated_at", flat=True)),
            # Property restrictions are compiled into the SQL, so a restriction change has to miss
            # the cache rather than be re-applied on read.
            [
                {
                    "name": restriction.name,
                    "property_type": restriction.property_type,
                    "group_type_index": restriction.group_type_index,
                }
                for restriction in sort_restricted_properties(
                    get_restricted_properties_with_group_type_index_for_team(user=user, team=team)
                )
            ],
        ]
    )
    digest = hashlib.sha256(spec.encode()).hexdigest()[:16]
    # Keyed by the viewer's restriction profile, not by the viewer: the property restrictions in
    # the digest are the only viewer-dependent input to the scan, and per-recording access is
    # applied on read. One viewer's scan then serves every viewer whose restrictions match, which
    # on the heaviest read in this family is the difference between paying it once per team per
    # TTL and once per viewer.
    return f"experiment_session_event_deltas_v13_{team.pk}_{experiment.pk}_{digest}"


def _metric_event_names(metrics: list[MetricEventSource]) -> set[str]:
    """Every named event this experiment's metrics count.

    Collected for the session-linkability lookup and for labeling the cards these events earn;
    they stay in the comparison itself. Sources with no single event name (actions, all-events
    nodes) are skipped — they can match client-captured events, so their identity can't be decided
    from a name.
    """
    return {
        source.node.event
        for metric in metrics
        for source in metric.sources
        if isinstance(source.node, EventsNode) and source.node.event
    }


def _nominate_enrollment(setup: _QuerySetup, *, window_end: datetime) -> _ComparedEnrollment:
    """Which exposed people this comparison covers, decided from the population alone.

    Read as per-minute counts of first exposures split by variant, newest minute first, rather than
    one row per person: the walk below only needs to know how many people of each variant a stretch
    of time admits, minute buckets bound the result by how many minutes enrolled anyone rather than
    by how many people the run has, and they make the cutoff a clean boundary the population filter
    can reproduce exactly. MULTIPLE_VARIANT_KEY people are in, so the count of people the analysis
    set aside is taken over the same people the comparison reads.
    """
    first_exposures = ast.SelectQuery(
        select=[
            # One value per person by construction: the exposure linkage stamps a person's first
            # exposure, and the variant the analysis attributes them, on every one of their
            # distinct ids.
            ast.Alias(
                alias="first_exposure", expr=ast.Call(name="min", args=[ast.Field(chain=["first_exposure_time"])])
            ),
            ast.Alias(alias="variant", expr=ast.Call(name="any", args=[ast.Field(chain=["variant"])])),
        ],
        select_from=ast.JoinExpr(table=setup.population()),
        group_by=[ast.Field(chain=["person_id"])],
    )
    minutes = ast.SelectQuery(
        select=[
            ast.Alias(
                alias="minute", expr=ast.Call(name="toStartOfMinute", args=[ast.Field(chain=["first_exposure"])])
            ),
            ast.Field(chain=["variant"]),
            ast.Alias(alias="people", expr=ast.Call(name="count", args=[])),
        ],
        select_from=ast.JoinExpr(table=first_exposures),
        group_by=[ast.Field(chain=["minute"]), ast.Field(chain=["variant"])],
        order_by=[ast.OrderExpr(expr=ast.Field(chain=["minute"]), order="DESC")],
        # Every row carries at least one person, and one minute holds at most one row per variant
        # plus one for the people who saw several. A result this long therefore has more people in
        # its complete minutes than the person cap, so the walk stops before the oldest minute,
        # whose rows the limit may have cut in half. One row past that, so a run whose enrollment
        # exactly fills the cap is not read as cut short.
        limit=ast.Constant(value=MAX_DELTA_SCAN_PERSONS + len(setup.variant_keys) + 2),
    )

    # Rows of one minute are adjacent, because the minute is the only sort key, so inserting them
    # in arrival order keeps the buckets newest first as well.
    people_by_minute: dict[datetime, dict[str, int]] = {}
    for minute, variant, people in setup.run(minutes):
        people_by_minute.setdefault(minute, {})[str(variant)] = int(people)
    buckets = [
        _EnrollmentMinute(minute=minute, people_by_variant=people_by_variant)
        for minute, people_by_variant in people_by_minute.items()
    ]

    def plan(walked: list[_EnrollmentMinute]) -> _ComparedEnrollment:
        return _plan_compared_enrollment(
            walked,
            window_end=window_end,
            horizon=timedelta(hours=FIRST_SESSION_HORIZON_HOURS),
            day_budget=timedelta(days=MAX_DELTA_SCAN_DAYS),
            person_cap=MAX_DELTA_SCAN_PERSONS,
        )

    if setup.experiment.end_date is None:
        # An experiment that is still running is read from settled enrollment where there is any,
        # for the reason FIRST_SESSION_SETTLING_HOURS gives. Nothing is held back once that would
        # leave fewer than two variants with enough people to compare, which is the case on an
        # experiment whose whole enrollment is minutes old.
        settled = plan(
            [bucket for bucket in buckets if bucket.minute < window_end - timedelta(hours=FIRST_SESSION_SETTLING_HOURS)]
        )
        if len(_enrolled_variant_keys(settled, setup.variant_keys)) >= 2:
            return settled
    return plan(buckets)


def _enrolled_variant_keys(enrollment: _ComparedEnrollment, variant_keys: list[str]) -> list[str]:
    """The variants with enough people in the compared enrollment to be compared at all.

    Counted over the people the variant enrolled, not over the ones that turned out to have a
    session: whether a variant enrolled is a fact about the rollout, and a variant whose people are
    all exposed server-side has no session for any of them.
    """
    return [key for key in variant_keys if enrollment.persons_by_variant.get(key, 0) >= MIN_VARIANT_PERSONS]


def _plan_compared_enrollment(
    buckets: list[_EnrollmentMinute],
    *,
    window_end: datetime,
    horizon: timedelta,
    day_budget: timedelta,
    person_cap: int,
) -> _ComparedEnrollment:
    """Walk the enrollment minutes newest first, admitting whole minutes until the person cap is
    reached or the union of the admitted stretches would exceed the day budget.

    A minute's stretch runs from its start to one minute plus the horizon later, because the
    people first exposed in it are read up to the horizon after their own first exposure. Stretches
    that overlap merge, so a busy week costs the budget once however many minutes it holds, and a
    gap between two bursts of enrollment costs nothing. A whole minute is admitted or none of it,
    so the compared set can exceed the person cap by as many people as one minute of enrollment
    holds, which on a project that enrolls thousands of people a minute is thousands. The cap
    absorbs that because it bounds aggregation state rather than the rows read. Free of ClickHouse
    so the cost bound can be tested on its own.
    """
    ranges: list[_TimeRange] = []
    covered = timedelta(0)
    persons = 0
    persons_by_variant: dict[str, int] = {}
    cutoff = window_end
    truncated = False
    for bucket in buckets:
        # An exposure can land between this module reading the clock and the population reading
        # it, so the newest minute can start past the window end; nothing is read past it.
        start = min(bucket.minute, window_end)
        end = min(start + timedelta(minutes=1) + horizon, window_end)
        # Older than every admitted minute, so it can only touch the oldest range, at its start.
        merges = bool(ranges) and end >= ranges[-1].start
        extra = ranges[-1].start - start if merges else end - start
        if persons >= person_cap or covered + extra > day_budget:
            truncated = True
            break
        if merges:
            ranges[-1] = replace(ranges[-1], start=start)
        else:
            ranges.append(_TimeRange(start=start, end=end))
        covered += extra
        persons += bucket.people
        for variant, people in bucket.people_by_variant.items():
            persons_by_variant[variant] = persons_by_variant.get(variant, 0) + people
        cutoff = start
    return _ComparedEnrollment(
        cutoff=cutoff,
        # Whole minutes at both ends. The newest minute the walk was given is always admitted, so
        # one minute past its start is where the people it counted stop.
        enrolled_before=min(buckets[0].minute, window_end) + timedelta(minutes=1) if ranges else window_end,
        persons=persons,
        persons_by_variant=persons_by_variant,
        ranges=tuple(ranges),
        truncated=truncated,
    )


def _query_event_deltas(
    setup: _QuerySetup,
    *,
    excluded_events: list[str],
    window_end: datetime,
) -> SessionEventDeltaScan:
    enrollment = _nominate_enrollment(setup, window_end=window_end)
    if not enrollment.persons:
        # Nobody has been exposed yet, so there is nothing to read and nobody to be out of sight.
        return SessionEventDeltaScan(
            persons={},
            sessions={},
            events_truncated=False,
            enrollment=enrollment,
            exposed_persons_without_session=False,
        )

    # One row per (person, session) among the rows the person fired from their first exposure on,
    # within the horizon: the variant the population attributes to them, when their part of the
    # session began, and the comparable event names their rows in it contain, merged across the
    # person's distinct ids by the GROUP BY. The join runs on the exposed person's own distinct
    # ids, so a session that a second, unexposed person shares never produces a row for that
    # person, and rows other people fired into the session never reach their event names. The
    # uncomparable names are dropped here rather than in the WHERE so a session whose only events
    # are excluded ones still produces a row and its person still counts toward their variant's
    # total.
    person_sessions = ast.SelectQuery(
        select=[
            ast.Alias(alias="person_id", expr=ast.Field(chain=["population", "person_id"])),
            # Exact, not an arbitrary pick: the population attributes each person exactly one
            # variant, carried on every one of their distinct ids.
            ast.Alias(alias="variant", expr=ast.Call(name="any", args=[ast.Field(chain=["population", "variant"])])),
            ast.Alias(alias="started", expr=ast.Call(name="min", args=[ast.Field(chain=["timestamp"])])),
            ast.Alias(
                alias="event_names",
                expr=ast.Call(
                    name="groupUniqArrayIf",
                    args=[
                        ast.Field(chain=["event"]),
                        ast.And(
                            exprs=[
                                ast.Call(name="notEmpty", args=[ast.Field(chain=["event"])]),
                                ast.Call(
                                    name="not",
                                    args=[
                                        ast.Call(
                                            name="has",
                                            args=[
                                                ast.Constant(value=excluded_events),
                                                ast.Field(chain=["event"]),
                                            ],
                                        )
                                    ],
                                ),
                            ]
                        ),
                    ],
                ),
            ),
        ],
        select_from=ast.JoinExpr(
            table=ast.Field(chain=["events"]),
            next_join=setup.population_joined("events", enrollment=enrollment),
        ),
        where=ast.And(exprs=setup.row_conditions(enrollment)),
        group_by=[ast.Field(chain=["population", "person_id"]), ast.Field(chain=["$session_id"])],
    )

    # One row per exposed person: which variant they saw, how many sessions of theirs fall in the
    # horizon, and what they did in the first of them. Only the first, because the variants don't
    # necessarily get the same number of sessions per person, and a person seen in seven sessions
    # has seven times the chance to have done anything than one seen in two. The module docstring's
    # "One person, one session" carries the measurement behind that.
    person_rows = ast.SelectQuery(
        select=[
            # One value per person by construction: the population attributes each person exactly
            # one variant over the whole experiment window.
            ast.Alias(alias="variant", expr=ast.Call(name="any", args=[ast.Field(chain=["variant"])])),
            ast.Alias(alias="session_count", expr=ast.Call(name="count", args=[])),
            ast.Alias(
                alias="event_names",
                expr=ast.Call(name="argMin", args=[ast.Field(chain=["event_names"]), ast.Field(chain=["started"])]),
            ),
        ],
        select_from=ast.JoinExpr(table=person_sessions),
        group_by=[ast.Field(chain=["person_id"])],
    )

    # The empty event name is the variant's own totals. They ride the same aggregation so the
    # denominator can never be computed over a different set than the numerators — and `notEmpty`
    # above guarantees no real event name collides with it.
    query = ast.SelectQuery(
        select=[
            ast.Alias(
                alias="event_name",
                expr=ast.Call(
                    name="arrayJoin",
                    args=[
                        ast.Call(
                            name="arrayConcat",
                            args=[ast.Array(exprs=[ast.Constant(value="")]), ast.Field(chain=["event_names"])],
                        )
                    ],
                ),
            ),
            ast.Field(chain=["variant"]),
            ast.Alias(alias="persons", expr=ast.Call(name="count", args=[])),
            ast.Alias(alias="sessions", expr=ast.Call(name="sum", args=[ast.Field(chain=["session_count"])])),
        ],
        select_from=ast.JoinExpr(table=person_rows),
        # Every variant, not only the qualifying ones: the totals are what decide which variants qualify at
        # all, and on a three-variant experiment a total over two variants could never reach the ceiling.
        group_by=[ast.Field(chain=["event_name"]), ast.Field(chain=["variant"])],
        # By name, not by count: every variant's rows for one event stay adjacent, so hitting the
        # ceiling drops whole events instead of leaving an event with one variant's count and a silent
        # zero for the others.
        order_by=[
            ast.OrderExpr(expr=ast.Field(chain=["event_name"]), order="ASC"),
            ast.OrderExpr(expr=ast.Field(chain=["variant"]), order="ASC"),
        ],
        # One past the cap, so a result that lands exactly on it isn't read as cut short.
        limit=ast.Constant(value=MAX_DELTA_EVENT_ROWS + 1),
    )

    rows = [(str(row[0]), str(row[1]), int(row[2]), int(row[3])) for row in setup.run(query)]
    events_truncated = len(rows) > MAX_DELTA_EVENT_ROWS
    if events_truncated:
        # The ceiling can land between an event's variant rows, which would read as one variant never
        # having done it. Dropping the last event name is exact rather than nearly right.
        last_event = rows[-1][0]
        rows = [row for row in rows if row[0] != last_event]

    return SessionEventDeltaScan(
        persons={(event_name, variant): persons for event_name, variant, persons, _sessions in rows},
        sessions={variant: sessions for event_name, variant, _persons, sessions in rows if event_name == ""},
        events_truncated=events_truncated,
        enrollment=enrollment,
        # People were chosen, so no row at all means none of them has a session in sight. It
        # separates "nobody exposed yet", which is worth waiting for, from "the people exposed have
        # no sessions we can see", which more of the same capture never fixes.
        exposed_persons_without_session=not rows,
    )


def _build_shelf(
    setup: _QuerySetup,
    *,
    scan: SessionEventDeltaScan,
    metrics: list[MetricEventSource],
    compared_variant_keys: list[str],
    never_linked: frozenset[str],
) -> _Shelf:
    """The cards this comparison earned, or the reason it earned none.

    Returns before the recordings lookups when the ranking finds no candidate: only shortcuts
    would be left, and a shortcut-only shelf is not shown.
    """
    named_metric_events, nodes_by_metric_event = _metric_events_by_name(metrics, setup.experiment)
    comparison_candidates = _pick_behavior_cards(
        scan,
        compared_variant_keys=compared_variant_keys,
        metric_names_by_event={named.event: named.metric_name for named in named_metric_events},
    )
    if not comparison_candidates:
        return _Shelf(cards=[], empty_reason=ExperimentWatchEmptyReason.NO_SEPARATION)

    metric_cards = _metric_card_candidates(
        named_metric_events,
        compared_variant_keys=compared_variant_keys,
        never_linked=never_linked,
        # An event that already won a comparison card is not offered a second time as a shortcut to
        # the same recordings, which on a two-metric experiment would be half the shelf restating
        # the other half.
        carded_events={candidate.event for candidate in comparison_candidates},
    )
    # A metric's property filters narrow the recordings behind its *shortcut* cards only. A
    # comparison card was ranked on the bare event name, so filtering its recordings would show a
    # narrower set than the one that earned it the card, and could leave it with none.
    resolved = _resolve_cards(
        setup,
        candidates=[*comparison_candidates, *metric_cards],
        metric_nodes=_shortcut_nodes(metric_cards, nodes_by_metric_event),
        enrollment=scan.enrollment,
    )
    comparison_cards = [card for card in resolved if card.kind != ExperimentWatchCardKind.METRIC]
    if not _has_finding(resolved):
        # Every finding died on the replay existence check; the surviving shortcuts alone are not
        # shown, so the shortcut recovery below has nothing to fill.
        return _Shelf(cards=[], empty_reason=ExperimentWatchEmptyReason.NO_RECORDINGS)
    shortcut_by_pair = {
        (card.event, card.variant): card for card in resolved if card.kind == ExperimentWatchCardKind.METRIC
    }

    # The shortcut selection is decided again now that survival is known: a comparison candidate
    # that died on the replay existence check must not keep suppressing its event's shortcuts, or
    # an event the experiment measures vanishes from the shelf just because the one variant that earned
    # its comparison card had nothing recorded. Re-running the selection, rather than appending a
    # recovery batch, keeps the shelf inside MAX_METRIC_CARD_EVENTS and keeps a recovered event at
    # its display-order position instead of after lower-ranked ones, which can also displace a
    # lower-ranked event's already-resolved shortcut cards.
    final_shortcuts = _metric_card_candidates(
        named_metric_events,
        compared_variant_keys=compared_variant_keys,
        never_linked=never_linked,
        carded_events={card.event for card in comparison_cards},
    )
    queried_events = {card.event for card in metric_cards}
    unqueried = [card for card in final_shortcuts if card.event not in queried_events]
    if unqueried:
        shortcut_by_pair.update(
            {
                (card.event, card.variant): card
                for card in _resolve_cards(
                    setup,
                    candidates=unqueried,
                    metric_nodes=_shortcut_nodes(unqueried, nodes_by_metric_event),
                    enrollment=scan.enrollment,
                )
            }
        )
    return _Shelf(
        cards=comparison_cards
        + [
            shortcut_by_pair[(card.event, card.variant)]
            for card in final_shortcuts
            if (card.event, card.variant) in shortcut_by_pair
        ],
        empty_reason=None,
    )


def _separation(
    *, baseline_count: int, target_count: int, baseline_persons: int, target_persons: int
) -> tuple[float, float]:
    """The smoothed ratio of the two populations' rates, and how much of it survives the noise.

    The second number is the conservative end of the ratio in log space: the difference minus the
    uncertainty in it, so an event two people did more of in one variant cannot outrank one hundreds
    did, however lopsided the raw ratio looks. Zero means the populations are indistinguishable on
    it.
    """
    baseline_share = (baseline_count + RATIO_SMOOTHING) / (baseline_persons + 1)
    target_share = (target_count + RATIO_SMOOTHING) / (target_persons + 1)
    ratio = target_share / baseline_share

    # Standard error of the log of a ratio of two independent proportions. Independent is why the
    # unit is the person: two sessions of one person are not, and treating them as such is what
    # makes an A/A comparison look full of findings.
    variance = (
        1 / (baseline_count + RATIO_SMOOTHING)
        + 1 / (target_count + RATIO_SMOOTHING)
        - 1 / (baseline_persons + 1)
        - 1 / (target_persons + 1)
    )
    standard_error = math.sqrt(max(variance, 0.0))
    return ratio, max(abs(math.log(ratio)) - CONFIDENCE_Z * standard_error, 0.0)


def _strength(*, separation: float, baseline_count: int, target_count: int) -> ExperimentWatchCardStrength:
    """Which band the difference falls in, read off the conservative end of it rather than the raw
    one. A card that is only past the floor because the sample is large says "slightly", however
    big the point estimate looks."""
    if not baseline_count or not target_count:
        return ExperimentWatchCardStrength.ONLY
    if separation >= FAR_MORE_LOG_RATIO:
        return ExperimentWatchCardStrength.FAR_MORE
    if separation >= MORE_LOG_RATIO:
        return ExperimentWatchCardStrength.MORE
    return ExperimentWatchCardStrength.SLIGHTLY_MORE


def _card_kind(
    *, event_name: str, target_count: int, target_persons: int, baseline_count: int, baseline_persons: int
) -> ExperimentWatchCardKind:
    """Which shelf a comparison card belongs on.

    Friction is decided first and beats everything: an error only the new variant throws is the
    single most useful thing this surface can find, and routing it to the variant's-own-rendering
    shelf on the strength of the same evidence would bury it.

    Everything else turns on how much of the event the other variants are missing rather than on the
    ratio. A ratio alone can't tell "almost nobody else did it" from "nobody else could": both look
    enormous, and the second is the variant rendering something the others never had. Comparing the
    other variants' occurrences against the number this variant's rate predicts for them separates the two,
    and it needs the prediction to be large before an absence means anything at all.
    """
    if event_name in FRICTION_EVENTS:
        return ExperimentWatchCardKind.FRICTION
    expected_baseline = target_count / target_persons * baseline_persons
    if (
        expected_baseline >= VARIANT_ONLY_MIN_EXPECTED
        and baseline_count <= expected_baseline * VARIANT_ONLY_MAX_LEAKAGE
    ):
        return ExperimentWatchCardKind.VARIANT_ONLY
    return ExperimentWatchCardKind.BEHAVIOR


def _pick_behavior_cards(
    scan: SessionEventDeltaScan, *, compared_variant_keys: list[str], metric_names_by_event: dict[str, str]
) -> list[ExperimentWatchCard]:
    """The events one variant did clearly more than the other variants pooled, strongest first.

    One card per event at most, on the variant where it is most over-represented — an event five variants
    share is nobody's finding, and an event one variant lacks shows up as the other variants' card. Pooling
    the rest is what makes a five-variant experiment cost the same ranking as two; on two variants it *is*
    the pairwise comparison.
    """
    variant_persons = {key: scan.persons.get(("", key), 0) for key in compared_variant_keys}
    total_persons = sum(variant_persons.values())
    event_names = {event_name for event_name, _variant in scan.persons if event_name != ""}

    picked: list[tuple[float, ExperimentWatchCard]] = []
    for event_name in sorted(event_names):
        counts = {key: scan.persons.get((event_name, key), 0) for key in compared_variant_keys}
        total_count = sum(counts.values())
        best: Optional[tuple[float, ExperimentWatchCard]] = None
        for key in compared_variant_keys:
            rest_persons = total_persons - variant_persons[key]
            rest_count = total_count - counts[key]
            if counts[key] < MIN_SUPPORT_PERSONS or not rest_persons:
                continue
            # Only where the variant over-indexes: the card's recordings live on the variant that did the
            # event, and under-indexing is the same fact seen from the other variants' cards.
            if counts[key] * rest_persons <= rest_count * variant_persons[key]:
                continue
            _ratio, separation = _separation(
                baseline_count=rest_count,
                target_count=counts[key],
                baseline_persons=rest_persons,
                target_persons=variant_persons[key],
            )
            if separation < MIN_LOG_RATIO_LOWER_BOUND:
                continue
            strength = _strength(separation=separation, baseline_count=rest_count, target_count=counts[key])
            card = ExperimentWatchCard(
                kind=_card_kind(
                    event_name=event_name,
                    target_count=counts[key],
                    target_persons=variant_persons[key],
                    baseline_count=rest_count,
                    baseline_persons=rest_persons,
                ),
                event=event_name,
                variant=key,
                strength=strength,
                metric_name=metric_names_by_event.get(event_name),
                recording_count=0,
                session_ids=[],
                highlights=[],
            )
            if best is None or separation > best[0]:
                best = (separation, card)
        if best is not None:
            picked.append(best)

    picked.sort(key=lambda entry: (-entry[0], entry[1].event))
    # Each shelf gets its own budget. Friction is capped by FRICTION_EVENTS instead, at one card per
    # name, because it is the first thing a reader triages; the variant's-own-rendering shelf is
    # capped low because it always sorts above everything else and is never the reason to open a
    # recording. Sharing one budget would let either of them push the whole behavior shelf out of
    # the response, which is the failure mode both splits exist to prevent.
    by_kind: dict[ExperimentWatchCardKind, list[ExperimentWatchCard]] = {}
    for _separation_value, card in picked:
        by_kind.setdefault(card.kind, []).append(card)
    return (
        by_kind.get(ExperimentWatchCardKind.BEHAVIOR, [])[:MAX_BEHAVIOR_CARDS]
        + by_kind.get(ExperimentWatchCardKind.FRICTION, [])
        + by_kind.get(ExperimentWatchCardKind.VARIANT_ONLY, [])[:MAX_VARIANT_ONLY_CARDS]
    )


def _shares_recordings(session_ids: set[str], other: set[str]) -> bool:
    """Whether one card's recordings are, near enough, already the other's.

    Measured against the smaller card rather than the union, so a card whose recordings all sit
    inside a longer one counts as a duplicate too.
    """
    return len(session_ids & other) >= min(len(session_ids), len(other)) * DUPLICATE_CARD_OVERLAP


def _drop_duplicate_recording_sets(cards: list[ExperimentWatchCard]) -> list[ExperimentWatchCard]:
    """The shelf with every card that only restates a higher-ranked card's recordings taken out.

    Two events a variant's people do together are ranked as two findings, because the comparison reads
    one event name at a time and both separate the variants. The reader gets one playlist twice, and on
    a redesign experiment, where a whole flow's events move together, that is most of the shelf.

    Compared within a shelf and never across them. An event a variant renders itself always
    co-occurs with the behavior it drives, so cutting the variant's-own-rendering card against the
    behavior card would empty the shelf built to hold it, and a metric shortcut is an offer to
    watch a metric event happen rather than a restatement of the finding it shares recordings with.

    Cards arrive in rank order, so the one kept is the one that earned its place. A dropped card is
    not replaced by the next-ranked candidate: candidates are capped before their recordings are
    resolved, and refilling would cost another round trip to find out whether the replacement can
    be backed by a recording at all.
    """
    kept: list[ExperimentWatchCard] = []
    seen_by_kind: dict[ExperimentWatchCardKind, list[set[str]]] = {}
    for card in cards:
        session_ids = set(card.session_ids)
        shelf = seen_by_kind.setdefault(card.kind, [])
        if any(_shares_recordings(session_ids, other) for other in shelf):
            continue
        shelf.append(session_ids)
        kept.append(card)
    return kept


def _assign_highlights(cards: list[ExperimentWatchCard]) -> list[ExperimentWatchCard]:
    """Each card cut down to the few recordings it names first, taken from its own ranking.

    The shelf's cards overlap by construction, so a recording an earlier card already names sorts
    last here rather than being dropped: no card is left without highlights, and no reader is sent
    to the same recording from every card on the shelf. Cards arrive in rank order, so the stronger
    card gets first claim on a recording both could name.
    """
    claimed: set[str] = set()
    assigned = []
    for card in cards:
        unclaimed = [highlight for highlight in card.highlights if highlight.session_id not in claimed]
        already = [highlight for highlight in card.highlights if highlight.session_id in claimed]
        highlights = (unclaimed + already)[:MAX_CARD_HIGHLIGHTS]
        claimed.update(highlight.session_id for highlight in highlights)
        assigned.append(replace(card, highlights=highlights))
    return assigned


def _metric_events_by_name(
    metrics: list[MetricEventSource], experiment: Experiment
) -> tuple[list[_MetricEvent], dict[str, list[EventsNode]]]:
    """Every named event the experiment's metrics count, paired with the metric that owns it, in the
    order the experiment's own metrics page lists them.

    That order rather than the order the metrics happen to be stored in: a reader who put the metric
    they care about first sees a shelf built around a different one otherwise, and on a production
    experiment that is exactly what happened — the event the experiment was built to move sat eighth
    in storage order and never reached the shelf.

    Also returns each event's source nodes from the metric that owns it, so the recordings lookup
    can honor that metric's property filters: the card is labeled with the metric's name, and a
    recording of the event happening outside the metric would be mislabeled.
    """
    rank = metric_display_rank(
        [
            *(experiment.primary_metrics_ordered_uuids or []),
            *(experiment.secondary_metrics_ordered_uuids or []),
        ]
    )

    named: list[_MetricEvent] = []
    owner_by_event: dict[str, str] = {}
    nodes_by_event: dict[str, list[EventsNode]] = {}
    for metric in sorted(metrics, key=lambda metric: rank(metric.metric_uuid)):
        for source in metric.sources:
            node = source.node
            if not isinstance(node, EventsNode) or not node.event:
                continue
            if node.event in UNCOMPARABLE_EVENTS:
                continue
            if node.event not in owner_by_event:
                owner_by_event[node.event] = metric.metric_uuid
                nodes_by_event[node.event] = [node]
                named.append(_MetricEvent(event=node.event, metric_name=metric.metric_name))
            elif owner_by_event[node.event] == metric.metric_uuid:
                # Another source of the owning metric on the same event — a funnel can repeat an
                # event across steps with different filters, and any of them counts as the metric.
                nodes_by_event[node.event].append(node)

    return named, nodes_by_event


def _metric_card_candidates(
    named_metric_events: list[_MetricEvent],
    *,
    compared_variant_keys: list[str],
    never_linked: frozenset[str],
    carded_events: set[str],
) -> list[ExperimentWatchCard]:
    """Shortcut cards to recordings around the experiment's own metric events, one per variant.

    No strength and no comparison claim: what happened to the metric is the results tab's answer.
    These cards only say "here is the metric's event happening on screen, in this variant". Events that
    have only ever been captured server-side can't back a recording and are skipped outright.
    """
    kept = [
        named for named in named_metric_events if named.event not in never_linked and named.event not in carded_events
    ][:MAX_METRIC_CARD_EVENTS]
    return [
        ExperimentWatchCard(
            kind=ExperimentWatchCardKind.METRIC,
            event=named.event,
            variant=variant_key,
            strength=None,
            metric_name=named.metric_name,
            recording_count=0,
            session_ids=[],
            highlights=[],
        )
        for named in kept
        for variant_key in compared_variant_keys
    ]


def _shortcut_nodes(
    metric_cards: list[ExperimentWatchCard], nodes_by_metric_event: dict[str, list[EventsNode]]
) -> dict[str, list[EventsNode]]:
    """The source nodes behind the shortcut cards in `metric_cards`, so the recordings lookup can
    apply their metrics' property filters to those cards only."""
    return {
        card.event: nodes_by_metric_event[card.event] for card in metric_cards if card.event in nodes_by_metric_event
    }


def _resolve_cards(
    setup: _QuerySetup,
    *,
    candidates: list[ExperimentWatchCard],
    metric_nodes: dict[str, list[EventsNode]],
    enrollment: _ComparedEnrollment,
) -> list[ExperimentWatchCard]:
    """The candidates that recordings can back, each carrying its recordings and highlights.

    A candidate without a single recording is dropped, not returned greyed-out: the deliverable is
    what can be watched, and replay sampling or retention already ate these sessions.
    """
    if not candidates:
        return []
    recordings = _recordings_for_cards(
        setup,
        wanted=[(candidate.event, candidate.variant) for candidate in candidates],
        enrollment=enrollment,
        metric_nodes=metric_nodes,
    )
    return [
        replace(
            candidate,
            recording_count=len(found.session_ids),
            session_ids=found.session_ids,
            highlights=found.highlights,
        )
        for candidate in candidates
        if (found := recordings.get((candidate.event, candidate.variant))) is not None
    ]


def _recordings_for_cards(
    setup: _QuerySetup,
    *,
    wanted: list[tuple[str, str]],
    enrollment: _ComparedEnrollment,
    metric_nodes: Optional[dict[str, list[EventsNode]]] = None,
) -> dict[tuple[str, str], _CardRecordings]:
    """Recent recorded sessions per (event, variant) pair, most recent first, and which of them to open
    first.

    Unlike the scan this prunes on event names, so it reads a sliver of the compared stretches. Every candidate
    then goes through replay's own existence check rather than being trusted because it was exposed:
    replay sampling, retention and deletion mean most exposed sessions have nothing to play, and a
    card is only as good as the recordings behind it.
    """
    wanted_events = sorted({event for event, _variant in wanted})
    wanted_variants = sorted({variant for _event, variant in wanted})
    # A metric card's event counts only where the metric's own property filters hold: the card
    # carries the metric's name, so a recording of the event happening outside the metric would be
    # mislabeled. An unfiltered source subsumes any filtered one on the same event, so an event
    # with one stays on the plain name match.
    filtered_nodes = {
        event: nodes
        for event, nodes in (metric_nodes or {}).items()
        if event in wanted_events and all(node.properties or node.fixedProperties for node in nodes)
    }
    plain_events = [event for event in wanted_events if event not in filtered_nodes]

    def card_event_match() -> ast.Expr:
        # Rebuilt per use site, like the setup's own builders: the HogQL resolver annotates ast
        # nodes in place, so one instance can't appear in two clauses of the same query.
        conditions: list[ast.Expr] = []
        if plain_events:
            conditions.append(
                ast.CompareOperation(
                    op=ast.CompareOperationOp.In,
                    left=ast.Field(chain=["event"]),
                    right=ast.Constant(value=plain_events),
                )
            )
        for nodes in filtered_nodes.values():
            conditions.extend(build_source_condition(node, setup.team) for node in nodes)
        if not conditions:
            return ast.Constant(value=False)
        return ast.Or(exprs=conditions) if len(conditions) > 1 else conditions[0]

    def highlight_signal_rows() -> ast.Expr:
        return ast.CompareOperation(
            op=ast.CompareOperationOp.In,
            left=ast.Field(chain=["event"]),
            right=ast.Constant(value=[event for event, _singular in HIGHLIGHT_SIGNALS]),
        )

    def highlight_count(event_name: str) -> ast.Expr:
        return ast.Call(
            name="countIf",
            args=[
                ast.CompareOperation(
                    op=ast.CompareOperationOp.Eq,
                    left=ast.Field(chain=["event"]),
                    right=ast.Constant(value=event_name),
                )
            ],
        )

    def event_condition(event_name: str) -> ast.Expr:
        # The same per-event terms card_event_match() is assembled from, so a filtered metric
        # event's repetition counts exactly the occurrences that made it into events_present.
        if event_name in filtered_nodes:
            conditions = [build_source_condition(node, setup.team) for node in filtered_nodes[event_name]]
            return ast.Or(exprs=conditions) if len(conditions) > 1 else conditions[0]
        return ast.CompareOperation(
            op=ast.CompareOperationOp.Eq, left=ast.Field(chain=["event"]), right=ast.Constant(value=event_name)
        )

    # The signal rows join the predicate rather than riding on a second query: they are three event
    # names, so ClickHouse still prunes this on the events table's primary key, and a session that
    # carries only signal rows contributes an empty `events_present` and drops out at the arrayJoin
    # below rather than polluting any card.
    wanted_event_rows = ast.Or(exprs=[card_event_match(), highlight_signal_rows()])

    # The variant comes from the joined population, never from an event row, so the wanted event
    # names are the whole predicate and ClickHouse prunes on the events table's primary key. The
    # population join also keeps the counted rows to the exposed persons' own: another person's
    # events in a shared session back no card.
    session_rows = ast.SelectQuery(
        select=[
            ast.Alias(alias="session_id", expr=ast.Field(chain=["$session_id"])),
            ast.Alias(alias="last_seen", expr=ast.Call(name="max", args=[ast.Field(chain=["timestamp"])])),
            # A session whose matched rows belong to people of two different variants belongs to no
            # card, because a recording can only show one experience. MULTIPLE_VARIANT_KEY is never
            # a wanted variant, so the outer WHERE drops such sessions, and it also drops the
            # sessions of people the analysis set aside as multiple-variant, who carry the key as
            # their attributed variant.
            ast.Alias(
                alias="variant",
                expr=ast.Call(
                    name="if",
                    args=[
                        ast.CompareOperation(
                            op=ast.CompareOperationOp.Gt,
                            left=ast.Call(name="uniqExact", args=[ast.Field(chain=["population", "variant"])]),
                            right=ast.Constant(value=1),
                        ),
                        ast.Constant(value=MULTIPLE_VARIANT_KEY),
                        ast.Call(name="any", args=[ast.Field(chain=["population", "variant"])]),
                    ],
                ),
            ),
            ast.Alias(
                alias="events_present",
                expr=ast.Call(
                    name="groupUniqArrayIf",
                    args=[ast.Field(chain=["event"]), card_event_match()],
                ),
            ),
            *(
                ast.Alias(alias=_highlight_alias(event), expr=highlight_count(event))
                for event, _singular in HIGHLIGHT_SIGNALS
            ),
            # How often the session fired each wanted event, one aggregated count per event rather
            # than an array of every occurrence: an occurrences array is unbounded on a hot event,
            # while these keep the per-session aggregation state bounded by the distinct wanted
            # names, the same shape the highlight-signal counts use.
            *(
                ast.Alias(alias=_repetition_alias(index), expr=ast.Call(name="countIf", args=[event_condition(event)]))
                for index, event in enumerate(wanted_events)
            ),
        ],
        select_from=ast.JoinExpr(
            table=ast.Field(chain=["events"]),
            next_join=setup.population_joined("events", enrollment=enrollment),
        ),
        where=ast.And(exprs=[*setup.row_conditions(enrollment), wanted_event_rows]),
        group_by=[ast.Field(chain=["$session_id"])],
    )

    candidates_query = ast.SelectQuery(
        select=[
            ast.Alias(alias="event_name", expr=ast.Call(name="arrayJoin", args=[ast.Field(chain=["events_present"])])),
            ast.Field(chain=["variant"]),
            ast.Field(chain=["session_id"]),
            *(ast.Field(chain=[_highlight_alias(event)]) for event, _singular in HIGHLIGHT_SIGNALS),
            *(ast.Field(chain=[_repetition_alias(index)]) for index in range(len(wanted_events))),
        ],
        select_from=ast.JoinExpr(table=session_rows),
        # Drops the MULTIPLE_VARIANT_KEY sessions (see the variant column above). A card's
        # recordings still come from any of a compared person's sessions in the horizon, not only
        # their first: the comparison needs a fair population, the watchlist needs the behavior on
        # screen, the module docstring's split.
        where=ast.CompareOperation(
            op=ast.CompareOperationOp.In, left=ast.Field(chain=["variant"]), right=ast.Constant(value=wanted_variants)
        ),
        order_by=[ast.OrderExpr(expr=ast.Field(chain=["last_seen"]), order="DESC")],
        limit_by=ast.LimitByExpr(
            n=ast.Constant(value=MAX_CARD_RECORDING_CANDIDATES),
            exprs=[ast.Field(chain=["event_name"]), ast.Field(chain=["variant"])],
        ),
        # Sized on what LIMIT BY can emit, not on the cards asked for. The arrayJoin produces every
        # (wanted event, wanted variant) pair that occurs, which is more pairs than there are cards —
        # a carded event also happens in the variants that didn't earn a card. LIMIT runs after LIMIT BY
        # and cuts by recency across all of them, so a limit sized on the cards would drop a card's
        # older recordings in favor of rows belonging to a pair nobody asked about, and the card
        # would then be dropped as unbacked.
        limit=ast.Constant(value=MAX_CARD_RECORDING_CANDIDATES * max(len(wanted_events) * len(wanted_variants), 1)),
    )

    # The query emits every (wanted event, wanted variant) pair that occurs — a carded event also
    # happens in variants that earned no card — but only the pairs a card actually asked for go on to
    # the replay existence check, which pays per id.
    wanted_pairs = set(wanted)
    repetition_index = {event: index for index, event in enumerate(wanted_events)}
    signal_base = 3
    repetition_base = signal_base + len(HIGHLIGHT_SIGNALS)
    candidates: dict[tuple[str, str], list[_CandidateRecording]] = {}
    for row in setup.run(candidates_query):
        pair = (str(row[0]), str(row[1]))
        if pair not in wanted_pairs:
            continue
        candidates.setdefault(pair, []).append(
            _CandidateRecording(
                session_id=str(row[2]),
                signals={
                    event: int(row[signal_base + index]) for index, (event, _singular) in enumerate(HIGHLIGHT_SIGNALS)
                },
                repetition=int(row[repetition_base + repetition_index[pair[0]]]),
            )
        )

    all_session_ids = sorted(
        {recording.session_id for pair_recordings in candidates.values() for recording in pair_recordings}
    )
    if not all_session_ids:
        return {}

    # Replay's own existence check rather than a read of the replay summaries here: "a row exists"
    # is not the same question as "this recording can be opened", and only that helper applies the
    # team's retention period and the deleted flag. A card promising a recording that expired or was
    # deleted is the one failure the whole shelf is built to avoid.
    exists_by_id = SessionReplayEvents().batch_exists(all_session_ids, setup.team)
    recorded = {session_id for session_id in all_session_ids if exists_by_id.get(session_id)}

    found = {}
    for pair, pair_recordings in candidates.items():
        kept = [recording for recording in pair_recordings if recording.session_id in recorded][:MAX_CARD_RECORDINGS]
        # A pair without a single playable recording is left out entirely, so any card leaning on
        # it is dropped rather than returned promising zero recordings.
        if not kept:
            continue
        found[pair] = _CardRecordings(
            session_ids=[recording.session_id for recording in kept],
            # Ranked, but not yet cut to the few a card names: which of them this card ends up
            # naming depends on the cards beside it, and which cards those are is settled per
            # viewer.
            highlights=_rank_highlights(
                kept,
                # On a card whose own event is one of the friction signals, the signal count already
                # is the repetition, so counting both would say "2 rage clicks, did this 2 times".
                count_repetition=pair[0] not in FRICTION_EVENTS,
            ),
        )
    return found


def _highlight_alias(event_name: str) -> str:
    """Column alias for one highlight signal's per-session count. Derived from the event name so the
    select list, the outer projection and the row unpacking cannot drift apart."""
    return f"signal_{event_name.lstrip('$')}"


def _repetition_alias(index: int) -> str:
    """Column alias for one wanted event's per-session occurrence count. Derived from the event's
    index in the sorted wanted events so the select list, the outer projection and the row unpacking
    cannot drift apart."""
    return f"repetition_{index}"


def _rank_highlights(
    recordings: list[_CandidateRecording], *, count_repetition: bool
) -> list[ExperimentWatchHighlight]:
    """A card's recordings worth opening first, best first, each with everything it carries.

    Every recording that carries anything, rather than only the few a card shows: which of them
    this card names is decided in `_assign_highlights`, once the shelf beside it is final.

    Ranked on the friction a session shows as a whole rather than by naming the leader of each
    signal in turn. Per-signal leaders describe half of what they point at and hide the sessions
    carrying several kinds of trouble at once: measured on a production-shaped card, the top
    rage-click session also held six errors and the top error session also held four rage clicks,
    while the session sitting just behind on both axes never appeared. That session is the one
    showing a person hitting two problems in a row, which is the case this surface exists to find.

    Kinds before volume for the same reason: a session that rage clicked and then hit an error says
    more about the variant than one that only rage clicked twice as often. Volume then decides the
    ties, but damped, because past a few occurrences a count measures the session's length, and
    ranking on raw totals puts the longest session first on every card it appears on.

    Repeating the card's own event counts as one more kind of signal, from two occurrences up
    since one is what put the session on the card at all, and it outranks friction volume on a tie:
    on a behavior card the recording worth opening first is the one where the difference the card
    claims is most on screen, not the one carrying the most of something every card shows.
    `count_repetition` is False when the card's own event is a friction signal, whose count already
    says the same thing.
    """
    scored: list[tuple[int, int, int, str, str]] = []
    for recording in recordings:
        present = [
            (recording.signals[event], singular)
            for event, singular in HIGHLIGHT_SIGNALS
            if recording.signals[event] > 0
        ]
        # A broken session tops every card it backs: damping caps what its counts are worth, but it
        # still wins ties on carrying every kind of signal, so it is kept off the highlights
        # entirely rather than merely held back. A rage click vouches for the session: loops don't
        # rage click, and a person grinding through this much friction is worth featuring.
        if recording.signals["$rageclick"] == 0 and any(
            count >= MAX_HIGHLIGHT_SIGNAL_COUNT for count, _singular in present
        ):
            continue
        # Every signal the session carries, in the shelf's own priority order rather than by size,
        # so two recordings' reasons stay comparable at a glance.
        phrases = [pluralize(count, singular) for count, singular in present]
        repetition = recording.repetition if count_repetition else 0
        if repetition > 1:
            phrases.append(f"did this {repetition} times")
        if not phrases:
            continue
        kinds = len(present) + (1 if repetition > 1 else 0)
        damped_repetition = min(repetition, HIGHLIGHT_COUNT_DAMPING) if repetition > 1 else 0
        damped_signals = sum(min(count, HIGHLIGHT_COUNT_DAMPING) for count, _singular in present)
        scored.append((kinds, damped_repetition, damped_signals, recording.session_id, ", ".join(phrases)))
    # Ties broken on the session id rather than left to dict order, so the same shelf computed
    # twice names the same recordings.
    scored.sort(key=lambda entry: (-entry[0], -entry[1], -entry[2], entry[3]))
    return [
        ExperimentWatchHighlight(session_id=session_id, reason=reason)
        for _kinds, _repetition, _signals, session_id, reason in scored
    ]