Query Examples

Querying data in PostHog

If the MCP server haven't provided instructions on querying data in PostHog, read the guidelines.

Data Schema

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

• Activity logs
• Actions
• Alerts
• Annotations
• Batch exports
• Early Access Features
• Cohorts & Persons
• Dashboards, Tiles & Insights
• Data Warehouse
• Data Modeling Endpoints
• Error Tracking
• Flags & Experiments
• Hog Flows
• Hog Functions
• Integrations
• Logs
• Notebooks
• Session Recording Playlists
• Session Recordings
• Support Tickets
• Surveys
• 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)
• 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 acos acosh addDays addHours addMinutes addMonths addQuarters addSeconds addWeeks addYears age alphaTokens and any anyHeavy anyLast appendTrailingCharIfAbsent argMax argMaxMerge argMin argMinMerge 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 countDistinctArgMax countDistinctArgMaxOrDefault countDistinctArgMaxOrNull countDistinctArgMin countDistinctArgMinOrDefault countDistinctArgMinOrNull countDistinctArray countDistinctArrayOrDefault countDistinctArrayOrNull countDistinctForEach countDistinctForEachOrDefault countDistinctForEachOrNull countDistinctIf 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 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 sumForEachOrDefault sumForEachOrNull 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 toFloatOrDefault 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
    e.issue_id AS id,
    max(timestamp) AS last_seen,
    min(timestamp) AS first_seen,
    argMax(properties.$exception_functions.-1, timestamp) AS function,
    argMax(properties.$exception_sources.-1, timestamp) AS source,
    count(DISTINCT uuid) AS occurrences,
    count(DISTINCT nullIf($session_id, '')) AS sessions,
    count(DISTINCT coalesce(nullIf(toString(person_id), '00000000-0000-0000-0000-000000000000'), distinct_id)) AS users,
    sumForEach(arrayMap(bin -> if(and(greater(timestamp, bin), lessOrEquals(dateDiff('seconds', bin, timestamp), divide(dateDiff('seconds', toDateTime(toDateTime('2025-12-09 00:00:00.000000')), toDateTime(toDateTime('2025-12-10 00:00:00.000000'))), 20))), 1, 0), arrayMap(i -> dateAdd(toDateTime(toDateTime('2025-12-09 00:00:00.000000')), toIntervalSecond(multiply(i, divide(dateDiff('seconds', toDateTime(toDateTime('2025-12-09 00:00:00.000000')), toDateTime(toDateTime('2025-12-10 00:00:00.000000'))), 20)))), range(0, 20)))) AS volumeRange,
    argMin(tuple(uuid, distinct_id, timestamp, properties), timestamp) AS first_event,
    argMax(properties.$lib, timestamp) AS library
FROM
    events AS e
