Querying Posthog Data

Querying data in PostHog

The guidelines contain the same instructions as posthog:execute-sql. If you've already read posthog:execute-sql, you don't need to read them again.

When to use this skill

Finding a specific PostHog entity

When the user wants to find a specific entity created in PostHog (insights, dashboards, cohorts, feature flags, experiments, surveys, hog flows, data warehouse items, etc.), or when a list/search tool returns too many results to narrow down:

1. Read the appropriate schema reference under Data Schema to understand the entity's table and columns. 2. Use posthog:execute-sql to query the system table and find the matching entity (typically returning its ID). 3. Use the dedicated read tool for that entity type (e.g. posthog:insight-get, posthog:dashboard-get) to retrieve the full entity by ID.

Don't try to reconstruct the entity from SQL — execute-sql is for discovery, the read tool is for retrieval.

Querying analytics data

When the user wants analytics data (trends, funnels, retention, paths, sessions, LLM traces, web analytics, errors, logs, etc.) and the existing insight schemas don't fit the request:

1. Look for a matching example under Analytics Query Examples. The list is not exhaustive — there may not be an example for every scenario. If one is a close fit (same domain, similar aggregation), read it; otherwise skip this step. 2. Adapt the example query (if one was found) to the user's request and run it via posthog:execute-sql. If no example fit, compose the query from scratch using the Data Schema and HogQL References.

Data Schema

Schema reference for PostHog's core system models, organized by domain:

• Activity logs
• Actions
• Alerts
• Annotations
• APM / tracing (`posthog.trace_spans`)
• Batch exports
• Early Access Features
• Cohorts & Persons
• Dashboards, Tiles & Insights
• Data Warehouse
• Data Modeling Endpoints
• Error Tracking
• Flags & Experiments
• Heatmaps (`heatmaps` data + `system.heatmaps_saved`)
• Hog Flows
• Hog Functions
• Integrations
• AI observability events (`posthog.ai_events`)
• AI observability reviews
• Logs (`logs` data plane + saved views and alerts)
• MCP analytics (`$mcp_tool_call` events)
• Metrics (`posthog.metrics`)
• Notebooks
• Session Recording Playlists
• Session Recordings
• Support Tickets
• Surveys
• Usage Metrics
• SQL Variables
• Skipped events in the read-data-schema tool
• Dynamic person and event properties — patterns like $survey_dismissed/{id}, $feature/{key} that don't appear in tool results

HogQL References

• Person property modes (event-time vs query-time). Read when working with person.properties.* to understand if values are historical or current.
• Sparkline, SemVer, Session replays, Actions, Translation, HTML tags and links, Text effects, and more
• SQL variables.
• Available functions in HogQL. IMPORTANT: the list is long, so read data using bash commands like grep.

Analytics Query Examples

Use the examples below to create optimized analytical queries.

