Setting the file. One moment.
Skill 103 · Querying PostHog Data
Subchapter 103.25
references/hogql-extensions.mdMarkdown6 KBView on GitHub
These functions are unique to HogQL and not available in standard ClickHouse.
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 descConverts 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 10Creates 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 10Filters events that match a named action. Actions are named event combinations defined in PostHog.
SELECT count()
FROM events
WHERE matchesAction('clicked homepage button')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 -- ChineseHogQL supports limited HTML tags for rich output in table visualizations. For security, no attributes are supported except for <a> tags.
<div>, <p>, <span>, <pre>, <code><em>, <strong>, <b>, <i>, <u><h1>, <h2>, <h3>, <h4>, <h5>, <h6><ul>, <ol>, <li><table>, <thead>, <tbody>, <tr>, <th>, <td><blockquote>, <hr>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'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 10Available models: 'text-embedding-3-small-1536', 'text-embedding-3-large-3072'.
Special tags for visual effects in table output.
Makes text blink.
SELECT <span>is this <blink>{event}</blink> real?</span> FROM eventsMakes text scroll horizontally.
SELECT <marquee>scrolling text!</marquee> FROM eventsHides text until hovered over.
SELECT <redacted>hidden until hover</redacted> FROM eventsSELECT
<span>is this <blink>{event}</blink> real?</span>,
<marquee>so real, yes!</marquee>,
<redacted>but this one is hidden</redacted>
FROM eventsThe three variants differ only in how the breakdown property column is typed.
7 arguments:
num_steps (Int) — total number of funnel stepsconversion_window_limit (Int) — max seconds between first and last stepbreakdown_attribution_type (String) — one of first_touch, last_touch, all_events, or step_Nfunnel_order_type (String) — ordered, unordered, or strictprop_vals (Array) — breakdown property values to aggregate overoptional_steps (Array(Int)) — 1-indexed step numbers marked as optionalevents_array (Array(Tuple)) — pre-sorted array of (timestamp, uuid, breakdown_prop, steps) tuples per personReturns an array of tuples: (step_reached, breakdown_value, timings, event_uuids, steps_bitmask).
8 arguments:
from_step (Int) — 1-indexed start step for conversion measurementto_step (Int) — 1-indexed goal step for conversion measurementnum_steps (Int) — total number of funnel stepsconversion_window_limit (Int) — max seconds between first and last stepbreakdown_attribution_type (String) — one of first_touch, last_touch, all_events, or step_Nfunnel_order_type (String) — ordered, unordered, or strictprop_vals (Array) — breakdown property values to aggregate overevents_array (Array(Tuple)) — pre-sorted array of (timestamp, interval_start, uuid, breakdown_prop, steps) tuples per personReturns an array of tuples: (interval_start, success_bool, breakdown_value, event_uuid).