WHERE
    and(equals(event, '$exception'), isNotNull(e.issue_id), equals(properties.tag, 'max_ai'), greaterOrEquals(timestamp, toDateTime(toDateTime('2025-12-09 00:00:00.000000'))), lessOrEquals(timestamp, toDateTime(toDateTime('2025-12-10 00:00:00.000000'))), or(greater(position(lower(properties.$exception_types), lower('constant')), 0), greater(position(lower(properties.$exception_values), lower('constant')), 0), greater(position(lower(properties.$exception_sources), lower('constant')), 0), greater(position(lower(properties.$exception_functions), lower('constant')), 0), greater(position(lower(properties.email), lower('constant')), 0), greater(position(lower(person.properties.email), lower('constant')), 0)))
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(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.

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.

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(max_observed_timestamp)
        FROM
            logs_kafka_metrics) 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('2025-12-09 00:00:00.000000')), 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

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, '\\/llm-analytics\\/traces\\/[0-9a-f\\-]+', '/llm-analytics/traces/<trace_id>') AS path_item_2,
                        replaceRegexpAll(path_item_2, '\\/llm-analytics\\/sessions\\/[0-9a-f\\-]+', '/llm-analytics/sessions/<session_id>') AS path_item_3,
                        replaceRegexpAll(path_item_3, '\\/llm-analytics\\/evaluations\\/[0-9a-f\\-]+', '/llm-analytics/evaluations/<evaluation_id>') AS path_item_4,
                        replaceRegexpAll(path_item_4, '\\/llm-analytics\\/datasets\\/[0-9a-f\\-]+', '/llm-analytics/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), '%llm-analytics%'), 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) -> if(has(start_event_timestamps, interval_date), minus(interval_index, 1), -1), arrayEnumerate(date_range), date_range))) 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('2025-12-10 00:00:00.000000'), expiry_time) AS recording_ttl,
    greaterOrEquals(max(s._timestamp), toDateTime('2025-12-09 23:55:00.000000')) 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
FROM
    raw_session_replay_events AS s
WHERE
    and(greaterOrEquals(s.min_first_timestamp, toDateTime('2025-12-07 00:00:00.000000')), lessOrEquals(s.min_first_timestamp, toDateTime('2025-12-10 00:00:00.000000')))
GROUP BY
    session_id
HAVING
    and(greaterOrEquals(expiry_time, toDateTime('2025-12-10 00:00:00.000000')), 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('2025-12-10 00:00:05.000000')), greater($start_timestamp, toDateTime('2025-12-09 00:00:00.000000')))
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', '$capture_metrics', '$create_alias', '$merge_dangerously', '$groupidentify']))
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,
                    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,
                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) -> minus(max_num, min_num), arrayZip(max_nums, min_nums)) AS diff,
                [10] AS bins,
                arrayMap(i -> arrayMap(x -> [plus(multiply(divide(diff[i], bins[i]), x), min_nums[i]), plus(plus(multiply(divide(diff[i], bins[i]), plus(x, 1)), min_nums[i]), if(equals(plus(x, 1), bins[i]), 0.01, 0))], range(bins[i])), range(1, 2)) 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`
HAVING
    notEquals(`context.columns.breakdown_value`, NULL)
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 query-examples.

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.

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.

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

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

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; use the batch-export-runs-list MCP tool

---

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

Cohorts & Persons

Cohort (system.cohorts)

Cohorts are groups of persons used for segmentation and targeting.

Columns

Column | Type | Nullable | Description id | integer | NOT NULL | Primary key (auto-generated) name | varchar(400) | NULL | Cohort display name description | varchar(1000) | NOT NULL | Cohort description deleted | boolean | NOT NULL | Soft delete flag filters | jsonb | NULL | Modern filter structure for cohort criteria query | jsonb | NULL | HogQL query for analytical cohorts version | integer | NULL | Current calculation version pending_version | integer | NULL | Version being calculated count | integer | NULL | Cached person count created_at | timestamp with tz | NULL | Creation timestamp is_calculating | boolean | NOT NULL | Whether calculation is in progress last_calculation | timestamp with tz | NULL | Timestamp of last successful calculation errors_calculating | integer | NOT NULL | Consecutive error count last_error_at | timestamp with tz | NULL | Timestamp of last calculation error is_static | boolean | NOT NULL | Static (manually uploaded) vs dynamic cohort cohort_type | varchar(50) | NULL | One of: static, person_property, behavioral, realtime, analytical created_by_id | integer | NULL | Creator user ID

Cohort Types

Type | Description static | Manually uploaded/managed list of persons person_property | Based on person properties (e.g., email contains "example.com") behavioral | Based on events performed (e.g., "viewed pricing page in last 30 days") realtime | Can be evaluated in real-time (< 20M persons) analytical | Complex queries with temporal/sequential logic via HogQL

Filters Structure Examples

Behavioral filter (performed event):

{
  "properties": {
    "type": "OR",
    "values": [
      {
        "key": "address page viewed",
        "type": "behavioral",
        "value": "performed_event",
        "negation": false,
        "event_type": "events",
        "time_value": "30",
        "time_interval": "day"
      }
    ]
  }
}

Person property filter:

{
  "properties": {
    "type": "OR",
    "values": [
      {
        "key": "email",
        "type": "person",
        "value": ["@example.com"],
        "negation": false,
        "operator": "icontains"
      }
    ]
  }
}

Cohort reference filter (nested cohorts):

{
  "properties": {
    "type": "OR",
    "values": [
      {
        "key": "id",
        "type": "cohort",
        "value": 8814,
        "negation": false
      }
    ]
  }
}

Key Relationships

• Persons: Many-to-many via raw_cohort_people table
• Calculation History: One-to-many via system.cohort_calculation_history
• Experiments: Referenced by system.experiments.exposure_cohort_id

Important Notes

• Cohorts can reference other cohorts creating nested dependencies
• realtime cohorts are cleared to NULL type if they exceed 20M persons
• Static cohorts are populated via CSV upload or API
• Dynamic cohorts are recalculated periodically

---

Cohort Calculation History (system.cohort_calculation_history)

Audit trail for cohort calculation jobs.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key filters | jsonb | NOT NULL | Cohort filters at calculation time count | integer | NULL | Number of persons in cohort (>= 0) started_at | timestamp with tz | NOT NULL | Calculation start time finished_at | timestamp with tz | NULL | Calculation end time (NULL = in progress) queries | jsonb | NULL | Array of query statistics error | text | NULL | Full error message if failed error_code | varchar(64) | NULL | Categorized error code cohort_id | integer | NOT NULL | FK to system.cohorts.id

Error Codes

Code | Description capacity | System busy interrupted | Socket timeout timeout | Query timeout (> 1200s) memory_limit | Memory exceeded query_size | Query too large invalid_regex | Regex compilation error incompatible_types | Type mismatch no_properties | No filters defined validation_error | Generic validation error

Queries Structure

[
  {
    "query": "SELECT ...",
    "query_id": "abc123",
    "query_ms": 1234,
    "memory_mb": 256,
    "read_rows": 1000000,
    "written_rows": 5000
  }
]

Entity Relationships Diagram

system.cohorts (main cohort definition)
├── <- system.cohort_calculation_history.cohort_id
└── persons through `IN COHORT`

system.experiments
└── exposure_cohort_id -> system.cohorts.id

---

Common Query Patterns

Find cohorts by name:

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

Get cohort with member count:

SELECT c.id, c.name, c.count, c.last_calculation
FROM system.cohorts c
WHERE c.id = 123

List persons in a cohort (via events):

By cohort ID:

SELECT DISTINCT person_id, person.properties.email
FROM events
WHERE person_id IN COHORT 123
LIMIT 100

List people in a cohort by its name:

select count()
from persons
where id IN COHORT 'Case-sensitive cohort name'

Check cohort calculation history:

SELECT id, started_at, finished_at, count, error_code
FROM system.cohort_calculation_history
WHERE cohort_id = 123
ORDER BY started_at DESC
LIMIT 10

Find people in a cohort of a specific version:

SELECT
    tuple(coalesce(toString(properties.email), toString(properties.name), toString(properties.username), toString(id)), toString(id)),
    id,
    created_at
FROM
    persons
WHERE
    in(id, (SELECT
            person_id
        FROM
            raw_cohort_people
        WHERE
            and(equals(cohort_id, 212606), equals(version, 2))))
ORDER BY
    id ASC
LIMIT 101
OFFSET 0

Dashboards, Tiles & Insights

Dashboard (system.dashboards)

Dashboards are collections of insights that provide a unified view of analytics data.

Columns

Column | Type | Nullable | Description id | integer | NOT NULL | Primary key (auto-generated) name | varchar(400) | NULL | Dashboard name description | text | NOT NULL | Dashboard description pinned | boolean | NOT NULL | Whether dashboard is pinned created_at | timestamp with tz | NOT NULL | Creation timestamp deleted | boolean | NOT NULL | Soft delete flag last_accessed_at | timestamp with tz | NULL | Last access timestamp filters | jsonb | NOT NULL | Dashboard-level filters applied to all insights creation_mode | varchar(16) | NOT NULL | How dashboard was created: default, template, duplicate, unlisted restriction_level | smallint | NOT NULL | Access restriction: 21 (everyone can edit), 37 (only collaborators) created_by_id | integer | NULL | Creator user ID variables | jsonb | NULL | Dashboard variables for dynamic filtering breakdown_colors | jsonb | NULL | Custom breakdown color assignments data_color_theme_id | integer | NULL | Color theme ID last_refresh | timestamp with tz | NULL | Last refresh timestamp

Important Notes

• The default manager excludes soft-deleted dashboards (deleted=True)
• creation_mode='unlisted' dashboards are hidden from general lists (used for product dashboards like LLM Analytics)
• Use filters to store dashboard-level date ranges and property filters

---

Insight (system.insights)

Insights are saved analytics queries that visualize data.

Columns

Column | Type | Nullable | Description id | integer | NOT NULL | Primary key (auto-generated) name | varchar(400) | NULL | User-defined insight name derived_name | varchar(400) | NULL | Auto-generated name from query description | varchar(400) | NULL | Insight description filters | jsonb | NOT NULL | Filter configuration (legacy, prefer query) filters_hash | varchar(400) | NULL | Hash for caching query | jsonb | NULL | Modern HogQL query definition (preferred) query_metadata | jsonb | NULL | Extracted query metadata for indexing order | integer | NULL | Display order deleted | boolean | NOT NULL | Soft delete flag saved | boolean | NOT NULL | Whether insight is saved (vs temporary) created_at | timestamp with tz | NULL | Creation timestamp refreshing | boolean | NOT NULL | Whether currently refreshing is_sample | boolean | NOT NULL | Whether this is sample data short_id | varchar(12) | NOT NULL | Unique short identifier for URLs favorited | boolean | NOT NULL | Whether favorited by user refresh_attempt | integer | NULL | Number of refresh attempts last_modified_at | timestamp with tz | NOT NULL | Last modification timestamp updated_at | timestamp with tz | NOT NULL | Auto-updated timestamp created_by_id | integer | NULL | Creator user ID last_modified_by_id | integer | NULL | Last modifier user ID

Key Relationships

• Survey: Can be linked to surveys via system.surveys.linked_insight_id

Important Notes

• short_id is unique per team and used in URLs: /insights/{short_id}
• Only insights with saved=True appear in the insights list
• The default manager excludes soft-deleted insights

Data Warehouse

External Data Source (system.data_warehouse_sources)

External data sources represent connections to third-party data providers (Stripe, Hubspot, Postgres, etc.) that sync data into PostHog.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key source_id | varchar(400) | NOT NULL | External identifier for the source connection_id | varchar(400) | NOT NULL | Connection identifier destination_id | varchar(400) | NULL | Destination identifier source_type | varchar(128) | NOT NULL | Type of source (Stripe, Hubspot, Postgres, etc.) status | varchar(400) | NOT NULL | Current sync status prefix | varchar(100) | NULL | Prefix applied to synced table names description | varchar(400) | NULL | User-defined description are_tables_created | boolean | NOT NULL | Whether tables have been created created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NOT NULL | Last update timestamp deleted | boolean | NOT NULL | Soft delete flag deleted_at | timestamp with tz | NULL | Deletion timestamp

Source Types

Common source types include:

• Stripe - Payment and subscription data
• Hubspot - CRM and marketing data
• Postgres - PostgreSQL databases
• MySQL - MySQL databases
• Snowflake - Snowflake data warehouse
• BigQuery - Google BigQuery
• S3 - Amazon S3 files
• Zendesk - Customer support data
• Salesforce - CRM data

Key Relationships

• Tables: One source can have many system.data_warehouse_tables entries

---

Data Warehouse Table (system.data_warehouse_tables)

Individual tables synced from external sources or manually uploaded. Each table contains columns with their types and metadata.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key name | varchar(128) | NOT NULL | Table name (may include prefix) columns | jsonb | NULL | Column definitions with types row_count | integer | NULL | Number of rows synced external_data_source_id | uuid | NULL | FK to source created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NOT NULL | Last update timestamp deleted | boolean | NOT NULL | Soft delete flag deleted_at | timestamp with tz | NULL | Deletion timestamp

Columns JSON Structure

The columns field contains column definitions with their types:

{
  "id": {
    "hogql": "IntegerDatabaseField",
    "clickhouse": "Int64",
    "valid": true
  },
  "email": {
    "hogql": "StringDatabaseField",
    "clickhouse": "Nullable(String)",
    "valid": true
  },
  "created_at": {
    "hogql": "DateTimeDatabaseField",
    "clickhouse": "DateTime64(3)",
    "valid": true
  }
}

Key Relationships

• Source: external_data_source_id -> system.data_warehouse_sources.id

Important Notes

• Table names may include source prefix (e.g., stripe_customers for Stripe source with no custom prefix)
• The columns field is synced from the actual data schema
• valid: false columns may have type mismatches or other issues
• Tables with external_data_source_id are managed by the sync system
• Tables without a source are user-uploaded or manually created

---

Source Schemas (system.source_schemas)

Per-table sync configuration for external data sources. Each schema represents one table or entity being synced from an external source.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key name | varchar(400) | NOT NULL | Schema/table name (e.g., customers, invoices) source_id | uuid | NOT NULL | FK to system.data_warehouse_sources.id table_id | uuid | NULL | FK to system.data_warehouse_tables.id should_sync | boolean | NOT NULL | Whether this schema is enabled for syncing status | varchar(400) | NULL | Current sync status sync_type | varchar(128) | NULL | Sync strategy last_synced_at | timestamp with tz | NULL | Last successful sync timestamp latest_error | text | NULL | Most recent error message created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NOT NULL | Last update timestamp deleted | boolean | NOT NULL | Soft delete flag deleted_at | timestamp with tz | NULL | Deletion timestamp

Status Values

• Running - Sync currently in progress
• Paused - Sync paused by user
• Completed - Last sync finished successfully
• Failed - Last sync encountered an error
• BillingLimitReached - Stopped due to billing limit
• BillingLimitTooLow - Billing limit too low to sync

Sync Types

• full_refresh - Full data reload each sync
• incremental - Only sync new/changed data
• append - Append new data without updating existing rows

Key Relationships

• Source: source_id -> system.data_warehouse_sources.id
• Table: table_id -> system.data_warehouse_tables.id

---

Source Sync Jobs (system.source_sync_jobs)

Individual sync job runs for external data sources. Each job tracks the status, row count, and timing of a single sync operation.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key pipeline_id | uuid | NOT NULL | FK to system.data_warehouse_sources.id schema_id | uuid | NULL | FK to schema being synced status | varchar | NOT NULL | Job status rows_synced | bigint | NULL | Number of rows synced billable | boolean | NULL | Whether this sync job is billable (non-billable syncs don't appear in the syncs UI) latest_error | text | NULL | Error message if failed created_at | timestamp with tz | NOT NULL | Job start timestamp finished_at | timestamp with tz | NULL | Job completion timestamp updated_at | timestamp with tz | NOT NULL | Last update timestamp

Status Values

• Running - Sync currently in progress
• Completed - Sync finished successfully
• Failed - Sync encountered an error
• BillingLimitReached - Stopped due to billing limit
• BillingLimitTooLow - Billing limit too low to sync

Key Relationships

• Source: pipeline_id -> system.data_warehouse_sources.id

---

Common Query Patterns

List all data warehouse tables:

SELECT name, row_count, created_at
FROM system.data_warehouse_tables
WHERE NOT deleted
ORDER BY created_at DESC

Find tables by source type:

SELECT t.name, t.row_count, s.source_type
FROM system.data_warehouse_tables AS t
INNER JOIN system.data_warehouse_sources AS s ON t.external_data_source_id = s.id
WHERE NOT t.deleted AND s.source_type = 'Stripe'

List columns for a specific table:

SELECT name, columns
FROM system.data_warehouse_tables
WHERE name = 'stripe_customers' AND NOT deleted

Find tables with specific column:

SELECT name, JSONExtractString(columns, 'email', 'clickhouse') AS email_type
FROM system.data_warehouse_tables
WHERE NOT deleted
  AND JSONHas(columns, 'email')

List active data sources with table counts:

SELECT
  s.source_type,
  s.prefix,
  count(t.id) AS table_count,
  sum(t.row_count) AS total_rows
FROM system.data_warehouse_sources AS s
LEFT JOIN system.data_warehouse_tables AS t ON t.external_data_source_id = s.id AND NOT t.deleted
WHERE NOT s.deleted
GROUP BY s.source_type, s.prefix
ORDER BY table_count DESC

View recent sync jobs with their source type:

SELECT
  j.status,
  j.rows_synced,
  j.created_at,
  j.finished_at,
  j.latest_error,
  s.source_type
FROM system.source_sync_jobs AS j
INNER JOIN system.data_warehouse_sources AS s ON j.pipeline_id = s.id
ORDER BY j.created_at DESC
LIMIT 50

Find failed sync jobs in the last 7 days:

SELECT
  j.pipeline_id,
  j.latest_error,
  j.created_at,
  s.source_type,
  s.prefix
FROM system.source_sync_jobs AS j
INNER JOIN system.data_warehouse_sources AS s ON j.pipeline_id = s.id
WHERE j.status = 'Failed'
  AND j.created_at >= now() - INTERVAL 7 DAY
ORDER BY j.created_at DESC

Get sync statistics per source:

SELECT
  s.source_type,
  s.prefix,
  count(j.id) AS total_jobs,
  countIf(j.status = 'Completed') AS completed,
  countIf(j.status = 'Failed') AS failed,
  sum(j.rows_synced) AS total_rows_synced
FROM system.source_sync_jobs AS j
INNER JOIN system.data_warehouse_sources AS s ON j.pipeline_id = s.id
GROUP BY s.source_type, s.prefix
ORDER BY total_jobs DESC

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

Data Modeling Endpoints

Endpoint (system.data_modeling_endpoints)

API endpoints that expose saved HogQL or insight queries as callable API routes.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key name | varchar(128) | NOT NULL | URL-safe endpoint name (unique per team) is_active | integer | NOT NULL | Whether endpoint is available via the API (0/1) current_version | integer | NOT NULL | Latest version number derived_from_insight | varchar(12) | NULL | Short ID of the source insight created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NOT NULL | Last update timestamp last_executed_at | timestamp with tz | NULL | When endpoint was last executed

Example Queries

-- List all active endpoints with their current version SELECT name, is_active, current_version FROM system.data_modeling_endpoints WHERE is_active = 1

-- Find endpoints that haven't been executed recently SELECT name, last_executed_at FROM system.data_modeling_endpoints WHERE last_executed_at < now() - INTERVAL 30 DAY

-- Join with versions to get current version description SELECT e.name, ev.description, ev.version FROM system.data_modeling_endpoints e LEFT JOIN system.data_modeling_endpoint_versions ev ON ev.endpoint_id = e.id AND ev.version = e.current_version

Important Notes

• Endpoints are looked up by name, not id
• Use system.data_modeling_endpoint_versions to access version-specific details
• Boolean fields (is_active) are exposed as integers (0/1) for HogQL compatibility

---

Endpoint Version (system.data_modeling_endpoint_versions)

Immutable query snapshots. A new version is created each time an endpoint's query changes.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key endpoint_id | uuid | NOT NULL | FK to endpoints.id version | integer | NOT NULL | Version number (1-based, ascending) description | text | NOT NULL | Version description query | jsonb | NOT NULL | Immutable query snapshot cache_age_seconds | integer | NULL | Cache TTL in seconds is_active | integer | NOT NULL | Whether this version can be executed (0/1) columns | jsonb | NULL | Column names and types created_at | timestamp with tz | NOT NULL | When this version was created

Example Queries

-- Get version history for an endpoint SELECT ev.version, ev.description, ev.created_at FROM system.data_modeling_endpoint_versions ev LEFT JOIN system.data_modeling_endpoints e ON e.id = ev.endpoint_id WHERE e.name = 'my-endpoint' ORDER BY ev.version DESC

Error Tracking

ErrorTrackingIssue (system.error_tracking_issues)

Error tracking issues represent grouped exceptions captured by PostHog SDKs. Each issue aggregates multiple exception events that share the same fingerprint.

Columns

Column | Type | Nullable | Description id | uuid | NOT NULL | Primary key (UUID) team_id | integer | NOT NULL | FK to system.teams.id created_at | timestamp with tz | NOT NULL | Creation timestamp status | varchar | NOT NULL | Issue status (see Status Values below) name | text | NULL | Issue name (typically the exception type/message) description | text | NULL | User-provided description

Status Values

Status | Description active | Issue is currently active and being tracked archived | Issue has been archived (hidden from default views) resolved | Issue has been marked as resolved pending_release | Issue is pending verification in a new release suppressed | Issue has been suppressed from alerts and notifications

Key Relationships

• Fingerprints: Issues are linked to fingerprints (not queryable via HogQL)
• Cohorts: Issues can be linked to cohorts via system.cohorts
• Exception Events: Query via events table with event = '$exception' and issue_id

Important Notes

• Issues group exception events by fingerprint (a hash of exception characteristics)
• The name field is typically auto-populated from the first exception's type/message
• Use the events table with event = '$exception' and issue_id to query actual exception occurrences
• Issues can be merged (combining fingerprints) or split (separating fingerprints into new issues)

---

Common Query Patterns

Find issues by status:

SELECT id, name, status, created_at
FROM system.error_tracking_issues
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20

Find issues by name pattern:

SELECT id, name, description, status
FROM system.error_tracking_issues
WHERE name ILIKE '%timeout%'
  AND status != 'archived'

Count issues by status:

SELECT status, count() AS count
FROM system.error_tracking_issues
GROUP BY status
ORDER BY count DESC

Find exception events for a specific issue:

SELECT
    timestamp,
    properties.$exception_type AS exception_type,
    properties.$exception_message AS exception_message,
    properties.$exception_source AS source,
    person.id AS user_id
FROM events
WHERE event = '$exception'
  AND issue_id = '01234567-89ab-cdef-0123-456789abcdef'
  AND timestamp >= now() - INTERVAL 7 DAY
ORDER BY timestamp DESC
LIMIT 50

Aggregate exception stats by issue:

SELECT
    issue_id,
    count() AS occurrences,
    count(DISTINCT person.id) AS affected_users,
    min(timestamp) AS first_seen,
    max(timestamp) AS last_seen
FROM events
WHERE event = '$exception'
  AND isNotNull(issue_id)
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY issue_id
ORDER BY occurrences DESC
LIMIT 20

Join issues with exception events:

SELECT
    i.id,
    i.name,
    i.status
FROM system.error_tracking_issues AS i
WHERE i.status = 'active'
  AND i.id IN (
    SELECT DISTINCT issue_id
    FROM events
    WHERE event = '$exception'
      AND timestamp >= now() - INTERVAL 1 DAY
  )
ORDER BY i.created_at DESC

Flags & Experiments

Feature Flag (system.feature_flags)

Feature flags control rollouts of new features and are used for A/B testing.

Columns

Column | Type | Nullable | Description id | integer | NOT NULL | Primary key (auto-generated) key | varchar(400) | NOT NULL | Unique flag key per team name | text | NOT NULL | Flag description (not the display name) filters | jsonb | NOT NULL | Targeting conditions and variants rollout_percentage | integer | NULL | Overall rollout percentage created_at | timestamp with tz | NOT NULL | Creation timestamp deleted | boolean | NOT NULL | Soft delete flag active | boolean | NOT NULL | Whether flag is enabled rollback_conditions | jsonb | NULL | Automatic rollback configuration performed_rollback | boolean | NULL | Whether rollback was triggered ensure_experience_continuity | boolean | NULL | Sticky bucketing for users created_by_id | integer | NULL | Creator user ID usage_dashboard_id | integer | NULL | FK to system.dashboards.id has_enriched_analytics | boolean | NULL | Whether rich analytics enabled is_remote_configuration | boolean | NULL | Whether used as remote config has_encrypted_payloads | boolean | NULL | Whether payloads are encrypted last_modified_by_id | integer | NULL | Last modifier user ID version | integer | NULL | Version number for tracking changes evaluation_runtime | varchar(10) | NULL | server, client, or all updated_at | timestamp with tz | NULL | Last update timestamp last_called_at | timestamp with tz | NULL | Last evaluation timestamp bucketing_identifier | varchar(50) | NULL | distinct_id or device_id

Filters Structure

{
  "groups": [
    {
      "properties": [...],
      "rollout_percentage": 50,
      "variant": "test"
    }
  ],
  "multivariate": {
    "variants": [
      {"key": "control", "rollout_percentage": 50},
      {"key": "test", "rollout_percentage": 50}
    ]
  },
  "payloads": {
    "control": {"value": "A"},
    "test": {"value": "B"}
  },
  "aggregation_group_type_index": null
}

Key Relationships

• Experiments: Referenced by system.experiments.feature_flag_id
• Surveys: Can be linked via system.surveys

Important Notes

• key must be unique per team
• Flag evaluation results are cached in Redis
• aggregation_group_type_index enables group-based targeting (company-level flags)

---

Experiment (system.experiments)

Experiments are A/B tests that compare variants against a control group.

Columns

Column | Type | Nullable | Description id | integer | NOT NULL | Primary key (auto-generated) name | varchar(400) | NOT NULL | Experiment name description | varchar(400) | NULL | Experiment description filters | jsonb | NOT NULL | Target metric definition parameters | jsonb | NULL | Experiment configuration secondary_metrics | jsonb | NULL | Additional metrics to track start_date | timestamp with tz | NULL | When experiment started (NULL = draft) end_date | timestamp with tz | NULL | When experiment ended created_at | timestamp with tz | NOT NULL | Creation timestamp updated_at | timestamp with tz | NOT NULL | Last update timestamp archived | boolean | NOT NULL | Whether experiment is archived deleted | boolean | NULL | Soft delete flag created_by_id | integer | NULL | Creator user ID feature_flag_id | integer | NOT NULL | FK to system.feature_flags.id exposure_cohort_id | integer | NULL | FK to system.cohorts.id holdout_id | integer | NULL | Holdout ID type | varchar(40) | NULL | web or product variants | jsonb | NULL | Variant configuration metrics | jsonb | NULL | Primary metrics (new format) metrics_secondary | jsonb | NULL | Secondary metrics (new format) stats_config | jsonb | NULL | Statistical analysis configuration exposure_criteria | jsonb | NULL | Exposure event criteria conclusion | varchar(30) | NULL | won, lost, inconclusive, stopped_early, invalid conclusion_comment | text | NULL | Notes about conclusion scheduling_config | jsonb | NULL | Scheduled actions configuration primary_metrics_ordered_uuids | jsonb | NULL | Ordered primary metric UUIDs secondary_metrics_ordered_uuids | jsonb | NULL | Ordered secondary metric UUIDs

Parameters Structure

{
  "minimum_detectable_effect": 5,
  "recommended_running_time": 14,
  "recommended_sample_size": 1000,
  "feature_flag_variants": [
    {"key": "control", "name": "Control", "rollout_percentage": 50},
    {"key": "test", "name": "Test", "rollout_percentage": 50}
  ],
  "custom_exposure_filter": {...}
}

Key Relationships

• Feature Flag: feature_flag_id -> system.feature_flags.id (required)
• Exposure Cohort: exposure_cohort_id -> system.cohorts.id

Important Notes

• An experiment is a "draft" if start_date is NULL
• Each experiment requires an associated feature flag
• The feature flag controls variant assignment

The team taxonomy query automatically excludes events that are not useful for analytics. These are events marked as system or ignored_in_assistant in PostHog's core taxonomy definitions.

Skipped events

Event | Reason $pageleave | Confuses LLMs — use $pageview instead $autocapture | Only useful with autocapture-specific filters $$heatmap | Internal heatmap data, doesn't contribute to event counts $copy_autocapture | Clipboard capture, too niche $set | Person property setting event, not an analytics event $opt_in | Analytics opt-in event, irrelevant for product analytics $feature_flag_called | Feature flag evaluation, not a user action $feature_view | posthog-js/react specific, niche $feature_interaction | posthog-js/react specific, niche $capture_metrics | Internal SDK metrics $create_alias | Identity management event $merge_dangerously | Identity management event $groupidentify | Group identification event

These events are filtered at the SQL level using a NOT IN clause, so they don't consume pagination slots.