• Trends (unique users, specific time range, single series)
• Trends (total count with multiple breakdowns)
• Funnel (two steps, aggregated by unique users, broken down by the person's role, sequential, 14-day conversion window)
• Conversion trends (funnel, two steps, aggregated by unique groups, 1-day conversion window)
• Retention (unique users, returned to perform an event in the next 12 weeks, recurring)
• User paths (pageviews, three steps, applied path cleaning and filters, maximum 50 paths)
• Lifecycle (unique users by pageviews)
• Stickiness (counted by pageviews from unique users, defined by at least one event for the interval, non-cumulative)
• LLM trace (generations, spans, embeddings, human feedback, captured AI metrics)
• LLM traces list (searching and listing traces with property filters, two-phase query)
• Web path stats (paths, visitors, views, bounce rate)
• Web traffic channels (direct, organic search, etc)
• Web views by devices
• Web overview
• Error tracking (search for a value in an error and filtering by custom properties)
• Logs (filtering by severity and searching for a term)
• Cross-signal correlation (metric exemplar → trace → logs)
• Sessions (listing sessions with duration, pageviews, and bounce rate)
• Session replay (listing recordings with activity filters)
• Team taxonomy (top events by count, paginated)
• Event taxonomy (properties of an event, with sample values)
• Person property taxonomy (sample values for person properties)

Supported functions

abs accurateCast accurateCastOrNull acos acosh addDays addHours addMinutes addMonths addQuarters addSeconds addWeeks addYears age alphaTokens and any anyHeavy anyLast appendTrailingCharIfAbsent argMax argMaxMerge argMaxState argMin argMinMerge argMinState array array_agg arrayAll arrayAUC arrayAvg arrayCompact arrayConcat arrayCount arrayCumSum arrayCumSumNonNegative arrayDifference arrayDistinct arrayElement arrayEnumerate arrayEnumerateDense arrayEnumerateUniq arrayExists arrayFill arrayFilter arrayFirst arrayFirstIndex arrayFlatten arrayFold arrayIntersect arrayJoin arrayLast arrayLastIndex arrayMap arrayMax arrayMin arrayPopBack arrayPopFront arrayProduct arrayPushBack arrayPushFront arrayReduce arrayResize arrayReverse arrayReverseFill arrayReverseSort arrayReverseSplit arrayRotateLeft arrayRotateRight arraySlice arraySort arraySplit arrayStringConcat arraySum arrayUniq arrayWithConstant arrayZip ascii asin asinh assumeNotNull atan atan2 atanh avg avgArgMax avgArgMaxOrDefault avgArgMaxOrNull avgArgMin avgArgMinOrDefault avgArgMinOrNull avgArray avgArrayOrDefault avgArrayOrNull avgForEach avgForEachOrDefault avgForEachOrNull avgMap avgMapMerge avgMapOrDefault avgMapOrNull avgMapState avgMerge avgMergeOrDefault avgMergeOrNull avgOrDefault avgOrNull avgState avgStateOrDefault avgStateOrNull avgWeighted bar base58Decode base58Encode base64Decode base64Encode bitAnd bitCount bitHammingDistance bitmapAnd bitmapAndCardinality bitmapAndnot bitmapAndnotCardinality bitmapBuild bitmapCardinality bitmapContains bitmapHasAll bitmapHasAny bitmapMax bitmapMin bitmapOr bitmapOrCardinality bitmapSubsetInRange bitmapSubsetLimit bitmapToArray bitmapTransform bitmapXor bitmapXorCardinality bitNot bitOr bitRotateLeft bitRotateRight bitShiftLeft bitShiftRight bitSlice bitTest bitTestAll bitTestAny bitXor btrim cbrt ceil cityHash64 coalesce concat concatWithSeparator contingency convertCharset corr cos cosh cosineDistance count countArgMax countArgMaxOrDefault countArgMaxOrNull countArgMin countArgMinOrDefault countArgMinOrNull countArray countArrayOrDefault countArrayOrNull countDistinct countDistinctArgMax countDistinctArgMaxOrDefault countDistinctArgMaxOrNull countDistinctArgMin countDistinctArgMinOrDefault countDistinctArgMinOrNull countDistinctArray countDistinctArrayOrDefault countDistinctArrayOrNull countDistinctForEach countDistinctForEachOrDefault countDistinctForEachOrNull countDistinctMap countDistinctMapOrDefault countDistinctMapOrNull countDistinctMerge countDistinctMergeOrDefault countDistinctMergeOrNull countDistinctOrDefault countDistinctOrNull countDistinctState countDistinctStateOrDefault countDistinctStateOrNull countEqual countForEach countForEachOrDefault countForEachOrNull countMap countMapOrDefault countMapOrNull countMatches countMatchesCaseInsensitive countMerge countMergeOrDefault countMergeOrNull countOrDefault countOrNull countState countStateOrDefault countStateOrNull countSubstrings countSubstringsCaseInsensitive countSubstringsCaseInsensitiveUTF8 covarPop covarSamp cramersV cramersVBiasCorrected current_date current_timestamp cutFragment cutQueryString cutQueryStringAndFragment cutToFirstSignificantSubdomain cutToFirstSignificantSubdomainWithWWW cutURLParameter cutWWW date_add date_bin date_diff date_part date_subtract date_trunc dateAdd dateDiff dateName dateSub dateTrunc decodeURLComponent decodeURLFormComponent decodeXMLComponent degrees deltaSum deltaSumTimestamp dense_rank divide divideDecimal domain domainWithoutWWW dotProduct e empty encodeURLComponent encodeURLFormComponent encodeXMLComponent endsWith equals erf erfc every exp exp10 exp2 extract extractAll extractAllGroups extractAllGroupsHorizontal extractAllGroupsVertical extractGroups extractIPv4Substrings extractTextFromHTML extractURLParameter extractURLParameterNames extractURLParameters factorial first_value firstSignificantSubdomain floor format formatDateTime formatReadableDecimalSize formatReadableQuantity formatReadableSize formatReadableTimeDelta fragment fromModifiedJulianDay fromUnixTimestamp fromUnixTimestamp64Milli gcd generateSeries geoDistance geohashDecode geohashEncode geohashesInBox geoToH3 greatCircleAngle greatCircleDistance greater greaterOrEquals greatest groupArray groupArrayInsertAt groupArrayMovingAvg groupArrayMovingSum groupArraySample groupBitAnd groupBitmap groupBitmapAnd groupBitmapAndState groupBitmapOr groupBitmapOrState groupBitmapState groupBitmapXor groupBitOr groupBitXor groupUniqArray groupUniqArrayArray h3CellAreaM2 h3CellAreaRads2 h3Distance h3EdgeAngle h3EdgeLengthKm h3EdgeLengthM h3ExactEdgeLengthKm h3ExactEdgeLengthM h3ExactEdgeLengthRads h3GetBaseCell h3GetDestinationIndexFromUnidirectionalEdge h3GetFaces h3GetIndexesFromUnidirectionalEdge h3GetOriginIndexFromUnidirectionalEdge h3GetPentagonIndexes h3GetRes0Indexes h3GetResolution h3GetUnidirectionalEdge h3GetUnidirectionalEdgeBoundary h3GetUnidirectionalEdgesFromHexagon h3HexAreaKm2 h3HexAreaM2 h3HexRing h3IndexesAreNeighbors h3IsPentagon h3IsResClassIII h3IsValid h3kRing h3Line h3NumHexagons h3PointDistKm h3PointDistM h3PointDistRads h3ToCenterChild h3ToChildren h3ToGeo h3ToGeoBoundary h3ToParent h3ToString h3UnidirectionalEdgeIsValid has hasAll hasAllTokens hasAny hasAnyTokens hasSubsequence hasSubsequenceCaseInsensitive hasSubsequenceCaseInsensitiveUTF8 hasSubsequenceUTF8 hasSubstr hasToken hasTokenCaseInsensitive hasTokenCaseInsensitiveOrNull hasTokenOrNull hex hop hopEnd hopStart hypot if ifNotFinite ifnull ilike in indexHint indexOf initcap intDiv intDivOrZero intExp10 intExp2 isFinite isInfinite isNaN isNotNull isnull isValidJSON isValidUTF8 json_agg JSON_VALUE JSONArrayLength JSONExtract JSONExtractArrayRaw JSONExtractBool JSONExtractFloat JSONExtractInt JSONExtractKeys JSONExtractKeysAndValues JSONExtractKeysAndValuesRaw JSONExtractRaw JSONExtractString JSONExtractUInt JSONHas JSONLength JSONType kurtPop kurtSamp L1Distance L1Norm L1Normalize L2Distance L2Norm L2Normalize lag lagInFrame languageCodeToName last_value lcm lead leadInFrame least left leftPad leftPadUTF8 length lengthUTF8 less lessOrEquals lgamma like LinfDistance LinfNorm LinfNormalize ln locate log log10 log1p log2 lower lowerUTF8 lpad LpDistance LpNorm LpNormalize ltrim make_date make_interval make_timestamp make_timestamptz map mapAdd mapApply mapContains mapContainsKeyLike mapExtractKeyLike mapFilter mapFromArrays mapKeys mapPopulateSeries mapSubtract mapUpdate mapValues match max max2 maxArgMax maxArgMaxOrDefault maxArgMaxOrNull maxArgMin maxArgMinOrDefault maxArgMinOrNull maxArray maxArrayOrDefault maxArrayOrNull maxForEach maxForEachOrDefault maxForEachOrNull maxIntersections maxIntersectionsPosition maxMap maxMapOrDefault maxMapOrNull maxMerge maxMergeOrDefault maxMergeOrNull maxOrDefault maxOrNull maxState maxStateOrDefault maxStateOrNull md5 median medianArgMax medianArgMaxOrDefault medianArgMaxOrNull medianArgMin medianArgMinOrDefault medianArgMinOrNull medianArray medianArrayOrDefault medianArrayOrNull medianBFloat16 medianDeterministic medianExact medianExactHigh medianExactLow medianExactWeighted medianForEach medianForEachOrDefault medianForEachOrNull medianMap medianMapOrDefault medianMapOrNull medianMerge medianMergeOrDefault medianMergeOrNull medianOrDefault medianOrNull medianState medianStateOrDefault medianStateOrNull medianTDigest medianTDigestWeighted medianTiming medianTimingWeighted min min2 minArgMax minArgMaxOrDefault minArgMaxOrNull minArgMin minArgMinOrDefault minArgMinOrNull minArray minArrayOrDefault minArrayOrNull minForEach minForEachOrDefault minForEachOrNull minMap minMapOrDefault minMapOrNull minMerge minMergeOrDefault minMergeOrNull minOrDefault minOrNull minState minStateOrDefault minStateOrNull minus modulo moduloOrZero monthName multiFuzzyMatchAllIndices multiFuzzyMatchAny multiFuzzyMatchAnyIndex multiIf multiMatchAllIndices multiMatchAny multiMatchAnyIndex multiply multiplyDecimal multiSearchAllPositions multiSearchAllPositionsCaseInsensitive multiSearchAllPositionsCaseInsensitiveUTF8 multiSearchAllPositionsUTF8 multiSearchAny multiSearchAnyCaseInsensitive multiSearchAnyCaseInsensitiveUTF8 multiSearchAnyUTF8 multiSearchFirstIndex multiSearchFirstIndexCaseInsensitive multiSearchFirstIndexCaseInsensitiveUTF8 multiSearchFirstIndexUTF8 multiSearchFirstPosition multiSearchFirstPositionCaseInsensitive multiSearchFirstPositionCaseInsensitiveUTF8 multiSearchFirstPositionUTF8 negate netloc ngramDistance ngramDistanceCaseInsensitive ngramDistanceCaseInsensitiveUTF8 ngramDistanceUTF8 ngrams ngramSearch ngramSearchCaseInsensitive ngramSearchCaseInsensitiveUTF8 ngramSearchUTF8 not notEmpty notEquals notILike notIn notLike now nowInBlock nth_value nullif or parseDateTime parseDateTimeBestEffort path pathFull percentile_cont percentile_disc pi plus pointInEllipses pointInPolygon port position positionCaseInsensitive positionCaseInsensitiveUTF8 positionUTF8 positiveModulo pow power protocol quantile quantileExact quantiles quantilesMerge quantilesState queryString queryStringAndFragment radians rand range rank regexpExtract regexpQuoteMeta reinterpretAsFloat32 reinterpretAsFloat64 reinterpretAsInt128 reinterpretAsInt16 reinterpretAsInt256 reinterpretAsInt32 reinterpretAsInt64 reinterpretAsInt8 reinterpretAsUInt128 reinterpretAsUInt16 reinterpretAsUInt256 reinterpretAsUInt32 reinterpretAsUInt64 reinterpretAsUInt8 reinterpretAsUUID repeat replace replaceAll replaceOne replaceRegexpAll replaceRegexpOne reverse reverseUTF8 right rightPad rightPadUTF8 round roundAge roundBankers roundDown roundDuration roundToExp2 row_number rowNumberInAllBlocks rowNumberInBlock rpad rtrim sign simpleLinearRegression sin sinh skewPop skewSamp split_part splitByChar splitByNonAlpha splitByRegexp splitByString splitByWhitespace sqrt startsWith stddevPop stddevSamp string_agg stringToH3 subBitmap substring substringUTF8 subtractDays subtractHours subtractMinutes subtractMonths subtractQuarters subtractSeconds subtractWeeks subtractYears sum sumArgMax sumArgMaxOrDefault sumArgMaxOrNull sumArgMin sumArgMinOrDefault sumArgMinOrNull sumArray sumArrayOrDefault sumArrayOrNull sumForEach sumForEachMerge sumForEachOrDefault sumForEachOrNull sumForEachState sumMap sumMapMerge sumMapOrDefault sumMapOrNull sumMerge sumMergeOrDefault sumMergeOrNull sumOrDefault sumOrNull sumState sumStateOrDefault sumStateOrNull sumWithOverflow tan tgamma theilsU throwIf timeSlot timeSlots timeStampAdd timeStampSub timezone timeZoneOf timeZoneOffset to_char to_date to_timestamp toBool toDate toDateTime toDateTime64 toDateTimeUS today toDayOfMonth toDayOfWeek toDayOfYear toDecimal toFloat toFloat64OrNull toFloatOrDefault toFloatOrNull toFloatOrZero toHour toInt toIntervalDay toIntervalHour toIntervalMinute toIntervalMonth toIntervalQuarter toIntervalSecond toIntervalWeek toIntervalYear toIntOrZero toISOWeek toISOYear toJSONString tokens toLastDayOfMonth toLastDayOfWeek toMinute toModifiedJulianDay toMonday toMonth toNullable toNullableString topK topLevelDomain toQuarter toSecond toStartOfDay toStartOfFifteenMinutes toStartOfFiveMinutes toStartOfHour toStartOfInterval toStartOfISOYear toStartOfMinute toStartOfMonth toStartOfQuarter toStartOfSecond toStartOfTenMinutes toStartOfWeek toStartOfYear toString toTime toTimeZone toTypeName toUnixTimestamp toUnixTimestamp64Milli toUUID toUUIDOrDefault toValidUTF8 toWeek toYear toYearWeek toYYYYMM toYYYYMMDD toYYYYMMDDhhmmss transform translate translateUTF8 trim trimLeft trimRight trunc tryBase58Decode tryBase64Decode tumble tumbleEnd tumbleStart tuple tupleDivide tupleDivideByNumber tupleElement tupleHammingDistance tupleMinus tupleMultiply tupleMultiplyByNumber tupleNegate tuplePlus tupleToNameValuePairs unhex uniq uniqExact uniqExactMerge uniqExactState uniqHLL12 uniqMap uniqMapMerge uniqMerge uniqState uniqTheta uniqUpToMerge untuple upper upperUTF8 URLHierarchy URLPathHierarchy UUIDv7ToDateTime varPop varSamp width_bucket windowFunnel xor yesterday

Error tracking (search for a value in an error and filtering by custom properties)

SELECT
    fp_state.issue_id AS id,
    any(fp_state.issue_status) AS status,
    any(fp_state.issue_name) AS name,
    any(fp_state.issue_description) AS description,
    any(fp_state.assigned_user_id) AS assignee_user_id,
    any(fp_state.assigned_role_id) AS assignee_role_id,
    min(fp_state.first_seen) AS first_seen,
    max(ev.last_seen_fp) AS last_seen,
    argMaxMerge(ev.function_state) AS function,
    argMaxMerge(ev.source_state) AS source,
    sum(ev.occ) AS occurrences,
    uniqMerge(ev.sessions_state) AS sessions,
    uniqMerge(ev.users_state) AS users,
    sumForEach(arrayMap(i -> if(equals(ev.bin_idx, i), ev.occ, _toUInt64(0)), range(0, 20))) AS volumeRange,
    argMinMerge(ev.first_event_uuid_state) AS first_event_uuid,
    argMaxMerge(ev.library_state) AS library
FROM
    (SELECT
        cityHash64(e.properties.$exception_fingerprint) AS fp_hash,
        max(timestamp) AS last_seen_fp,
        argMaxState(properties.$exception_functions.-1, timestamp) AS function_state,
        argMaxState(properties.$exception_sources.-1, timestamp) AS source_state,
        argMaxState(properties.$lib, timestamp) AS library_state,
        least(19, intDiv(dateDiff('seconds', toDateTime(toDateTime('2026-06-21 07:09:26.002999')), timestamp), greatest(1, intDiv(dateDiff('seconds', toDateTime(toDateTime('2026-06-21 07:09:26.002999')), toDateTime(toDateTime('2026-06-22 07:09:26.004646'))), 20)))) AS bin_idx,
        count() AS occ,
        uniqState(nullIf(e.$session_id, '')) AS sessions_state,
        uniqState(coalesce(nullIf(toString(e.person_id), '00000000-0000-0000-0000-000000000000'), e.distinct_id)) AS users_state,
        argMinState(e.uuid, e.timestamp) AS first_event_uuid_state
    FROM
        events AS e
    WHERE
        and(equals(e.event, '$exception'), isNotNull(e.properties.$exception_fingerprint), true, greaterOrEquals(e.timestamp, toDateTime(toDateTime('2026-06-21 07:09:26.002999'))), lessOrEquals(e.timestamp, toDateTime(toDateTime('2026-06-22 07:09:26.004646'))), or(greater(position(lower(e.properties.$exception_types), lower('constant')), 0), greater(position(lower(e.properties.$exception_values), lower('constant')), 0), greater(position(lower(e.properties.$exception_sources), lower('constant')), 0), greater(position(lower(e.properties.$exception_functions), lower('constant')), 0), greater(position(lower(e.properties.email), lower('constant')), 0), greater(position(lower(e.person.properties.email), lower('constant')), 0)), equals(properties.tag, 'max_ai'))
    GROUP BY
        fp_hash,
        bin_idx) AS ev
    INNER JOIN error_tracking_fingerprint_issue_state AS fp_state ON equals(ev.fp_hash, fp_state.fp_hash)
WHERE
    isNotNull(fp_state.issue_id)
GROUP BY
    id
ORDER BY
    last_seen DESC
LIMIT 50000

Event taxonomy (properties of an event, with sample values)

All properties for a given event, with up to 5 sample values each:

SELECT
    key,
    arraySlice(arrayDistinct(groupArray(value)), 1, 5) AS values,
    count(DISTINCT value) AS total_count
FROM
    (SELECT
        JSONExtractKeysAndValues(properties, 'String') AS kv
    FROM
        events
    WHERE
        and(greaterOrEquals(timestamp, minus(now(), toIntervalDay(30))), equals(event, '$pageview'))
    ORDER BY
        timestamp DESC
    LIMIT 100)
ARRAY JOIN (kv).1 AS key, (kv).2 AS value
WHERE
    not(match(key, '(\\$set|\\$time|\\$set_once|\\$sent_at|distinct_id|\\$ip|\\$feature\\/|\\$feature_enrollment\\/|\\$feature_interaction\\/|\\$product_tour|__|survey_dismiss|survey_responded|phjs|partial_filter_chosen|changed_action|window-id|changed_event|partial_filter)'))
GROUP BY
    key
ORDER BY
    total_count DESC
LIMIT 50000

Specific properties only (faster, skips the omit filter):

SELECT
    key,
    arraySlice(arrayDistinct(groupArray(value)), 1, 5) AS values,
    count(DISTINCT value) AS total_count
FROM
    (SELECT
        key,
        value,
        count() AS count
    FROM
        (SELECT
            [tuple('$browser', JSONExtractString(properties, '$browser')), tuple('$os', JSONExtractString(properties, '$os'))] AS kv
        FROM
            events
        WHERE
            and(greaterOrEquals(timestamp, minus(now(), toIntervalDay(30))), equals(event, '$pageview'), or(notEquals(JSONExtractString(properties, '$browser'), ''), notEquals(JSONExtractString(properties, '$os'), ''))))
    ARRAY JOIN (kv).1 AS key, (kv).2 AS value
    WHERE
        and(notEquals(value, NULL), notEquals(value, ''))
    GROUP BY
        key,
        value
    ORDER BY
        count DESC)
GROUP BY
    key
ORDER BY
    total_count DESC,
    key ASC
LIMIT 50000

Funnel (two steps, aggregated by unique users, $pageview -> user signed up, broken down by the person's role, sequential, 14-day conversion window)

SELECT
    sum(step_1) AS step_1,
    sum(step_2) AS step_2,
    arrayMap(x -> if(isNaN(x), NULL, x), [avgArray(step_1_conversion_times)])[1] AS step_1_average_conversion_time,
    arrayMap(x -> if(isNaN(x), NULL, x), [medianArray(step_1_conversion_times)])[1] AS step_1_median_conversion_time,
    groupArray(row_number) AS row_number,
    final_prop
FROM
    (SELECT
        countIf(notEquals(bitAnd(steps_bitfield, 1), 0)) AS step_1,
        countIf(notEquals(bitAnd(steps_bitfield, 2), 0)) AS step_2,
        groupArrayIf(timings[1], greater(timings[1], 0)) AS step_1_conversion_times,
        rowNumberInAllBlocks() AS row_number,
        if(less(row_number, 25), breakdown, ['Other']) AS final_prop
    FROM
        (SELECT
            arraySort(t -> t.1, groupArray(tuple(toFloat(timestamp), uuid, arrayMap(x -> ifNull(x, ''), prop_basic), arrayFilter(x -> notEquals(x, 0), [multiply(1, step_0), multiply(2, step_1)])))) AS events_array,
            argMinIf(prop_basic, timestamp, notEmpty(arrayFilter(x -> notEmpty(x), prop_basic))) AS prop,
            arrayJoin(aggregate_funnel_array(2, 1209600, 'first_touch', 'ordered', [if(empty(prop), [''], prop)], [], arrayFilter((x, x_before, x_after) -> not(and(lessOrEquals(length(x.4), 1), equals(x.4, x_before.4), equals(x.4, x_after.4), equals(x.3, x_before.3), equals(x.3, x_after.3), greater(x.1, x_before.1), less(x.1, x_after.1))), events_array, arrayRotateRight(events_array, 1), arrayRotateLeft(events_array, 1)))) AS af_tuple,
            af_tuple.1 AS step_reached,
            plus(af_tuple.1, 1) AS steps,
            af_tuple.2 AS breakdown,
            af_tuple.3 AS timings,
            af_tuple.5 AS steps_bitfield,
            aggregation_target
        FROM
            (SELECT
                e.timestamp AS timestamp,
                person_id AS aggregation_target,
                e.uuid AS uuid,
                e.$session_id AS $session_id,
                e.$window_id AS $window_id,
                if(equals(event, '$pageview'), 1, 0) AS step_0,
                if(equals(event, 'user signed up'), 1, 0) AS step_1,
                [ifNull(toString(person.properties.role), '')] AS prop_basic,
                prop_basic AS prop
            FROM
                events AS e
            WHERE
                and(and(and(greaterOrEquals(e.timestamp, toDateTime('2025-12-03 00:00:00.000000')), lessOrEquals(e.timestamp, toDateTime('2025-12-10 23:59:59.999999'))), in(event, tuple('$pageview', 'user signed up'))), or(equals(step_0, 1), equals(step_1, 1))))
        GROUP BY
            aggregation_target
        HAVING
            greaterOrEquals(step_reached, 0))
    GROUP BY
        breakdown
    ORDER BY
        step_2 DESC,
        step_1 DESC)
GROUP BY
    final_prop
ORDER BY
    step_2 DESC,
    step_1 DESC
LIMIT 26

Conversion trends (funnel, two steps, $pageview -> user signed up, aggregated by unique groups, 1-day conversion window)

SELECT
    fill.entrance_period_start AS entrance_period_start,
    countIf(notEquals(success_bool, 0)) AS reached_from_step_count,
    countIf(equals(success_bool, 1)) AS reached_to_step_count,
    if(greater(reached_from_step_count, 0), round(multiply(divide(reached_to_step_count, reached_from_step_count), 100), 2), 0) AS conversion_rate,
    breakdown AS prop
FROM
    (SELECT
        arraySort(t -> t.1, groupArray(tuple(toFloat(timestamp), _toUInt64(toDateTime(toStartOfDay(timestamp))), uuid, '', arrayFilter(x -> notEquals(x, 0), [multiply(1, step_0), multiply(2, step_1)])))) AS events_array,
        [''] AS prop,
        arrayJoin(aggregate_funnel_trends(1, 2, 2, 86400, 'first_touch', 'strict', prop, events_array)) AS af_tuple,
        toTimeZone(toDateTime(_toUInt64(af_tuple.1)), 'UTC') AS entrance_period_start,
        af_tuple.2 AS success_bool,
        af_tuple.3 AS breakdown,
        aggregation_target AS aggregation_target
    FROM
        (SELECT
            e.timestamp AS timestamp,
            $group_0 AS aggregation_target,
            e.uuid AS uuid,
            if(equals(event, '$pageview'), 1, 0) AS step_0,
            if(equals(event, 'user signed up'), 1, 0) AS step_1
        FROM
            events AS e
        WHERE
            and(and(greaterOrEquals(e.timestamp, toDateTime('2025-12-03 00:00:00.000000')), lessOrEquals(e.timestamp, toDateTime('2025-12-10 23:59:59.999999'))), and(notEquals(toString(aggregation_target), ''), notEquals(aggregation_target, NULL))))
    GROUP BY
        aggregation_target) AS data
    RIGHT OUTER JOIN (SELECT
        plus(toStartOfDay(assumeNotNull(toDateTime(('2025-12-03 00:00:00')))), toIntervalDay(number)) AS entrance_period_start
    FROM
        numbers(plus(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime(('2025-12-03 00:00:00')))), toStartOfDay(assumeNotNull(toDateTime(('2025-12-10 23:59:59'))))), 1)) AS period_offsets) AS fill ON equals(data.entrance_period_start, fill.entrance_period_start)
