Setting the file. One moment.
Skill 103 · Querying PostHog Data
Subchapter 103.11
references/example-paths.mdMarkdown5 KBView on GitHub
SELECT
last_path_key AS source_event,
path_key AS target_event,
COUNT(*) AS event_count,
avg(conversion_time) AS average_conversion_time
FROM
(SELECT
person_id,
path,
conversion_time,
event_in_session_index,
concat(toString(event_in_session_index), '_', path) AS path_key,
if(greater(event_in_session_index, 1), concat(toString(minus(event_in_session_index, 1)), '_', prev_path), NULL) AS last_path_key,
path_dropoff_key
FROM
(SELECT
person_id,
joined_path_tuple.1 AS path,
joined_path_tuple.2 AS conversion_time,
joined_path_tuple.3 AS prev_path,
event_in_session_index,
session_index,
arrayPopFront(arrayPushBack(path_basic, '')) AS path_basic_0,
arrayMap((x, y) -> if(equals(x, y), 0, 1), path_basic, path_basic_0) AS mapping,
arrayFilter((x, y) -> y, time, mapping) AS timings,
arrayFilter((x, y) -> y, path_basic, mapping) AS compact_path,
indexOf(compact_path, NULL) AS target_index,
if(greater(target_index, 0), arraySlice(compact_path, target_index), compact_path) AS filtered_path,
arraySlice(filtered_path, 1, 3) AS limited_path,
if(greater(target_index, 0), arraySlice(timings, target_index), timings) AS filtered_timings,
arraySlice(filtered_timings, 1, 3) AS limited_timings,
arrayDifference(limited_timings) AS timings_diff,
concat(toString(length(limited_path)), '_', limited_path[-1]) AS path_dropoff_key,
arrayZip(limited_path, timings_diff, arrayPopBack(arrayPushFront(limited_path, ''))) AS limited_path_timings
FROM
(SELECT
person_id,
path_time_tuple.1 AS path_basic,
path_time_tuple.2 AS time,
session_index,
arrayZip(path_list, timing_list, arrayDifference(timing_list)) AS paths_tuple,
arraySplit(x -> if(less(x.3, 1800), 0, 1), paths_tuple) AS session_paths
FROM
(SELECT
person_id,
groupArray(timestamp) AS timing_list,
groupArray(path_item) AS path_list
FROM
(SELECT
events.timestamp,
events.person_id,
ifNull(if(equals(event, '$pageview'), replaceRegexpAll(ifNull(properties.$current_url, ''), '(.)/$', '\\1'), event), '') AS path_item_ungrouped,
replaceRegexpAll(path_item_ungrouped, '^https:\\/\\/[a-z-]+\\.posthog\\.com', 'https://<region>.posthog.com') AS path_item_0,
replaceRegexpAll(path_item_0, '\\/project\\/\\d+', '/project/<team_id>') AS path_item_1,
replaceRegexpAll(path_item_1, '\\/ai-observability\\/traces\\/[0-9a-f\\-]+', '/ai-observability/traces/<trace_id>') AS path_item_2,
replaceRegexpAll(path_item_2, '\\/ai-observability\\/sessions\\/[0-9a-f\\-]+', '/ai-observability/sessions/<session_id>') AS path_item_3,
replaceRegexpAll(path_item_3, '\\/ai-evals\\/evaluations\\/[0-9a-f\\-]+', '/ai-evals/evaluations/<evaluation_id>') AS path_item_4,
replaceRegexpAll(path_item_4, '\\/ai-evals\\/datasets\\/[0-9a-f\\-]+', '/ai-evals/datasets/<dataset_id>') AS path_item_cleaned,
NULL AS groupings,
multiMatchAnyIndex(path_item_cleaned, NULL) AS group_index,
(if(greater(group_index, 0), groupings[group_index], path_item_cleaned) AS path_item) AS path_item
FROM
events
WHERE
and(and(ilike(toString(properties.$pathname), '%ai-%'), notILike(toString(properties.$pathname), '%docs%')), and(greaterOrEquals(events.timestamp, toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1))), lessOrEquals(events.timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), equals(event, '$pageview'))
ORDER BY
events.person_id ASC,
events.timestamp ASC)
GROUP BY
person_id)
ARRAY JOIN session_paths AS path_time_tuple, arrayEnumerate(session_paths) AS session_index)
ARRAY JOIN limited_path_timings AS joined_path_tuple, arrayEnumerate(limited_path_timings) AS event_in_session_index))
WHERE
notEquals(source_event, NULL)
GROUP BY
source_event,
target_event
ORDER BY
event_count DESC,
source_event ASC,
target_event ASC
LIMIT 50Wildcard groups are a paid feature. Wildcard groups (pathGroupings) — glob-like patterns using * to collapse similar paths into a single node, also usable in exclusions — are part of “Advanced paths”, which is only available on paid plans. On the free plan these controls are hidden or disabled in the paths UI. If a user on the free plan asks to add wildcard groups (including in exclusions), explain that this requires upgrading to a paid plan rather than suggesting workarounds.