GROUP BY
    entrance_period_start,
    data.breakdown
ORDER BY
    entrance_period_start ASC
LIMIT 1000

Lifecycle (unique users by pageviews)

SELECT
    groupArray(start_of_period) AS date,
    groupArray(counts) AS total,
    status
FROM
    (SELECT
        if(equals(status, 'dormant'), negate(sum(counts)), negate(negate(sum(counts)))) AS counts,
        start_of_period,
        status
    FROM
        (SELECT
            periods.start_of_period AS start_of_period,
            0 AS counts,
            status
        FROM
            (SELECT
                minus(toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)), toIntervalDay(number)) AS start_of_period
            FROM
                numbers(dateDiff('day', toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1)), toStartOfInterval(plus(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)), toIntervalDay(1)))) AS numbers) AS periods
            CROSS JOIN (SELECT
                status
            FROM
                (SELECT
                    1)
            ARRAY JOIN ['new', 'returning', 'resurrecting', 'dormant'] AS status) AS sec
        ORDER BY
            status ASC,
            start_of_period ASC
        UNION ALL
        SELECT
            start_of_period,
            count(DISTINCT actor_id) AS counts,
            status
        FROM
            (SELECT
                min(events.person.created_at) AS created_at,
                arraySort(groupUniqArray(toStartOfInterval(events.timestamp, toIntervalDay(1)))) AS all_activity,
                arrayPopBack(arrayPushFront(all_activity, toStartOfInterval(created_at, toIntervalDay(1)))) AS previous_activity,
                arrayPopFront(arrayPushBack(all_activity, toStartOfInterval(toDateTime('1970-01-01 00:00:00'), toIntervalDay(1)))) AS following_activity,
                arrayMap((previous, current, index) -> if(equals(previous, current), 'new', if(and(equals(minus(toTimeZone(current, 'UTC'), toIntervalDay(1)), previous), notEquals(index, 1)), 'returning', 'resurrecting')), previous_activity, all_activity, arrayEnumerate(all_activity)) AS initial_status,
                arrayMap((current, next) -> if(equals(plus(toTimeZone(current, 'UTC'), toIntervalDay(1)), toTimeZone(next, 'UTC')), '', 'dormant'), all_activity, following_activity) AS dormant_status,
                arrayMap(x -> plus(toTimeZone(x, 'UTC'), toIntervalDay(1)), arrayFilter((current, is_dormant) -> equals(is_dormant, 'dormant'), all_activity, dormant_status)) AS dormant_periods,
                arrayMap(x -> 'dormant', dormant_periods) AS dormant_label,
                arrayConcat(arrayZip(all_activity, initial_status), arrayZip(dormant_periods, dormant_label)) AS temp_concat,
                arrayJoin(temp_concat) AS period_status_pairs,
                period_status_pairs.1 AS start_of_period,
                period_status_pairs.2 AS status,
                person_id AS actor_id
            FROM
                events
            WHERE
                and(notEquals(properties.$process_person_profile, 'false'), greaterOrEquals(events.timestamp, minus(toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1)), toIntervalDay(1))), less(events.timestamp, plus(toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)), toIntervalDay(1))), equals(event, '$pageview'))
            GROUP BY
                actor_id)
        GROUP BY
            start_of_period,
            status)
    WHERE
        and(lessOrEquals(start_of_period, toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1))), greaterOrEquals(start_of_period, toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1))))
    GROUP BY
        start_of_period,
        status
    ORDER BY
        start_of_period ASC)
GROUP BY
    status
LIMIT 50000

LLM Trace query

This query might return a very large blob of JSON data. You should either only include data you need in case it's minimal or dump the results to a file and use bash commands to explore it. This query must always have time ranges set. You can calculate the time range as -30 to +30 minutes from the source event. The typical order of event capture for a trace is: $ai_span -> $ai_generation/$ai_embedding -> $ai_trace. Explore $ai\_\*-prefixed properties to find data related to traces, generations, embeddings, spans, feedback, and metric. Key properties of the $ai_generation event: $ai_input and $ai_output_choices.

IMPORTANT: The $ai_input, $ai_input_state, and $ai_output_state properties can be extremely large (containing full conversation histories, system prompts, or application state). When your query selects these properties, you MUST dump the results to a file and use bash commands to explore the output. Never output them directly into the conversation.

These heavy fields live only on posthog.ai_events (read it directly by trace_id), not on events.properties — see AI observability events for the column mapping and query patterns.

SELECT
    trace_id AS id,
    any(session_id) AS ai_session_id,
    min(timestamp) AS first_timestamp,
    max(timestamp) AS last_timestamp,
    ifNull(nullIf(argMinIf(distinct_id, timestamp, equals(event, '$ai_trace')), ''), argMin(distinct_id, timestamp)) AS first_distinct_id,
    round(if(and(equals(countIf(and(greater(latency, 0), notEquals(event, '$ai_generation'))), 0), greater(countIf(and(greater(latency, 0), equals(event, '$ai_generation'))), 0)), sumIf(latency, and(equals(event, '$ai_generation'), greater(latency, 0))), sumIf(latency, or(equals(parent_id, NULL), equals(parent_id, trace_id)))), 2) AS total_latency,
    nullIf(sumIf(input_tokens, in(event, tuple('$ai_generation', '$ai_embedding'))), 0) AS input_tokens,
    nullIf(sumIf(output_tokens, in(event, tuple('$ai_generation', '$ai_embedding'))), 0) AS output_tokens,
    nullIf(round(sumIf(input_cost_usd, in(event, tuple('$ai_generation', '$ai_embedding'))), 10), 0) AS input_cost,
    nullIf(round(sumIf(output_cost_usd, in(event, tuple('$ai_generation', '$ai_embedding'))), 10), 0) AS output_cost,
    nullIf(round(sumIf(total_cost_usd, in(event, tuple('$ai_generation', '$ai_embedding'))), 10), 0) AS total_cost,
    arrayDistinct(arraySort(x -> x.3, groupArrayIf(tuple(uuid, event, timestamp, properties, input, output, output_choices, input_state, output_state, tools), notEquals(event, '$ai_trace')))) AS events,
    argMinIf(input_state, timestamp, equals(event, '$ai_trace')) AS input_state,
    argMinIf(output_state, timestamp, equals(event, '$ai_trace')) AS output_state,
    ifNull(argMinIf(ifNull(nullIf(span_name, ''), nullIf(trace_name, '')), timestamp, equals(event, '$ai_trace')), argMin(ifNull(nullIf(span_name, ''), nullIf(trace_name, '')), timestamp)) AS trace_name
FROM
    ai_events
WHERE
    and(in(event, tuple('$ai_span', '$ai_generation', '$ai_embedding', '$ai_metric', '$ai_feedback', '$ai_trace')), and(greaterOrEquals(ai_events.timestamp, assumeNotNull(toDateTime('2025-12-09 23:35:41'))), lessOrEquals(ai_events.timestamp, assumeNotNull(toDateTime('2025-12-10 00:25:41'))), equals(trace_id, '79955c94-7453-488f-a84a-eabb6f084e4c')))
GROUP BY
    trace_id
LIMIT 1

LLM Traces list query

List multiple LLM traces with aggregated latency, token usage, costs, and error counts. This is a two-phase query for performance: first find matching trace IDs, then fetch full trace data. Time ranges are always required. Results can be large — dump to a file if needed.

This query intentionally omits large content fields ($ai_input, $ai_output_choices, $ai_input_state, $ai_output_state). Use the single trace query to retrieve those for a specific trace. This content lives only on posthog.ai_events (not events), retained 30 days by default — read it anchored on trace_id. See AI observability events for the column mapping and access patterns.

Phase 1 — Find trace IDs

Use this subquery to find trace IDs matching your criteria. Add property filters here for efficiency.

SELECT
    properties.$ai_trace_id AS trace_id,
    min(timestamp) AS first_ts,
    max(timestamp) AS last_ts
FROM events
WHERE
    event IN ('$ai_span', '$ai_generation', '$ai_embedding', '$ai_metric', '$ai_feedback', '$ai_trace')
    AND isNotNull(properties.$ai_trace_id)
    AND properties.$ai_trace_id != ''
    AND timestamp >= now() - INTERVAL 1 HOUR
    AND timestamp <= now()
    -- Add property filters here, e.g.:
    -- AND properties.$ai_model = 'gpt-4o'
    -- AND properties.$ai_is_error = 'true'
GROUP BY trace_id
ORDER BY min(timestamp) DESC
LIMIT 20

Phase 2 — Fetch trace data

Use the trace IDs from phase 1 to fetch aggregated metrics. Replace the IN (...) clause with the IDs found above.

SELECT
    properties.$ai_trace_id AS id,
    any(properties.$ai_session_id) AS ai_session_id,
    min(timestamp) AS first_timestamp,
    ifNull(
        nullIf(argMinIf(distinct_id, timestamp, event = '$ai_trace'), ''),
        argMin(distinct_id, timestamp)
    ) AS first_distinct_id,
    round(
        CASE
            WHEN countIf(toFloat(properties.$ai_latency) > 0 AND event != '$ai_generation') = 0
                 AND countIf(toFloat(properties.$ai_latency) > 0 AND event = '$ai_generation') > 0
            THEN sumIf(toFloat(properties.$ai_latency),
                       event = '$ai_generation' AND toFloat(properties.$ai_latency) > 0)
            ELSE sumIf(toFloat(properties.$ai_latency),
                       properties.$ai_parent_id IS NULL
                       OR toString(properties.$ai_parent_id) = toString(properties.$ai_trace_id))
        END, 2
    ) AS total_latency,
    sumIf(toFloat(properties.$ai_input_tokens),
          event IN ('$ai_generation', '$ai_embedding')) AS input_tokens,
    sumIf(toFloat(properties.$ai_output_tokens),
          event IN ('$ai_generation', '$ai_embedding')) AS output_tokens,
    round(sumIf(toFloat(properties.$ai_input_cost_usd),
          event IN ('$ai_generation', '$ai_embedding')), 10) AS input_cost,
    round(sumIf(toFloat(properties.$ai_output_cost_usd),
          event IN ('$ai_generation', '$ai_embedding')), 10) AS output_cost,
    round(sumIf(toFloat(properties.$ai_total_cost_usd),
          event IN ('$ai_generation', '$ai_embedding')), 10) AS total_cost,
    ifNull(
        argMinIf(
            ifNull(properties.$ai_span_name, properties.$ai_trace_name),
            timestamp, event = '$ai_trace'
        ),
        argMin(
            ifNull(properties.$ai_span_name, properties.$ai_trace_name),
            timestamp
        )
    ) AS trace_name,
    countIf(
        isNotNull(properties.$ai_error) OR properties.$ai_is_error = 'true'
    ) AS error_count
FROM events
WHERE
    event IN ('$ai_span', '$ai_generation', '$ai_embedding', '$ai_metric', '$ai_feedback', '$ai_trace')
    AND timestamp >= now() - INTERVAL 1 HOUR
    AND timestamp <= now()
    AND properties.$ai_trace_id IN ('trace-id-1', 'trace-id-2')
GROUP BY properties.$ai_trace_id
ORDER BY first_timestamp DESC

Logs (filtering by severity and searching for a term)

SELECT
    uuid,
    hex(tryBase64Decode(trace_id)),
    hex(tryBase64Decode(span_id)),
    body,
    attributes,
    timestamp,
    observed_timestamp,
    severity_text,
    severity_number,
    severity_text AS level,
    resource_attributes,
    resource_fingerprint,
    instrumentation_scope,
    event_name,
    (SELECT
            min(partition_checkpoint)
        FROM
            (SELECT
                _topic,
                _partition,
                max(max_observed_timestamp) AS partition_checkpoint
            FROM
                logs_kafka_metrics
            GROUP BY
                _topic,
                _partition)) AS live_logs_checkpoint
FROM
    logs
WHERE
    and(and(greaterOrEquals(toStartOfDay(time_bucket), toStartOfDay(assumeNotNull(toDateTime('2025-12-09 00:00:00')))), lessOrEquals(toStartOfDay(time_bucket), toStartOfDay(assumeNotNull(toDateTime('2025-12-10 00:00:00'))))), 1, greaterOrEquals(timestamp, toDateTime('2026-06-21 07:09:28.635157')), indexHint(like(lower(body), '%timeout%')), ilike(toString(body), '%timeout%'), in(severity_text, tuple('warn', 'error', 'fatal')))
ORDER BY
    timestamp DESC,
    uuid DESC
LIMIT 101
OFFSET 0

Cross-signal correlation (metric exemplar → trace → logs)

Use when investigating a metric anomaly (latency spike, error rate jump) and you want to inspect a representative trace and the logs from that request in one round trip.

The three observability tables — posthog.metrics, posthog.trace_spans, logs — share trace_id as a join key. The schema is wired so posthog.metrics can carry an exemplar trace_id on every point when the SDK attached one (OpenTelemetry exemplar pattern).

Namespacing: logs is registered at the HogQL root level; posthog.trace_spans and posthog.metrics live under the posthog. namespace and must be referenced with the prefix. Bare names fail.

`trace_id` format: All three tables store trace_id as base64-encoded 16 bytes. Joins are direct equality (no decoding needed). Use hex(tryBase64Decode(trace_id)) to display in hex.

⚠️ Status (as of PR [#50936](https://github.com/PostHog/posthog/pull/50936)): exemplar extraction is not yet wired up in rust/capture-logs/src/metric_record.rs — the _exemplars argument is prefixed with underscore (unused). Every metric row has trace_id = '' today. The example query below describes the intended pattern but returns empty until exemplars are populated. The "Works today" alternative further down uses posthog.trace_spans directly as the starting point and works against current data.

Pattern

1. Locate the spike in metrics for a specific (service, metric, time window). 2. Pick an exemplar — argMax(trace_id, value) returns the trace_id from the row with the highest value. 3. Fetch spans and logs for that trace_id in a single UNION ALL, ordered by timestamp so the timeline interleaves.

Query

WITH exemplar AS (
    SELECT argMax(trace_id, value) AS trace_id
    FROM posthog.metrics
    WHERE service_name = 'checkout'
      AND metric_name = 'http.server.duration'
      AND timestamp >= now() - INTERVAL 15 MINUTE
      AND trace_id != ''
)
SELECT
    'span' AS source,
    name AS detail,
    service_name,
    duration_nano,
    status_code,
    NULL AS severity_number,
    timestamp
FROM posthog.trace_spans
WHERE trace_id = (SELECT trace_id FROM exemplar)

UNION ALL

SELECT
    'log',
    body,
    service_name,
    NULL,
    NULL,
    severity_number,
    timestamp
FROM logs
WHERE trace_id = (SELECT trace_id FROM exemplar)

ORDER BY timestamp

Notes

• `argMax(trace_id, value)` is cheap because the per-minute projection on posthog.metrics pre-aggregates by (service_name, metric_name, ...). Constrain the time window tightly (15 minutes is plenty for a spike).
• Filter `trace_id != ''` — metric points without an exemplar use empty string, not null.
• `UNION ALL` (not `UNION`) — UNION deduplicates and adds cost.
• `status_code = 2` is Error in posthog.trace_spans (OTel semantics). Use this column to flag error spans inline in the result.
• If you need to drill into the span tree visually, take the resulting trace_id and call posthog:apm-trace-get to get the full waterfall.

Works today: span-anchored correlation

Until metric exemplars are populated by ingestion, anchor on a span instead. Find an interesting trace (slowest error, longest duration, specific service), then pull its logs.

WITH slow_error_trace AS (
    SELECT trace_id
    FROM posthog.trace_spans
    WHERE service_name = 'checkout'
      AND is_root_span
      AND status_code = 2
      AND timestamp >= now() - INTERVAL 1 HOUR
    ORDER BY duration_nano DESC
    LIMIT 1
)
SELECT
    'span' AS source,
    name AS detail,
    service_name,
    duration_nano,
    status_code,
    NULL AS severity_number,
    timestamp
FROM posthog.trace_spans
WHERE trace_id = (SELECT trace_id FROM slow_error_trace)

UNION ALL

SELECT
    'log',
    body,
    service_name,
    NULL,
    NULL,
    severity_number,
    timestamp
FROM logs
WHERE trace_id = (SELECT trace_id FROM slow_error_trace)

ORDER BY timestamp

trace_id is base64 in both tables, so the equality join works directly.

Variants

Pick a sample of exemplar traces, not just one:

SELECT trace_id, max(value) AS peak
FROM posthog.metrics
WHERE service_name = 'checkout'
  AND metric_name = 'http.server.duration'
  AND timestamp >= now() - INTERVAL 15 MINUTE
  AND trace_id != ''
GROUP BY trace_id
ORDER BY peak DESC
LIMIT 5

Find services with the biggest error-rate jump and pick an exemplar trace per service:

SELECT
    service_name,
    countIf(status_code = 2) / count() AS error_rate,
    argMax(trace_id, status_code = 2) AS sample_error_trace
FROM posthog.trace_spans
WHERE timestamp >= now() - INTERVAL 1 HOUR
  AND is_root_span
GROUP BY service_name
HAVING count() > 100
ORDER BY error_rate DESC
LIMIT 10

sample_error_trace is then a candidate for posthog:apm-trace-get or a logs lookup by trace_id.

User paths (pageviews, three steps, applied path cleaning and filters, maximum 50 paths)

SELECT
    last_path_key AS source_event,
    path_key AS target_event,
    COUNT(*) AS event_count,
    avg(conversion_time) AS average_conversion_time
FROM
    (SELECT
        person_id,
        path,
        conversion_time,
        event_in_session_index,
        concat(toString(event_in_session_index), '_', path) AS path_key,
        if(greater(event_in_session_index, 1), concat(toString(minus(event_in_session_index, 1)), '_', prev_path), NULL) AS last_path_key,
        path_dropoff_key
    FROM
        (SELECT
            person_id,
            joined_path_tuple.1 AS path,
            joined_path_tuple.2 AS conversion_time,
            joined_path_tuple.3 AS prev_path,
            event_in_session_index,
            session_index,
            arrayPopFront(arrayPushBack(path_basic, '')) AS path_basic_0,
            arrayMap((x, y) -> if(equals(x, y), 0, 1), path_basic, path_basic_0) AS mapping,
            arrayFilter((x, y) -> y, time, mapping) AS timings,
            arrayFilter((x, y) -> y, path_basic, mapping) AS compact_path,
            indexOf(compact_path, NULL) AS target_index,
            if(greater(target_index, 0), arraySlice(compact_path, target_index), compact_path) AS filtered_path,
            arraySlice(filtered_path, 1, 3) AS limited_path,
            if(greater(target_index, 0), arraySlice(timings, target_index), timings) AS filtered_timings,
            arraySlice(filtered_timings, 1, 3) AS limited_timings,
            arrayDifference(limited_timings) AS timings_diff,
            concat(toString(length(limited_path)), '_', limited_path[-1]) AS path_dropoff_key,
            arrayZip(limited_path, timings_diff, arrayPopBack(arrayPushFront(limited_path, ''))) AS limited_path_timings
        FROM
            (SELECT
                person_id,
                path_time_tuple.1 AS path_basic,
                path_time_tuple.2 AS time,
                session_index,
                arrayZip(path_list, timing_list, arrayDifference(timing_list)) AS paths_tuple,
                arraySplit(x -> if(less(x.3, 1800), 0, 1), paths_tuple) AS session_paths
            FROM
                (SELECT
                    person_id,
                    groupArray(timestamp) AS timing_list,
                    groupArray(path_item) AS path_list
                FROM
                    (SELECT
                        events.timestamp,
                        events.person_id,
                        ifNull(if(equals(event, '$pageview'), replaceRegexpAll(ifNull(properties.$current_url, ''), '(.)/$', '\\1'), event), '') AS path_item_ungrouped,
                        replaceRegexpAll(path_item_ungrouped, '^https:\\/\\/[a-z-]+\\.posthog\\.com', 'https://<region>.posthog.com') AS path_item_0,
                        replaceRegexpAll(path_item_0, '\\/project\\/\\d+', '/project/<team_id>') AS path_item_1,
                        replaceRegexpAll(path_item_1, '\\/ai-observability\\/traces\\/[0-9a-f\\-]+', '/ai-observability/traces/<trace_id>') AS path_item_2,
                        replaceRegexpAll(path_item_2, '\\/ai-observability\\/sessions\\/[0-9a-f\\-]+', '/ai-observability/sessions/<session_id>') AS path_item_3,
                        replaceRegexpAll(path_item_3, '\\/ai-evals\\/evaluations\\/[0-9a-f\\-]+', '/ai-evals/evaluations/<evaluation_id>') AS path_item_4,
                        replaceRegexpAll(path_item_4, '\\/ai-evals\\/datasets\\/[0-9a-f\\-]+', '/ai-evals/datasets/<dataset_id>') AS path_item_cleaned,
                        NULL AS groupings,
                        multiMatchAnyIndex(path_item_cleaned, NULL) AS group_index,
                        (if(greater(group_index, 0), groupings[group_index], path_item_cleaned) AS path_item) AS path_item
                    FROM
                        events
                    WHERE
                        and(and(ilike(toString(properties.$pathname), '%ai-%'), notILike(toString(properties.$pathname), '%docs%')), and(greaterOrEquals(events.timestamp, toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1))), lessOrEquals(events.timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), equals(event, '$pageview'))
                    ORDER BY
                        events.person_id ASC,
                        events.timestamp ASC)
                GROUP BY
                    person_id)
            ARRAY JOIN session_paths AS path_time_tuple, arrayEnumerate(session_paths) AS session_index)
        ARRAY JOIN limited_path_timings AS joined_path_tuple, arrayEnumerate(limited_path_timings) AS event_in_session_index))
WHERE
    notEquals(source_event, NULL)
GROUP BY
    source_event,
    target_event
ORDER BY
    event_count DESC,
    source_event ASC,
    target_event ASC
LIMIT 50

Person property taxonomy (sample values for person properties)

Sample values for specific person properties:

SELECT
    groupArray(5)(prop),
    count(),
    prop_index
FROM
    (SELECT
        DISTINCT prop_index,
        toString(prop_value) AS prop
    FROM
        persons
    ARRAY JOIN arrayEnumerate([toString(properties.email), toString(properties.$initial_browser)]) AS prop_index, [toString(properties.email), toString(properties.$initial_browser)] AS prop_value
    WHERE
        isNotNull(prop_value)
    ORDER BY
        created_at DESC)
GROUP BY
    prop_index
ORDER BY
    prop_index ASC
LIMIT 50000

Retention (unique users, $ai_trace -> $ai_trace in the next 12 weeks, recurring)

SELECT
    actor_activity.start_interval_index AS start_event_matching_interval,
    actor_activity.intervals_from_base AS intervals_from_base,
    COUNT(DISTINCT actor_activity.actor_id) AS count
FROM
    (SELECT
        events.person_id AS actor_id,
        arraySort(groupUniqArrayIf(toStartOfWeek(events.timestamp, 0), and(and(equals(events.event, '$ai_trace'), in(properties.$ai_span_name, tuple('LangGraph', 'LangGraphUpdateState'))), and(greaterOrEquals(events.timestamp, toStartOfWeek(assumeNotNull(toDateTime('2025-09-07 00:00:00')))), less(events.timestamp, toDateTime('2025-12-14 00:00:00.000000')))))) AS start_event_timestamps,
        arrayMap(x -> plus(toStartOfWeek(assumeNotNull(toDateTime('2025-09-07 00:00:00'))), toIntervalWeek(x)), range(0, 14)) AS date_range,
        arraySort(groupUniqArrayIf(toStartOfWeek(events.timestamp, 0), and(and(equals(events.event, '$ai_trace'), in(properties.$ai_span_name, tuple('LangGraph', 'LangGraphUpdateState'))), and(greaterOrEquals(events.timestamp, toStartOfWeek(assumeNotNull(toDateTime('2025-09-07 00:00:00')))), less(events.timestamp, toDateTime('2025-12-14 00:00:00.000000')))))) AS return_event_timestamps,
        arrayJoin(arrayFilter(x -> greater(x, -1), arrayMap((interval_index, interval_date, _start_event_timestamps) -> if(has(_start_event_timestamps, interval_date), minus(interval_index, 1), -1), arrayEnumerate(date_range), date_range, arrayResize([start_event_timestamps], length(date_range), start_event_timestamps)))) AS start_interval_index,
        arrayJoin(arrayConcat(if(has(start_event_timestamps, date_range[plus(start_interval_index, 1)]), [0], []), arrayFilter(x -> greater(x, 0), arrayMap(_timestamp -> minus(indexOf(arraySlice(date_range, plus(start_interval_index, 1), 12), _timestamp), 1), return_event_timestamps)))) AS intervals_from_base
    FROM
        events
    WHERE
        and(and(greaterOrEquals(events.timestamp, toStartOfWeek(assumeNotNull(toDateTime('2025-09-07 00:00:00')))), less(events.timestamp, toDateTime('2025-12-14 00:00:00.000000'))), in(event, tuple('$ai_trace')), or(and(equals(events.event, '$ai_trace'), in(properties.$ai_span_name, tuple('LangGraph', 'LangGraphUpdateState'))), and(equals(events.event, '$ai_trace'), in(properties.$ai_span_name, tuple('LangGraph', 'LangGraphUpdateState')))))
    GROUP BY
        actor_id
    HAVING
        and(1, 1)) AS actor_activity
GROUP BY
    start_event_matching_interval,
    intervals_from_base
ORDER BY
    start_event_matching_interval ASC,
    intervals_from_base ASC
LIMIT 50000

Session replay (listing recordings with activity filters)

SELECT
    s.session_id,
    any(s.team_id),
    any(s.distinct_id),
    min(s.min_first_timestamp) AS start_time,
    max(s.max_last_timestamp) AS end_time,
    dateDiff('SECOND', start_time, end_time) AS duration,
    argMinMerge(s.first_url) AS first_url,
    sum(s.click_count) AS click_count,
    sum(s.keypress_count) AS keypress_count,
    sum(s.mouse_activity_count) AS mouse_activity_count,
    divide(sum(s.active_milliseconds), 1000) AS active_seconds,
    minus(duration, active_seconds) AS inactive_seconds,
    sum(s.console_log_count) AS console_log_count,
    sum(s.console_warn_count) AS console_warn_count,
    sum(s.console_error_count) AS console_error_count,
    max(s.retention_period_days) AS retention_period_days,
    plus(dateTrunc('DAY', start_time), toIntervalDay(coalesce(retention_period_days, 30))) AS expiry_time,
    date_diff('DAY', toDateTime('2026-06-22 07:09:29.610005'), expiry_time) AS recording_ttl,
    greaterOrEquals(max(s._timestamp), toDateTime('2026-06-22 07:04:29.609141')) AS ongoing,
    round(multiply(divide(plus(plus(plus(divide(sum(s.active_milliseconds), 1000), sum(s.click_count)), sum(s.keypress_count)), sum(s.console_error_count)), plus(plus(plus(plus(sum(s.mouse_activity_count), dateDiff('SECOND', start_time, end_time)), sum(s.console_error_count)), sum(s.console_log_count)), sum(s.console_warn_count))), 100), 2) AS activity_score,
    coalesce(max(s.surfacing_score), 0.36) AS surfacing_score
FROM
    raw_session_replay_events AS s
WHERE
    and(greaterOrEquals(s.min_first_timestamp, toDateTime('2026-06-19 00:00:00.000000')), lessOrEquals(s.min_first_timestamp, toDateTime('2026-06-22 07:09:29.609325')))
GROUP BY
    session_id
HAVING
    and(greaterOrEquals(expiry_time, toDateTime('2026-06-22 07:09:29.609896')), equals(max(s.is_deleted), 0), greater(active_seconds, 5.0))
ORDER BY
    start_time DESC,
    session_id DESC
LIMIT 50000

Sessions (listing sessions with duration, pageviews, and bounce rate)

SELECT
    session_id,
    $start_timestamp,
    $end_timestamp,
    $session_duration,
    $pageview_count,
    $is_bounce,
    $entry_current_url,
    $end_current_url
FROM
    sessions
WHERE
    and(less($start_timestamp, toDateTime('2026-06-22 07:09:36.043071')), greater($start_timestamp, toDateTime('2026-06-21 07:09:31.043785')))
ORDER BY
    $start_timestamp DESC
LIMIT 50000

Stickiness (counted by pageviews from unique users, defined by at least one event for the interval, non-cumulative)

SELECT
    groupArray(num_actors) AS counts,
    groupArray(num_intervals) AS intervals
FROM
    (SELECT
        sum(num_actors) AS num_actors,
        num_intervals
    FROM
        (SELECT
            0 AS num_actors,
            plus(number, 1) AS num_intervals
        FROM
            numbers(ceil(divide(dateDiff('day', toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1)), plus(toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)), toIntervalDay(1))), 1))) AS numbers
        UNION ALL
        SELECT
            count(DISTINCT aggregation_target) AS num_actors,
            num_intervals
        FROM
            (SELECT
                aggregation_target,
                count() AS num_intervals
            FROM
                (SELECT
                    e.person_id AS aggregation_target,
                    toStartOfInterval(e.timestamp, toIntervalDay(1)) AS start_of_interval
                FROM
                    events AS e
                WHERE
                    and(greaterOrEquals(timestamp, toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59'))), equals(event, '$pageview'))
                GROUP BY
                    aggregation_target,
                    start_of_interval
                HAVING
                    greater(count(), 0))
            GROUP BY
                aggregation_target)
        GROUP BY
            num_intervals
        ORDER BY
            num_intervals ASC)
    GROUP BY
        num_intervals
    ORDER BY
        num_intervals ASC)
LIMIT 50000

Team taxonomy (top events by count, paginated)

SELECT
    event,
    count() AS count
FROM
    events
WHERE
    and(greaterOrEquals(timestamp, minus(now(), toIntervalDay(30))), notIn(event, ['$pageleave', '$autocapture', '$$heatmap', '$copy_autocapture', '$set', '$opt_in', '$feature_flag_called', '$feature_view', '$feature_interaction', '$element_viewed', '$capture_metrics', '$create_alias', '$merge_dangerously', '$groupidentify', '$snapshot']))
GROUP BY
    event
ORDER BY
    count DESC,
    event ASC
LIMIT 50000

Trends (total event count, specific week)

SELECT
    groupArray(1)(date)[1] AS date,
    arrayFold((acc, x) -> arrayMap(i -> plus(acc[i], x[i]), range(1, plus(length(date), 1))), groupArray(ifNull(total, 0)), arrayWithConstant(length(date), reinterpretAsFloat64(0))) AS total,
    arrayMap(i -> if(ifNull(greaterOrEquals(row_number, 25), 0), '$$_posthog_breakdown_other_$$', i), breakdown_value) AS breakdown_value
FROM
    (SELECT
        arrayMap(number -> plus(toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1)), toIntervalDay(number)), range(0, plus(coalesce(dateDiff('day', toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1)), toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)))), 1))) AS date,
        arrayMap(_match_date -> arraySum(arraySlice(groupArray(ifNull(count, 0)), indexOf(groupArray(day_start) AS _days_for_count, _match_date) AS _index, plus(minus(arrayLastIndex(x -> equals(x, _match_date), _days_for_count), _index), 1))), date) AS total,
        breakdown_value AS breakdown_value,
        rowNumberInAllBlocks() AS row_number
    FROM
        (WITH
            min_max AS (SELECT
                    count() AS total,
                    toStartOfDay(timestamp) AS day_start,
                    ifNull(nullIf(left(toString(properties.$browser), 400), ''), '$$_posthog_breakdown_null_$$') AS breakdown_value_1,
                    toFloat(properties.$browser_version) AS breakdown_value_2
                FROM
                    events AS e
                WHERE
                    and(greaterOrEquals(timestamp, toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59'))), equals(event, '$pageview'))
                GROUP BY
                    day_start,
                    breakdown_value_1,
                    breakdown_value_2)
        SELECT
            sum(total) AS count,
            day_start,
            [breakdown_value_1, if(empty(arrayFilter(x -> and(lessOrEquals(x[1], breakdown_value_2), less(breakdown_value_2, x[2])), buckets[1])[1]), '$$_posthog_breakdown_null_$$', ifNull(nullIf(left(toString(arrayFilter(x -> and(lessOrEquals(x[1], breakdown_value_2), less(breakdown_value_2, x[2])), buckets[1])[1]), 400), ''), '$$_posthog_breakdown_null_$$'))] AS breakdown_value
        FROM
            (SELECT
                count() AS total,
                toStartOfDay(timestamp) AS day_start,
                ifNull(nullIf(left(toString(properties.$browser), 400), ''), '$$_posthog_breakdown_null_$$') AS breakdown_value_1,
                toFloat(properties.$browser_version) AS breakdown_value_2,
                (SELECT
                        [max(breakdown_value_2)]
                    FROM
                        min_max) AS max_nums,
                (SELECT
                        [min(breakdown_value_2)]
                    FROM
                        min_max) AS min_nums,
                arrayMap((max_num, min_num, bin_count) -> arrayMap(x -> [plus(multiply(divide(minus(max_num, min_num), bin_count), x), min_num), plus(plus(multiply(divide(minus(max_num, min_num), bin_count), plus(x, 1)), min_num), if(equals(plus(x, 1), bin_count), 0.01, 0))], range(bin_count)), max_nums, min_nums, [10]) AS buckets
            FROM
                events AS e
            WHERE
                and(greaterOrEquals(timestamp, toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59'))), equals(event, '$pageview'))
            GROUP BY
                day_start,
                breakdown_value_1,
                breakdown_value_2)
        GROUP BY
            day_start,
            breakdown_value
        ORDER BY
            day_start ASC,
            breakdown_value ASC)
    GROUP BY
        breakdown_value
    ORDER BY
        if(has(breakdown_value, '$$_posthog_breakdown_other_$$'), 2, if(has(breakdown_value, '$$_posthog_breakdown_null_$$'), 1, 0)) ASC,
        arraySum(total) DESC,
        breakdown_value ASC)
WHERE
    arrayExists(x -> isNotNull(x), breakdown_value)
GROUP BY
    breakdown_value
ORDER BY
    if(has(breakdown_value, '$$_posthog_breakdown_other_$$'), 2, if(has(breakdown_value, '$$_posthog_breakdown_null_$$'), 1, 0)) ASC,
    arraySum(total) DESC,
    breakdown_value ASC
LIMIT 50000

Trends (unique users, for specific 90 days)

SELECT
    arrayMap(number -> plus(toStartOfInterval(assumeNotNull(toDateTime('2025-11-10 00:00:00')), toIntervalDay(1)), toIntervalDay(number)), range(0, plus(coalesce(dateDiff('day', toStartOfInterval(assumeNotNull(toDateTime('2025-11-10 00:00:00')), toIntervalDay(1)), toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)))), 1))) AS date,
    arrayMap(_match_date -> arraySum(arraySlice(groupArray(ifNull(count, 0)), indexOf(groupArray(day_start) AS _days_for_count, _match_date) AS _index, plus(minus(arrayLastIndex(x -> equals(x, _match_date), _days_for_count), _index), 1))), date) AS total
FROM
    (SELECT
        sum(total) AS count,
        day_start
    FROM
        (SELECT
            count(DISTINCT e.person_id) AS total,
            toStartOfDay(timestamp) AS day_start
        FROM
            events AS e
        WHERE
            and(greaterOrEquals(timestamp, toStartOfInterval(assumeNotNull(toDateTime('2025-11-10 00:00:00')), toIntervalDay(1))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59'))), equals(event, 'chat with ai'))
        GROUP BY
            day_start)
    GROUP BY
        day_start
    ORDER BY
        day_start ASC)
ORDER BY
    arraySum(total) DESC
LIMIT 50000

Web overview (visitors, page views, sessions, session duration, bounce rate)

SELECT
    uniq(session_person_id) AS unique_users,
    NULL AS previous_unique_users,
    sum(filtered_pageview_count) AS total_filtered_pageview_count,
    NULL AS previous_total_filtered_pageview_count,
    uniq(session_id) AS unique_sessions,
    NULL AS previous_unique_sessions,
    avg(session_duration) AS avg_duration_s,
    NULL AS previous_avg_duration_s,
    avg(is_bounce) AS bounce_rate,
    NULL AS previous_bounce_rate
FROM
    (SELECT
        any(events.person_id) AS session_person_id,
        session.session_id AS session_id,
        min(session.$start_timestamp) AS start_timestamp,
        any(session.$session_duration) AS session_duration,
        countIf(or(equals(event, '$pageview'), equals(event, '$screen'))) AS filtered_pageview_count,
        any(session.$is_bounce) AS is_bounce
    FROM
        events
    WHERE
        and(notEquals(events.$session_id, NULL), or(equals(event, '$pageview'), equals(event, '$screen')), or(and(greaterOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), false), 1)
    GROUP BY
        session_id
    HAVING
        or(and(greaterOrEquals(start_timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(start_timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), false))
LIMIT 50000

Web path stats

In this view you can validate all of the paths that were accessed in your application, regardless of when they were accessed through the lifetime of a user session.

The bounce rate indicates the percentage of users who left your page immediately after visiting without capturing any event.

SELECT
    counts.breakdown_value AS `context.columns.breakdown_value`,
    tuple(counts.visitors, counts.previous_visitors) AS `context.columns.visitors`,
    tuple(counts.views, counts.previous_views) AS `context.columns.views`,
    tuple(bounce.bounce_rate, bounce.previous_bounce_rate) AS `context.columns.bounce_rate`,
    divide(`context.columns.visitors`.1, sum(`context.columns.visitors`.1) OVER ()) AS `context.columns.ui_fill_fraction`
FROM
    (SELECT
        breakdown_value,
        uniqIf(filtered_person_id, and(greaterOrEquals(start_timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(start_timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59'))))) AS visitors,
        uniqIf(filtered_person_id, false) AS previous_visitors,
        sumIf(filtered_pageview_count, and(greaterOrEquals(start_timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(start_timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59'))))) AS views,
        sumIf(filtered_pageview_count, false) AS previous_views
    FROM
        (SELECT
            any(person_id) AS filtered_person_id,
            count() AS filtered_pageview_count,
            events.properties.$pathname AS breakdown_value,
            session.session_id AS session_id,
            min(session.$start_timestamp) AS start_timestamp
        FROM
            events
        WHERE
            and(or(equals(events.event, '$pageview'), equals(events.event, '$screen')), or(and(greaterOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), false), 1, 1)
        GROUP BY
            session_id,
            breakdown_value)
    GROUP BY
        breakdown_value) AS counts
    LEFT JOIN (SELECT
        breakdown_value,
        avgIf(is_bounce, and(greaterOrEquals(start_timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(start_timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59'))))) AS bounce_rate,
        avgIf(is_bounce, false) AS previous_bounce_rate
    FROM
        (SELECT
            session.$entry_pathname AS breakdown_value,
            any(session.$is_bounce) AS is_bounce,
            session.session_id AS session_id,
            min(session.$start_timestamp) AS start_timestamp
        FROM
            events
        WHERE
            and(or(equals(events.event, '$pageview'), equals(events.event, '$screen')), or(and(greaterOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), false), 1, 1)
        GROUP BY
            session_id,
            breakdown_value)
    GROUP BY
        breakdown_value) AS bounce ON equals(counts.breakdown_value, bounce.breakdown_value)
WHERE
    notEquals(counts.breakdown_value, NULL)
ORDER BY
    `context.columns.visitors` DESC,
    `context.columns.views` DESC,
    `context.columns.breakdown_value` ASC
LIMIT 50000

Web traffic views by device type

SELECT
    breakdown_value AS `context.columns.breakdown_value`,
    tuple(uniq(filtered_person_id), NULL) AS `context.columns.visitors`,
    tuple(sum(filtered_pageview_count), NULL) AS `context.columns.views`,
    divide(`context.columns.visitors`.1, sum(`context.columns.visitors`.1) OVER ()) AS `context.columns.ui_fill_fraction`
FROM
    (SELECT
        any(person_id) AS filtered_person_id,
        count() AS filtered_pageview_count,
        properties.$device_type AS breakdown_value,
        session.session_id AS session_id,
        any(session.$is_bounce) AS is_bounce,
        min(session.$start_timestamp) AS start_timestamp
    FROM
        events
    WHERE
        and(or(and(greaterOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), false), or(equals(event, '$pageview'), equals(event, '$screen')), 1)
    GROUP BY
        session_id,
        breakdown_value)
GROUP BY
    `context.columns.breakdown_value`
ORDER BY
    `context.columns.visitors` DESC,
    `context.columns.views` DESC,
    `context.columns.breakdown_value` ASC
LIMIT 50000

Web traffic channels (direct, organic search, etc)

Channels are the different sources that bring traffic to your website, e.g. Paid Search, Organic Social, Direct, etc.

SELECT
    breakdown_value AS `context.columns.breakdown_value`,
    tuple(uniq(filtered_person_id), NULL) AS `context.columns.visitors`,
    tuple(sum(filtered_pageview_count), NULL) AS `context.columns.views`,
    divide(`context.columns.visitors`.1, sum(`context.columns.visitors`.1) OVER ()) AS `context.columns.ui_fill_fraction`
FROM
    (SELECT
        any(person_id) AS filtered_person_id,
        count() AS filtered_pageview_count,
        session.$channel_type AS breakdown_value,
        session.session_id AS session_id,
        any(session.$is_bounce) AS is_bounce,
        min(session.$start_timestamp) AS start_timestamp
    FROM
        events
    WHERE
        and(or(and(greaterOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), false), or(equals(event, '$pageview'), equals(event, '$screen')), 1)
    GROUP BY
        session_id,
        breakdown_value)
GROUP BY
    `context.columns.breakdown_value`
HAVING
    and(notEquals(`context.columns.breakdown_value`, NULL), notEquals(`context.columns.breakdown_value`, ''))
ORDER BY
    `context.columns.visitors` DESC,
    `context.columns.views` DESC,
    `context.columns.breakdown_value` ASC
LIMIT 50000

Querying data in PostHog

Use the posthog:execute-sql MCP tool to execute HogQL queries. HogQL is PostHog's variant of SQL that supports most of ClickHouse SQL. We use terms "HogQL" and "SQL" interchangeably. References mentioned in this file are relevant to PostHog's skill querying-posthog-data.

Do not assume that data exists. Use the SQL tool proactively to find the right data.

Search types

Proactively use different search types depending on a task:

• Grep-like (regex search) with match(), LIKE, ILIKE, position, multiMatch, etc.
• Full-text search with hasToken, hasTokenCaseInsensitive, etc. Make sure you pass string constants to hasToken* functions.
• Dumping results to a file and using bash commands to process potentially large outputs.

Data Groups

PostHog has two distinct groups of data you can query:

1. System Data (PostHog-Created Data)

Data created directly in PostHog by users - metadata about PostHog setup.

All system tables are prefixed with system.:

Table | Description system.actions | Named event combinations for filtering system.cohorts | Groups of persons for segmentation system.dashboards | Collections of insights system.data_warehouse_sources | Connected external data sources system.data_warehouse_tables | Connected tables with their columns and formats system.error_tracking_issues | Error tracking issues (grouped exceptions) system.experiments | A/B tests and experiments system.exports | Export jobs system.feature_flags | Feature flags for controlling rollouts system.groups | Group entities system.ingestion_warnings | Data ingestion issues system.insight_variables | SQL, dashboard, and insight variables for dynamic query filtering system.insights | Visual and textual representations of aggregated data system.logs_alerts | Log alert configurations and their states system.logs_views | Saved log filter views system.notebooks | Collaborative documents with embedded insights system.surveys | Questionnaires and feedback forms system.teams | Team/project settings

Example - List insights:

SELECT id, name, short_id FROM system.insights WHERE NOT deleted LIMIT 10

Example - Count insight variables:

SELECT count() AS total FROM system.insight_variables

System Models Reference

Schema reference for PostHog's core system models, organized by domain:

• Actions
• Cohorts & Persons
• Dashboards, Tiles & Insights
• Data Warehouse
• Error Tracking
• Logs
• Flags & Experiments
• Notebooks
• Surveys
• SQL Variables

Entity Relationships

From | Relation | To | Join Experiment | 1:1 | FeatureFlag | feature_flag_id Experiment | N:1 | Cohort | exposure_cohort_id Survey | N:1 | FeatureFlag | linked_flag_id, targeting_flag_id Survey | N:1 | Insight | linked_insight_id Cohort | M:N | Person | via cohortpeople Person | 1:N | PersonDistinctId | person_id

All entities are scoped by a team by default. You cannot access data of another team unless you switch a team.

2. Captured Data (Analytics Data)

Data collected via the PostHog SDK - used for analytics.

Table | Description events | Recorded events from SDKs persons | Individuals captured by the SDK. "Person" = "user" groups | Groups of individuals (organizations, companies, etc.) sessions | Session data captured by the SDK Data warehouse tables | Connected external data sources and custom views

Use posthog:read-data-warehouse-schema to retrieve the full schema of the tables above.

Key concepts:

• Events: Standardized events/properties start with $ (e.g., $pageview). Custom ones start with any other character.
• Properties: Key-value metadata accessed via properties.foo.bar or properties.foo['bar'] for special characters
• Person properties: Access via events.person.properties.foo or persons.properties.foo
• Person property modes: person.properties.* behavior depends on the project's person-on-events setting. Check the project metadata to determine if values are event-time (value at ingestion) or query-time (current value). See Person property modes for details.
• Unique users: Use events.person_id for counting unique users

Example - Weekly active users:

SELECT toStartOfWeek(timestamp) AS week, count(DISTINCT person_id) AS users
FROM events
WHERE event = '$pageview'
  AND timestamp > now() - INTERVAL 8 WEEK
GROUP BY week
ORDER BY week DESC

3. Document Embeddings (Semantic Search)

The document_embeddings table stores text content with vector embeddings, partitioned by model_name. To discover what kinds of data are available:

SELECT product, document_type, count() as cnt
FROM document_embeddings
WHERE model_name = 'text-embedding-3-small-1536'
  AND timestamp >= now() - INTERVAL 1 MONTH
GROUP BY product, document_type
ORDER BY cnt DESC

Run separately for each model. Available models: 'text-embedding-3-small-1536', 'text-embedding-3-large-3072'. You MUST filter on exactly one model_name per query — it routes to the correct underlying ClickHouse table. IN clauses and cross-model queries will fail.

Use embedText(text, model_name) and cosineDistance() for semantic search. See the signals skill for detailed query patterns around the signals product specifically, including required deduplication and metadata extraction.

Querying guidelines

Schema verification

Before writing analytical queries, always verify that:

• The required event names or actions exist.
• Properties and property values of events, persons, sessions, and groups data exist.

Follow this workflow:

1. Fetch the tool schema - Use posthog:read-data-schema to get the latest schema from the MCP. 1. Verify data exist - Use posthog:read-data-schema with different data types to check if the data you need is captured 1. Only then write the query - Once you've confirmed the data exists, write and execute your analytical query

<example> User: how many times the tool search was used? Assistant: 1. First, verify the events exist:

• Call posthog:read-data-schema with kind: events
• Look for events/actions matching the request

2. If required events don't exist, inform the user immediately instead of running queries that will return empty results 3. If events exist, like "tool executed", verify the properties:

• Call posthog:read-data-schema with kind: event_properties and event_name: tool executed
• Look for properties indicating a tool

4. Check other events/actions or return if required properties don't exist 5. If events exist, like "tool_name", verify the property values:

• Call posthog:read-data-schema with kind: event_property_values, event_name: tool executed, and property_name: tool_name
• Follow the pattern from the sample or dig deeper into existing properties with SQL queries.

6. Only then write and execute the analytical SQL query

<reasoning> Assistant should verify the data schema to write a correct SQL query, as the data schema varies over time. </reasoning> </example>

<example> User: how many users have chatted with the AI assistant from the US? Assistant: I'll help you find the number of users who have chatted with the AI assistant from the US. Let me create a todo list to track this implementation. 1. Find the relevant events to "chatted with the AI assistant" 2. Find the relevant properties of the events and persons to narrow down data to users from specific country 3. Retrieve the sample property values for found properties 4. Create the insight schema by using the data retrieved in the previous steps 5. Generate the insight 6. Analyze retrieved data <reasoning> The task list helps the assistant to stay on track. </reasoning> </example>

This prevents wasted API calls and gives users immediate feedback when the data they're looking for doesn't exist.

Progressive exploration

For unfamiliar or potentially large datasets, probe cheaply before running the expensive aggregation. Widen only if the cheap step looks reasonable:

1. Count first — SELECT count() FROM events WHERE timestamp >= now() - INTERVAL 1 DAY AND event = 'foo'. Confirms the data exists and gives a sense of volume. 2. Small sample — inspect a handful of rows (LIMIT 10) to verify property shapes and values match expectations. 3. Full query — run the real aggregation with a time range and LIMIT, having confirmed it won't scan needlessly or return empty.

This is faster than discovering an empty result or a mis-shaped property after the full aggregation, and it costs less.

Skipping index

You should use the skipping index signature to write optimized analytical queries.

Time ranges

All analytical queries and subqueries must always have time ranges set for supported tables (events). If the user doesn't state it, assume default time range based on the data volume, like a day, week, or month.

How you should use time ranges

<example> User: Find events from returning browsers - browsers that appeared both yesterday and today Assistant:

SELECT event FROM events WHERE timestamp >= now() - INTERVAL 1 DAY and properties['$browser'] IN (SELECT properties['$browser'] FROM events WHERE timestamp >= now() - INTERVAL 2 DAY and timestamp < now() - INTERVAL 1 DAY)

</example>

How you should NOT write queries

<example> User: List 10 events with SQL Assistant:

SELECT event, timestamp, distinct_id, properties FROM events ORDER BY timestamp DESC LIMIT 10

</example>

JOINs

General guidelines

Keep in mind that the right expression is loaded in memory when joining data in ClickHouse, so the joining query or table must always fit in memory. Common strategies:

• Analytical functions and combinators.
• Subqueries as a source or filter.
• Arrays (arrayMap, arrayJoin) and ARRAY JOIN.

A subquery used as a join/correlation source must pre-filter and, where possible, pre-aggregate — push the time range, WHERE, and any GROUP BY inside it so the right side stays small in memory. Wrapping a full table in a subquery without narrowing it gains nothing. When you only need a single match per row (enrichment lookups, e.g. attaching one attribute from system data), use LEFT ANY JOIN — it stops at the first match, using less memory and running faster than a regular join.

System data

You are allowed joining system data. Insights are the most used entity, so keep it on the left.

Analytical data

Prefer using analytical functions and subqueries for joins. Do not use raw joins on the events table.

How you should join data

<example> User: Find ai traces with feedback Assistant:

SELECT
 g.properties.$ai_trace_id as trace_id
FROM events AS g
WHERE
  timestamp >= now() - INTERVAL 1 WEEK
  AND g.event = '$ai_generation'
  AND trace_id IN (SELECT properties.$ai_trace_id FROM events WHERE event = '$ai_feedback' AND timestamp >= now() - INTERVAL 1 WEEK)

<reasoning>A subquery is used instead of a JOIN clause. Both queries have the timestamp filters.</reasoning> </example>

How you should NOT join data

<example> User: Find ai traces with feedback Assistant:

SELECT
 g.properties.$ai_trace_id
FROM events AS g
INNER JOIN (SELECT properties.$ai_trace_id as trace_id FROM events WHERE event = '$ai_feedback') AS f
ON g.properties.$ai_trace_id = f.trace_id
WHERE g.event = '$ai_generation'

<reasoning>Join is not necessary here. The assistant could've used a subquery.</reasoning> </example>

Other constraints

• Your query results are capped at 100 rows by default. You can request up to 500 rows using a LIMIT clause. If you need more data, paginate using LIMIT and OFFSET in subsequent queries.
• You should cherry-pick properties of events, persons, or groups, so we don't get OOMs. Never select the full `properties` object (e.g., SELECT properties FROM events) and dump it into the conversation output. Instead, select only the specific properties you need (e.g., properties.$browser, properties.$os). If you must inspect the full properties object, dump the query results to a file and use bash commands to explore it.
• When query results contain large JSON blobs (e.g., AI trace inputs/outputs, full property objects), always dump them to a file rather than outputting them directly. Use bash commands to process the file.

HogQL Differences from Standard SQL

Property access

-- Simple keys
properties.foo.bar

-- Keys with special characters
properties.foo['bar-baz']

Unsupported/changed functions

Don't use | Use instead toFloat64OrNull(), toFloat64() | toFloat() toDateOrNull(timestamp) | toDate(timestamp) LAG(), LEAD() | lagInFrame(), leadInFrame() with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING count(*) | count() cardinality(bitmap) | bitmapCardinality(bitmap) split() | splitByChar(), splitByString()

JOIN constraints

Relational operators (>, <, >=, <=) are forbidden in JOIN clauses. Use CROSS JOIN with WHERE:

-- Wrong
JOIN persons p ON e.person_id = p.id AND e.timestamp > p.created_at

-- Correct
CROSS JOIN persons p WHERE e.person_id = p.id AND e.timestamp > p.created_at

Syntax extensions and HogQL functions

Find the reference for Sparkline, SemVer, Session replays, Actions, Translation, HTML tags and links, Text effects, and more.

Other rules

• WHERE clause must come after all JOINs
• No semicolons at end of queries
• toStartOfWeek(timestamp, 1) for Monday start (numeric, not string)
• Always handle nulls before array functions: splitByChar(',', coalesce(field, ''))
• Performance: always filter events by timestamp

SQL Variables

Review the reference for SQL variables and dashboard filters.

Available HogQL functions

Verify what functions are available using the reference list with suitable bash commands.

Examples

Weekly active users with activation event:

SELECT week_of, countIf(weekly_event_count >= 3)
FROM (
   SELECT person.id AS person_id, toStartOfWeek(timestamp) AS week_of, count() AS weekly_event_count
   FROM events
   WHERE event = 'activation_event'
     AND properties.$current_url = 'https://example.com/foo/'
     AND toStartOfWeek(now()) - INTERVAL 8 WEEK <= timestamp
     AND timestamp < toStartOfWeek(now())
   GROUP BY person.id, week_of
)
GROUP BY week_of
ORDER BY week_of DESC

Find cohorts by name:

SELECT id, name, count FROM system.cohorts WHERE name ILIKE '%paying%' AND NOT deleted

List feature flags:

SELECT key, name, rollout_percentage
FROM system.feature_flags
WHERE NOT deleted
ORDER BY created_at DESC
LIMIT 20

HogQL syntax extensions

These functions are unique to HogQL and not available in standard ClickHouse.

Visualization

sparkline(array)

Creates a tiny inline graph from an array of integers. Useful for visualizing trends in table cells.

-- Basic sparkline
SELECT sparkline(range(1, 10)) FROM (SELECT 1)

-- 24-hour pageview sparkline per URL
SELECT
    pageview,
    sparkline(arrayMap(h -> countEqual(groupArray(hour), h), range(0,23))),
    count() as pageview_count
FROM (
    SELECT
        properties.$current_url as pageview,
        toHour(timestamp) AS hour
    FROM events
    WHERE timestamp > now() - interval 1 day AND event = '$pageview'
) subquery
GROUP BY pageview
ORDER BY pageview_count desc

Version handling

sortableSemVer(version_string)

Converts a SemVer version number into a sortable format for ordering purposes.

SELECT DISTINCT properties.$lib_version
FROM events
WHERE event = '$pageview' AND timestamp >= now() - INTERVAL 1 DAY
ORDER BY sortableSemVer(properties.$lib_version) DESC
LIMIT 10

Session replays

recordingButton(session_id)

Creates a clickable button to view the session replay for a given session ID.

SELECT
    person.properties.email,
    min_first_timestamp AS start,
    recordingButton(session_id)
FROM raw_session_replay_events
WHERE min_first_timestamp >= now() - INTERVAL 1 DAY
    AND min_first_timestamp <= now()
ORDER BY min_first_timestamp DESC
LIMIT 10

Actions

matchesAction(action_name)

Filters events that match a named action. Actions are named event combinations defined in PostHog.

SELECT count()
FROM events
WHERE matchesAction('clicked homepage button')

Localization

languageCodeToName(code)

Translates a language code (e.g., 'en', 'fr') to its full language name.

SELECT
    languageCodeToName('en') AS english,  -- English
    languageCodeToName('fr') AS french,   -- French
    languageCodeToName('pt') AS portuguese, -- Portuguese
    languageCodeToName('ru') AS russian,  -- Russian
    languageCodeToName('zh') AS chinese   -- Chinese

HTML rendering

HogQL supports limited HTML tags for rich output in table visualizations. For security, no attributes are supported except for <a> tags.

Supported tags

• Structure: <div>, <p>, <span>, <pre>, <code>
• Text formatting: <em>, <strong>, <b>, <i>, <u>
• Headings: <h1>, <h2>, <h3>, <h4>, <h5>, <h6>
• Lists: <ul>, <ol>, <li>
• Tables: <table>, <thead>, <tbody>, <tr>, <th>, <td>
• Other: <blockquote>, <hr>

Links with <a>

Create clickable links. URLs in Table visualization are automatically clickable, but use <a> for custom link text.

SELECT
    properties.$pathname,
    <a href={f'https://posthog.com/{properties.$pathname}'} target='_blank'>Link</a> as link
FROM events
WHERE event = '$pageview'

Embeddings

embedText(text, model_name)

Converts a text string into an embedding vector at query compile time. Both arguments must be string literals — you cannot pass column references.

SELECT cosineDistance(
    embedding,
    embedText('users seeing checkout errors', 'text-embedding-3-small-1536')
) as distance
FROM document_embeddings
WHERE
    model_name = 'text-embedding-3-small-1536'
    AND timestamp >= now() - INTERVAL 30 DAY
ORDER BY distance ASC
LIMIT 10

Available models: 'text-embedding-3-small-1536', 'text-embedding-3-large-3072'.

Text effects

Special tags for visual effects in table output.

<blink>

Makes text blink.

SELECT <span>is this <blink>{event}</blink> real?</span> FROM events

<marquee>

Makes text scroll horizontally.

SELECT <marquee>scrolling text!</marquee> FROM events

<redacted>

Hides text until hovered over.

SELECT <redacted>hidden until hover</redacted> FROM events

Combined example

SELECT
    <span>is this <blink>{event}</blink> real?</span>,
    <marquee>so real, yes!</marquee>,
    <redacted>but this one is hidden</redacted>
FROM events

Funnel functions

The three variants differ only in how the breakdown property column is typed.

aggregate_funnel / aggregate_funnel_array / aggregate_funnel_cohort

7 arguments:

1. num_steps (Int) — total number of funnel steps 2. conversion_window_limit (Int) — max seconds between first and last step 3. breakdown_attribution_type (String) — one of first_touch, last_touch, all_events, or step_N 4. funnel_order_type (String) — ordered, unordered, or strict 5. prop_vals (Array) — breakdown property values to aggregate over 6. optional_steps (Array(Int)) — 1-indexed step numbers marked as optional 7. events_array (Array(Tuple)) — pre-sorted array of (timestamp, uuid, breakdown_prop, steps) tuples per person

Returns an array of tuples: (step_reached, breakdown_value, timings, event_uuids, steps_bitmask).

aggregate_funnel_trends / aggregate_funnel_array_trends / aggregate_funnel_cohort_trends

8 arguments:

1. from_step (Int) — 1-indexed start step for conversion measurement 2. to_step (Int) — 1-indexed goal step for conversion measurement 3. num_steps (Int) — total number of funnel steps 4. conversion_window_limit (Int) — max seconds between first and last step 5. breakdown_attribution_type (String) — one of first_touch, last_touch, all_events, or step_N 6. funnel_order_type (String) — ordered, unordered, or strict 7. prop_vals (Array) — breakdown property values to aggregate over 8. events_array (Array(Tuple)) — pre-sorted array of (timestamp, interval_start, uuid, breakdown_prop, steps) tuples per person

Returns an array of tuples: (interval_start, success_bool, breakdown_value, event_uuid).

Actions

Action (system.actions)

Actions are named combinations of events and conditions used for filtering and analysis.

Columns

Column | Type | Nullable | Description id | integer | NOT NULL | Primary key (auto-generated) name | varchar(400) | NULL | Action name description | text | NOT NULL | Action description deleted | boolean | NOT NULL | Soft delete flag created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NOT NULL | Last update timestamp steps_json | jsonb | NULL | Action step definitions post_to_slack | boolean | NOT NULL | Whether to post matches to Slack slack_message_format | varchar(1200) | NOT NULL | Slack message template is_calculating | boolean | NOT NULL | Whether calculation is in progress last_calculated_at | timestamp with tz | NOT NULL | Last calculation timestamp bytecode | jsonb | NULL | Compiled action bytecode bytecode_error | text | NULL | Compilation error message pinned_at | timestamp with tz | NULL | When action was pinned summary | text | NULL | AI-generated summary created_by_id | integer | NULL | Creator user ID

Steps JSON Structure

[
  {
    "id": "uuid",
    "event": "$pageview",
    "url": "https://example.com/pricing",
    "url_matching": "contains",
    "properties": [{ "key": "$current_url", "value": "pricing", "operator": "icontains" }]
  },
  {
    "id": "uuid",
    "event": "button_clicked",
    "selector": "button.cta-primary",
    "text": "Sign Up",
    "text_matching": "exact"
  }
]

Step Matching Options

Field | Description event | Event name to match url | URL pattern to match url_matching | exact, contains, regex selector | CSS selector for element text | Element text to match text_matching | exact, contains, regex properties | Additional property filters

Key Relationships

• Surveys: Actions can be linked to surveys via system.surveys

Important Notes

• Actions can combine multiple event conditions (steps)
• Steps are OR'd together - matching any step triggers the action
• bytecode is compiled from steps for efficient evaluation
• Actions can be used in insights, cohorts, and feature flag targeting

---

Common Query Patterns

Find actions by name:

SELECT id, name, description, steps_json
FROM system.actions
WHERE name ILIKE '%signup%' AND NOT deleted

Find actions with specific event:

SELECT id, name, steps_json
FROM system.actions
WHERE NOT deleted
  AND JSONExtractString(steps_json, 1, 'event') = '$pageview'

Find events matching a specific action:

By action's name:

SELECT count()
FROM events
WHERE matchesAction('clicked homepage button')

By action's ID:

SELECT count()
FROM events
WHERE matchesAction(43)

Activity logs

Activity log (system.activity_logs)

Activity logs track user and system actions across PostHog entities, providing an audit trail of changes to feature flags, insights, dashboards, experiments, and more.

Note: Only team-scoped activity logs are visible. Organisation-level logs (e.g. membership changes) are not included because they are not associated with a specific team.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key (auto-generated UUID) team_id | integer | NULL | Team the activity belongs to activity | varchar(79) | NOT NULL | Action performed (e.g., created, updated, deleted) item_id | varchar(72) | NULL | ID of the entity being logged (may be numeric ID, short ID, or UUID) scope | varchar(79) | NOT NULL | Entity type being logged (e.g., FeatureFlag, Insight) detail | jsonb | NULL | Structured details about the change created_at | timestamp with tz | NOT NULL | When the activity occurred

Detail JSON Structure

{
  "name": "My Feature Flag",
  "short_id": "abc123",
  "type": "FeatureFlag",
  "changes": [
    {
      "type": "FeatureFlag",
      "action": "changed",
      "field": "active",
      "before": false,
      "after": true
    }
  ]
}

Detail Fields

Field | Description name | Display name of the entity short_id | Short identifier (if applicable) type | Entity type changes | Array of individual field changes changes[].type | Entity type for this change changes[].action | changed, created, deleted, merged, split, exported, revoked, copied changes[].field | Name of the field that changed changes[].before | Previous value changes[].after | New value

Common Scopes

FeatureFlag, Insight, Dashboard, Experiment, Cohort, Survey, Notebook, Action, HogFunction, Person, Replay, Comment, BatchExport, Team, Project, ErrorTrackingIssue, EarlyAccessFeature, Annotation, AlertConfiguration

Common Activities

created, updated, deleted, exported, logged_in, logged_out

---

Common Query Patterns

Find recent activity for a scope:

SELECT id, activity, item_id, detail, created_at
FROM system.activity_logs
WHERE scope = 'FeatureFlag'
ORDER BY created_at DESC
LIMIT 50

Find all changes to a specific entity:

SELECT activity, detail, created_at
FROM system.activity_logs
WHERE scope = 'Insight' AND item_id = '42'
ORDER BY created_at DESC

Search for a specific change in detail JSON:

SELECT id, scope, activity, detail, created_at
FROM system.activity_logs
WHERE JSONExtractString(detail, 'name') ILIKE '%signup%'
ORDER BY created_at DESC
LIMIT 20

AI observability events (posthog.ai_events)

LLM/AI events ($ai_generation, $ai_span, $ai_trace, $ai_embedding, $ai_metric, $ai_feedback, $ai_evaluation) are captured on the shared events table. The heavy LLM properties are not stored on `events` — they live as native columns on a dedicated ClickHouse table, posthog.ai_events.

Namespacing: Reference this table as posthog.ai_events, not bare ai_events — it's registered under the posthog. namespace in the HogQL database (see posthog/hogql/database/database.py), same as posthog.trace_spans / posthog.metrics. A bare FROM ai_events fails with "Unknown table" at HogQL compile time. (Asymmetric with events and logs, which are registered at root level.)

Prefer the typed tools when they fit: posthog:query-llm-trace for a single trace and posthog:query-llm-traces-list for listing both join posthog.ai_events for you. Reach for HogQL when you need custom aggregations, joins, or pre-filtering the typed tools don't expose.

Which columns live where

events keeps the lightweight metadata — token counts, costs, model, provider, $ai_trace_id, latency, error flags (also mirrored as native columns on posthog.ai_events, where the $ai_-prefixed property maps to the un-prefixed column, e.g. $ai_model → model). The heavy properties live only on posthog.ai_events:

Nothing restricts which heavy columns an event can carry, but the typical shape is: $ai_generation carries input / output_choices / tools (embeddings carry input); $ai_span and $ai_trace carry input_state / output_state. The full native column list is in posthog/hogql/database/schema/ai_events.py.

Access patterns

posthog.ai_events is ORDER BY (team_id, trace_id, timestamp), so `trace_id` is the access path, not `timestamp`. Rows are dropped after the retention period (30 days by default), so traces older than that have no content.

Single trace (you have the ID): read it directly.

SELECT timestamp, span_id, event, model, input, output_choices
FROM posthog.ai_events
WHERE trace_id = '<trace_id>'
ORDER BY timestamp

Batch / analytics (a time window across many traces): filter the timestamp-indexed events table to get the trace IDs, then fetch the heavy content from posthog.ai_events anchored on trace_id.

WITH matching_traces AS (
    SELECT DISTINCT properties.$ai_trace_id AS trace_id
    FROM events
    WHERE event = '$ai_generation'
        AND timestamp >= now() - INTERVAL 7 DAY
        AND properties.$ai_model = 'gpt-4o'
)
SELECT a.trace_id, a.span_id, a.model, a.input, a.output_choices
FROM posthog.ai_events AS a
WHERE a.trace_id IN (SELECT trace_id FROM matching_traces)
ORDER BY a.trace_id, a.timestamp

AI observability reviews

Trace review (system.trace_reviews)

Trace reviews are review records attached to LLM traces. Each active trace can have at most one active review at a time.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key team_id | integer | NOT NULL | Owning team trace_id | varchar(255) | NOT NULL | Reviewed LLM trace ID created_by_id | integer | NULL | User ID that originally created the review reviewed_by_id | integer | NULL | User ID that last saved the review comment | text | NULL | Optional comment attached to the review created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NULL | Last save timestamp deleted | integer | NULL | Soft-delete flag (0 = active, 1 = deleted) deleted_at | timestamp with tz | NULL | When the review was soft-deleted

Key relationships

• Review scores: One trace review can have many system.trace_review_scores rows via review_id
• Pending queue items: trace_id overlaps with system.review_queue_items.trace_id

---

Trace review score (system.trace_review_scores)

Trace review scores store the saved scorer values for a review. Each row captures one scorer definition and exactly one value type.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key team_id | integer | NOT NULL | Owning team review_id | uuid | NOT NULL | FK to system.trace_reviews.id definition_id | uuid | NOT NULL | Stable scorer definition ID definition_version | uuid | NOT NULL | Immutable scorer version ID used when saving the score definition_version_number | integer | NOT NULL | Immutable scorer version number used when saving the score definition_config | jsonb | NOT NULL | Snapshot of the scorer configuration used for validation categorical_values | array(varchar) | NULL | Selected categorical option keys numeric_value | decimal(12,6) | NULL | Saved numeric score boolean_value | boolean | NULL | Saved boolean score created_by_id | integer | NULL | User ID that created the score row created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NULL | Last update timestamp

Important notes

• Exactly one of categorical_values, numeric_value, or boolean_value is populated per row
• Use definition_config when you need the historical scoring rules rather than the current scorer definition

---

Review queue (system.review_queues)

Review queues are named buckets used to route traces that still need review.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key team_id | integer | NOT NULL | Owning team name | varchar(255) | NOT NULL | Display name for the queue created_by_id | integer | NULL | User ID that created the queue created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NULL | Last update timestamp deleted | integer | NULL | Soft-delete flag (0 = active, 1 = deleted) deleted_at | timestamp with tz | NULL | When the queue was soft-deleted

Key relationships

• Queue items: One review queue can have many system.review_queue_items rows via queue_id

---

Review queue item (system.review_queue_items)

Review queue items are pending trace assignments inside review queues. An active trace can only be pending in one queue at a time.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key team_id | integer | NOT NULL | Owning team queue_id | uuid | NOT NULL | FK to system.review_queues.id trace_id | varchar(255) | NOT NULL | Pending LLM trace ID created_by_id | integer | NULL | User ID that queued the trace created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NULL | Last update timestamp deleted | integer | NULL | Soft-delete flag (0 = active, 1 = deleted) deleted_at | timestamp with tz | NULL | When the queue item was soft-deleted

Important notes

• Queue items represent pending work, not completed reviews
• Saving a matching trace review may soft-delete the pending queue item

---

Score definition (system.score_definitions)

Score definitions (a.k.a. "scorers") are reusable structured-score fields used by trace reviews. Each scorer has a stable identity but config is versioned and immutable — bumping config creates a new version row.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Stable scorer ID team_id | integer | NOT NULL | Owning team name | varchar(255) | NOT NULL | Display name description | text | NOT NULL | Optional description (defaults to empty string) kind | varchar(32) | NOT NULL | One of categorical, numeric, boolean. Immutable after creation archived | boolean | NOT NULL | Whether the scorer is archived (hidden from default lists) current_version_id | uuid | NULL | FK to the latest score_definition_versions row (config + version number) created_by_id | integer | NULL | User ID that created the scorer created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NULL | Last metadata update timestamp

Key relationships

• Score values: system.trace_review_scores.definition_id references id and definition_version references the current/historical version
• Versions: current_version_id points to the current immutable config; the version table itself is not exposed via HogQL — fetch full version detail through the REST API tools

Important notes

• kind is immutable. Create a new scorer of the desired kind and archive the old one (there is no destroy endpoint)
• Filter on archived = false to mirror the default product UX
• Use the REST llma-score-definition-get tool when you need the full config payload — only metadata is exposed here

---

Common query patterns

List active trace reviews with their saved score counts:

SELECT
    r.id,
    r.trace_id,
    r.reviewed_by_id,
    r.updated_at,
    count(s.id) AS score_count
FROM system.trace_reviews AS r
LEFT JOIN system.trace_review_scores AS s ON s.review_id = r.id
WHERE r.deleted = 0
GROUP BY r.id, r.trace_id, r.reviewed_by_id, r.updated_at
ORDER BY r.updated_at DESC
LIMIT 20

List active review queues with pending item counts:

SELECT
    q.id,
    q.name,
    count(i.id) AS pending_item_count
FROM system.review_queues AS q
LEFT JOIN system.review_queue_items AS i
    ON i.queue_id = q.id
   AND i.deleted = 0
WHERE q.deleted = 0
GROUP BY q.id, q.name
ORDER BY q.name ASC

Find pending traces in a specific review queue:

SELECT
    i.trace_id,
    i.created_at,
    i.created_by_id
FROM system.review_queue_items AS i
WHERE i.queue_id = '01234567-89ab-cdef-0123-456789abcdef'
  AND i.deleted = 0
ORDER BY i.created_at ASC
LIMIT 100

List review scores for recently updated reviews:

SELECT
    r.trace_id,
    s.definition_id,
    s.definition_version_number,
    s.categorical_values,
    s.numeric_value,
    s.boolean_value
FROM system.trace_review_scores AS s
INNER JOIN system.trace_reviews AS r ON r.id = s.review_id
WHERE r.deleted = 0
ORDER BY r.updated_at DESC, s.created_at ASC
LIMIT 100

List active scorers with how many times each has been used:

SELECT
    d.id,
    d.name,
    d.kind,
    count(s.id) AS uses
FROM system.score_definitions AS d
LEFT JOIN system.trace_review_scores AS s ON s.definition_id = d.id
WHERE d.archived = false
GROUP BY d.id, d.name, d.kind
ORDER BY uses DESC, d.name ASC
LIMIT 50

Alerts

AlertConfiguration (system.alerts)

Alerts monitor insight values and notify subscribed users when thresholds are breached.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key team_id | integer | NOT NULL | Team this alert belongs to name | varchar(255) | NOT NULL | Human-readable alert name (can be blank) insight_id | integer | NOT NULL | FK to the insight being monitored enabled | boolean | NOT NULL | Whether the alert is actively evaluated state | varchar(10) | NOT NULL | Firing, Not firing, Errored, or Snoozed calculation_interval | varchar(10) | NULL | Check frequency: hourly, daily, weekly, or monthly condition | jsonb | NOT NULL | Alert condition: {"type": "absolute_value" | "relative_increase" | "relative_decrease"} config | jsonb | NULL | Trends config: {"type": "TrendsAlertConfig", "series_index": int, "check_ongoing_interval": bool} created_at | timestamp with tz | NOT NULL | Creation timestamp last_notified_at | timestamp with tz | NULL | When subscribers were last notified last_checked_at | timestamp with tz | NULL | When the alert was last evaluated next_check_at | timestamp with tz | NULL | When the next evaluation is scheduled snoozed_until | timestamp with tz | NULL | Snooze expiry (UTC) skip_weekend | boolean | NULL | Whether to skip evaluation on Saturday and Sunday

Key Relationships

• Each alert monitors exactly one Insight (insight_id → system.insights.id)
• Alerts belong to a Team (team_id)
• Subscribers are managed via AlertSubscription (not exposed as a system table)

Important Notes

• The condition.type determines evaluation mode:
• absolute_value — fires when the value crosses the threshold bounds
• relative_increase — fires when the value increases beyond the threshold
• relative_decrease — fires when the value decreases beyond the threshold
• The config.series_index selects which series in a multi-series insight to monitor
• Alerts have a per-team limit (2 on the free tier, higher on paid plans)

Annotations

Annotation (system.annotations)

Annotations are timestamped notes used to mark product changes, incidents, or releases directly on charts.

Columns

Column | Type | Nullable | Description id | integer | NOT NULL | Primary key (auto-generated) team_id | integer | NOT NULL | Project/team ID for isolation content | varchar(8192) | NULL | Annotation text content scope | varchar(24) | NOT NULL | Annotation scope: project, organization, dashboard, dashboard_item, recording creation_type | varchar(3) | NOT NULL | Creation source: USR (user) or GIT (GitHub/bot) date_marker | timestamp with tz | NULL | Timestamp shown on charts deleted | boolean | NOT NULL | Soft delete flag dashboard_item_id | integer | NULL | Linked insight ID (if scoped to insight) dashboard_id | integer | NULL | Linked dashboard ID (if scoped to dashboard) created_by_id | integer | NULL | Creator user ID created_at | timestamp with tz | NULL | Creation timestamp updated_at | timestamp with tz | NOT NULL | Last update timestamp

Key Relationships

• Insights: dashboard_item_id -> system.insights.id
• Dashboards: dashboard_id -> system.dashboards.id

Important Notes

• The API usually hides deleted=true rows; SQL queries should filter them explicitly when needed.
• scope='organization' annotations can appear across multiple projects in the same organization.

---

Common Query Patterns

List recent non-deleted annotations:

SELECT id, scope, content, date_marker, created_at
FROM system.annotations
WHERE NOT deleted
ORDER BY date_marker DESC NULLS LAST
LIMIT 100

Find annotations around a release window:

SELECT id, content, scope, date_marker
FROM system.annotations
WHERE NOT deleted
  AND date_marker >= toDateTime('2026-03-01 00:00:00')
  AND date_marker < toDateTime('2026-03-08 00:00:00')
ORDER BY date_marker ASC

Get organization-scoped annotations only:

SELECT id, content, date_marker, created_by_id
FROM system.annotations
WHERE NOT deleted
  AND scope = 'organization'
ORDER BY date_marker DESC NULLS LAST

APM / tracing (OpenTelemetry spans)

The posthog.trace_spans table holds OpenTelemetry span data from instrumented services. Each row is one span — a unit of work in a distributed trace. Spans within the same trace share a trace_id; the parent-child hierarchy is reconstructed via parent_span_id → span_id.

Namespacing: Reference this table as posthog.trace_spans, not bare trace_spans — it's registered under the posthog. namespace in the HogQL database (see posthog/hogql/database/database.py). The same applies to posthog.trace_attributes. Bare names fail with "Unknown table" at HogQL compile time. (Asymmetric with logs, which is registered at root level — logs works without a prefix.)

Prefer the typed tools when they fit: posthog:query-apm-spans for span listing with structured filters, posthog:apm-trace-get for full-trace fetches, posthog:apm-spans-aggregate / posthog:apm-spans-tree for aggregations. Reach for HogQL when you need cross-signal joins (with logs or posthog.metrics by trace_id), exemplar lookups, or aggregations the typed tools don't expose.

posthog.trace_spans

OpenTelemetry spans. One row per span. Backed by ClickHouse trace_spans_distributed.

Columns

Sort key

(team_id, time_bucket, service_name, resource_fingerprint, status_code, name, timestamp). Queries that filter on service_name + time_bucket are very efficient. Filters on name further narrow the read.

Important notes

• Durations are nanoseconds. Filter duration_nano > 1000000000 for spans longer than 1 second.
• `status_code == 2` is Error. Use status_code = 2 (not the string "ERROR").
• `trace_id`, `span_id`, `parent_span_id` are base64-encoded bytes, not hex. The MCP layer (posthog:query-apm-spans, posthog:apm-trace-get) converts to hex via hex(tryBase64Decode(...)) for display. Raw HogQL queries against this table see the base64 form.
• `parent_span_id` of a root span is 'AAAAAAAAAAA=' (12-char base64 of 8 zero bytes), not null. Use `is_root_span` to find trace entries — don't string-match the padding.
• Use `hex(tryBase64Decode(trace_id))` to display trace_ids in hex for human-readable output.
• Cross-signal joins by trace_id work against logs (both store base64). For posthog.metrics, exemplar extraction is not yet wired up in the ingestion pipeline — see the metrics reference for the current state.
• User HogQL queries on posthog.trace_spans are capped at 50 GB read per query.

posthog.trace_attributes

AggregatingMergeTree rollup of span attribute values, partitioned by service and 10-minute bucket. Backs the attribute discovery endpoints used by posthog:apm-attributes-list and posthog:apm-attribute-values-list. Same posthog. namespacing rule — reference as posthog.trace_attributes.

Columns

Prefer posthog:apm-attributes-list / posthog:apm-attribute-values-list over querying this table directly — they handle the aggregation correctly.

Common query patterns

Top-10 slowest root spans for a service in the last hour (convert trace_id to hex for display):

SELECT name, duration_nano, hex(tryBase64Decode(trace_id)) AS trace_id, timestamp
FROM posthog.trace_spans
WHERE service_name = 'checkout'
  AND is_root_span
  AND timestamp >= now() - INTERVAL 1 HOUR
ORDER BY duration_nano DESC
LIMIT 10

Error rate per service in the last hour:

SELECT
    service_name,
    countIf(status_code = 2) AS errors,
    count() AS total,
    errors / total AS error_rate
FROM posthog.trace_spans
WHERE timestamp >= now() - INTERVAL 1 HOUR
GROUP BY service_name
HAVING total > 100
ORDER BY error_rate DESC

Find traces touching both `payments` and `inventory` services:

SELECT hex(tryBase64Decode(trace_id)) AS trace_id, min(timestamp) AS started, count() AS span_count
FROM posthog.trace_spans
WHERE service_name IN ('payments', 'inventory')
  AND timestamp >= now() - INTERVAL 1 HOUR
GROUP BY trace_id
HAVING uniqExact(service_name) = 2
ORDER BY started DESC
LIMIT 20

Batch exports

BatchExport (system.batch_exports)

Batch exports define recurring data export jobs that send events, persons, or sessions to external destinations.

Columns

Key Relationships

• Each batch export belongs to a Team (team_id)
• Backfills reference this table via system.batch_export_backfills.batch_export_id

Important Notes

• Filter with deleted = 0 to exclude soft-deleted exports
• Filter with paused = 0 to find actively running exports
• Destination details (type, connection config) are not in this table; use the batch-export-get MCP tool instead
• Run history is not directly queryable via SQL; batch-export-get returns the 10 most recent runs in latest_runs — for older runs use the PostHog UI (the runs endpoints are not exposed as MCP tools)

---

BatchExportBackfill (system.batch_export_backfills)

Backfills are one-time historical data export jobs triggered for a batch export.

Columns

Key Relationships

• Each backfill belongs to a BatchExport (batch_export_id → system.batch_exports.id)
• Each backfill belongs to a Team (team_id)

Important Notes

• Status values: Starting, Running, Completed, Failed, FailedRetryable, Cancelled, ContinuedAsNew, Terminated, TimedOut
• A NULL start_at means backfilling from the earliest available data
• A NULL end_at means backfilling up to the current time

Early Access Features

EarlyAccessFeature (system.early_access_features)

Early access features let teams manage staged feature rollouts where users can opt in. Each feature is linked to a feature flag that controls access.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key team_id | integer | NOT NULL | Project/team ID for isolation feature_flag_id | integer | NULL | Linked feature flag ID name | varchar(200) | NOT NULL | Feature name description | text | NOT NULL | Longer description shown in the opt-in UI (may be empty string) stage | varchar(40) | NOT NULL | Lifecycle stage (see values below) documentation_url | varchar(800) | NOT NULL | URL to external docs (may be empty string) created_at | timestamp with tz | NOT NULL | Creation timestamp

Stage Values

Value | Description draft | Initial stage, not visible to users concept | Gauging interest, opt-in tracked but feature flag not enabled alpha | Active stage, opted-in users get the feature flag enabled beta | Active stage, opted-in users get the feature flag enabled general-availability | Active stage, feature available to all users archived | Feature retired, flag enrollment conditions removed

Active stages (where opted-in users get the feature flag enabled): alpha, beta, general-availability.

Key Relationships

• Feature flags: feature_flag_id -> system.feature_flags.id

Important Notes

• The stage field uses a hyphenated value general-availability (not underscore).
• Features without a feature_flag_id are rare but possible during creation errors.
• There is no soft-delete column; deleted features are removed from the table.

---

Common Query Patterns

List all early access features with their stages:

SELECT id, name, stage, feature_flag_id, created_at
FROM system.early_access_features
ORDER BY created_at DESC
LIMIT 100

Find active features (in alpha, beta, or GA):

SELECT id, name, stage, feature_flag_id
FROM system.early_access_features
WHERE stage IN ('alpha', 'beta', 'general-availability')
ORDER BY created_at DESC

Join with feature flags to see flag keys:

SELECT eaf.id, eaf.name, eaf.stage, ff.key AS flag_key
FROM system.early_access_features AS eaf
LEFT JOIN system.feature_flags AS ff ON eaf.feature_flag_id = ff.id
ORDER BY eaf.created_at DESC
LIMIT 100