AutoGineer
Generated banner for Tools And Features Hogql
Generated logo for Tools And Features Hogql

Tools And Features Hogql

PostHogOfficialCreated by PostHog

  • Skill
  • Tool integration
  • Data and AI
  • MIT
  • No issues found
Download ZIP

From the author

HogQL queries for PostHog analytics
— the description by PostHog

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

HogQL is PostHog's SQL dialect, designed as a wrapper around ClickHouse SQL to simplify data access and analytics within the PostHog platform. This skill provides the essential reference material for constructing queries, including how to access event and person properties, utilize PostHog-specific functions, and integrate UI-based filters into your SQL insights. It emphasizes performance and efficiency, offering guidelines on aggregation, time-range filtering, and query structure to help users build effective analytics dashboards.

What it does

  • Provides syntax patterns for querying events, persons, and sessions.
  • Explains how to access event and person properties using specific dot notation.
  • Details the use of the {filters} placeholder for dynamic dashboard filtering.
  • Lists supported ClickHouse functions and aggregation methods for data analysis.
  • Offers performance optimization tips like using uniq() over count(distinct).
  • Provides guidance on using cohort and action-based filtering in SQL.

When to use it

  • When you need to create custom SQL insights in PostHog.
  • When you want to perform complex data analysis beyond standard UI-based reports.
  • When you need to optimize existing queries for better performance.
  • When you are building dashboards that require dynamic date ranges or user-defined filters.

Advantages

  • Simplifies complex ClickHouse SQL for common analytics tasks.
  • Enables seamless integration with PostHog's UI-based filtering system.
  • Provides access to specialized PostHog functions like windowFunnel and sequenceMatch.
  • Promotes efficient query patterns to reduce load on the underlying database.

Usage examples

“Write a HogQL query to count pageviews by browser.”
SELECT properties.$browser AS browser, count() FROM events WHERE event = '$pageview' AND {filters} GROUP BY browser
“How do I count unique users in a HogQL query?”
Use the uniq() function instead of count(distinct) for better performance, for example: SELECT uniq(person_id) FROM events WHERE {filters}

Install

  1. Download the skill as a ZIP:

    https://mp-staging.autogineer.com/agent-skills/posthog/tools-and-features-hogql/download

  2. Unzip it into the cross-client skills folder of your project:

    mkdir -p .agents/skills && unzip tools-and-features-hogql.zip -d .agents/skills

Documentation by PostHog

HogQL queries for PostHog

This skill helps you write HogQL queries for PostHog analytics. HogQL is PostHog's SQL dialect, a wrapper around ClickHouse SQL with simplified property access and PostHog-specific functions.

Reference files

  • references/expressions.md - Sql expressions - docs

  • references/aggregations.md - Supported aggregations - docs

  • references/clickhouse-functions.md - Supported clickhouse functions - docs

  • references/data-access.md - Accessing data using sql - docs

  • references/variables.md - Sql variables - docs

  • references/useful-functions.md - Useful sql functions - docs

  • references/posthog.md - PostHog table schemas (events, persons, groups, sessions)

  • references/sessions.md - Sessions - docs

Consult the documentation for SQL syntax, available functions, and query patterns.

Key principles

  • Property access: Use properties.$property_name for event properties and person.properties.$property_name for person properties

  • Null handling: HogQL has simplified null handling compared to raw ClickHouse SQL

  • Filters placeholder: Use {filters} in queries to allow UI-based filtering in PostHog dashboards

  • Aggregations: Prefer ClickHouse aggregation functions like count(), uniq(), avg(), sum()

Common patterns

Event queries
SELECT event, count()
FROM events
WHERE {filters}
GROUP BY event
ORDER BY count() DESC
Property breakdowns
SELECT properties.$browser AS browser, count()
FROM events
WHERE event = '$pageview' AND {filters}
GROUP BY browser
Person properties
SELECT person.properties.email, count()
FROM events
WHERE {filters}
GROUP BY person.properties.email

Framework guidelines

  • Use properties.$name syntax for event properties, person.properties.$name for person properties

  • Use bracket notation for special characters like properties['$feature/cool-flag']

  • For cohorts, filter with person_id IN COHORT 'cohort-name'

  • For actions, use matchesAction('action-name') in WHERE clauses

  • Include {filters} placeholder in WHERE clauses to enable UI-based filtering in dashboards

  • Use {variables.name} for reusable SQL variables across dashboards

  • Access dashboard date range with {filters.dateRange.from} and {filters.dateRange.to}

  • ALWAYS include a time range filter - shorter is faster (e.g., timestamp >= now() - INTERVAL 7 DAY)

  • Prefer uniq() over count(distinct) for counting unique values - it's more efficient

  • Don't scan the same table multiple times - use materialized views for reusable subsets

  • Use timestamp-based pagination instead of OFFSET for large datasets

  • Name queries descriptively for easier debugging in query_log

  • Use dateTrunc() for time-based grouping (e.g., dateTrunc('day', timestamp))

  • For funnel queries, use windowFunnel() or sequenceMatch() functions

  • Test queries in the PostHog SQL editor before using them in insights or the API

Files

references/clickhouse-functions.md

Supported ClickHouse functions - Docs

This is an ever-expanding list of enabled ClickHouse functions.

You can find their full definitions in the ClickHouse documentation. Additionally, we include a list of popular ones and their uses in the HogQL expressions and SQL insight documentation.

Most of the type conversion functions are safer versions of the equivalent ClickHouse functions to avoid NULL/zero mistakes. If the function's name includes a leading underscore, then it's not safe and simply calls the underlying ClickHouse function.

Arithmetic

  • plus

  • minus

  • multiply

  • divide

  • intDiv

  • intDivOrZero

  • modulo

  • moduloOrZero

  • positiveModulo

  • negate

  • abs

  • gcd

  • lcm

  • max2

  • min2

  • multiplyDecimal

  • divideDecimal

Arrays and strings in common

  • empty

  • notEmpty

  • length

  • reverse

  • in

  • notIn

Arrays

  • array

  • range

  • arrayConcat

  • arrayElement

  • has

  • hasAll

  • hasAny

  • hasSubstr

  • indexOf

  • arrayCount

  • countEqual

  • arrayEnumerate

  • arrayEnumerateUniq

  • arrayPopBack

  • arrayPopFront

  • arrayPushBack

  • arrayPushFront

  • arrayResize

  • arraySlice

  • arraySort

  • arrayReverseSort

  • arrayUniq

  • arrayJoin

  • arrayDifference

  • arrayDistinct

  • arrayEnumerateDense

  • arrayIntersect

  • arrayReduce

  • arrayReverse

  • arrayFilter

  • arrayFlatten

  • arrayCompact

  • arrayZip

  • arrayAUC

  • arrayMap

  • arrayFill

  • arrayFold

  • arrayWithConstant

  • arraySplit

  • arrayReverseFill

  • arrayReverseSplit

  • arrayRotateLeft

  • arrayRotateRight

  • arrayExists

  • arrayAll

  • arrayFirst

  • arrayLast

  • arrayFirstIndex

  • arrayLastIndex

  • arrayMin

  • arrayMax

  • arraySum

  • arrayAvg

  • arrayCumSum

  • arrayCumSumNonNegative

  • arrayProduct

  • arrayStringConcat

  • generateSeries

Comparison

  • equals

  • notEquals

  • less

  • greater

  • lessOrEquals

  • greaterOrEquals

Logical

  • and

  • or

  • xor

  • not

Type conversions

  • hex

  • unhex

  • toInt

  • toIntOrZero

  • _toInt8

  • _toInt16

  • _toInt32

  • _toInt64

  • _toUInt64

  • _toUInt128

  • toFloat

  • toFloatOrZero

  • toFloatOrDefault

  • toDecimal

  • toUUID

  • toString

  • toBool

  • toJSONString

  • toTypeName

  • parseDateTime

  • parseDateTimeBestEffort

  • cityHash64

  • UUIDv7ToDateTime

Dates and times

  • toDate

  • toDateTime

  • toDateTime64

  • toDateTimeUS

  • _toDate

  • toTimeZone

  • timeZoneOf

  • timeZoneOffset

  • toYear

  • toQuarter

  • toMonth

  • toDayOfYear

  • toDayOfMonth

  • toDayOfWeek 

  • toHour

  • toMinute

  • toSecond

  • toUnixTimestamp

  • toUnixTimestamp64Milli

  • toStartOfInterval

  • toStartOfYear

  • toStartOfISOYear

  • toStartOfQuarter

  • toStartOfMonth

  • toLastDayOfMonth

  • toMonday

  • toStartOfWeek

  • toLastDayOfWeek

  • toStartOfDay

  • toStartOfHour

  • toStartOfMinute

  • toStartOfSecond

  • toStartOfFiveMinutes

  • toStartOfTenMinutes

  • toStartOfFifteenMinutes

  • toTime

  • toISOYear

  • toISOWeek

  • toWeek

  • toYearWeek

  • age

  • dateDiff

  • dateTrunc

  • dateAdd

  • dateSub

  • timeStampAdd

  • timeStampSub

  • now

  • NOW

  • nowInBlock

  • current_timestamp

  • current_date

  • today

  • yesterday

  • timeSlot

  • toYYYYMM

  • toYYYYMMDD

  • toYYYYMMDDhhmmss

  • addYears

  • addMonths

  • addWeeks

  • addDays

  • addHours

  • addMinutes

  • addSeconds

  • addQuarters

  • subtractYears

  • subtractMonths

  • subtractWeeks

  • subtractDays

  • subtractHours

  • subtractMinutes

  • subtractSeconds

  • subtractQuarters

  • timeSlots

  • formatDateTime

  • dateName

  • monthName

  • fromUnixTimestamp

  • toModifiedJulianDay

  • fromModifiedJulianDay

  • toIntervalSecond

  • toIntervalMinute

  • toIntervalHour

  • toIntervalDay

  • toIntervalWeek

  • toIntervalMonth

  • toIntervalQuarter

  • toIntervalYear

Postgres-style date functions

  • date_part

  • date_trunc

  • make_timestamp

  • date_bin

  • date_add

  • date_subtract

  • date_diff

  • make_interval

  • make_timestampz

Strings

  • left – operates on UTF-8 characters, not bytes (maps to ClickHouse's leftUTF8)

  • right – operates on UTF-8 characters, not bytes (maps to ClickHouse's rightUTF8)

  • lengthUTF8

  • leftPad or lpad

  • rightPad or rpad

  • leftPadUTF8

  • rightPadUTF8

  • lower

  • upper

  • lowerUTF8

  • upperUTF8

  • isValidUTF8

  • toValidUTF8

  • format

  • reverseUTF8

  • concat

  • substring

  • substringUTF8

  • appendTrailingCharIfAbsent

  • convertCharset

  • base58Encode

  • base58Decode

  • tryBase58Decode

  • base64Encode

  • base64Decode

  • tryBase64Decode

  • endsWith

  • startsWith

  • trim or btrim

  • trimLeft or ltrim

  • trimRight or rtrim

  • encodeXMLComponent

  • decodeXMLComponent

  • extractTextFromHTML

  • ascii

  • concatWithSeparator

  • repeat

  • initcap

Searching in strings

  • position

  • positionCaseInsensitive

  • positionUTF8

  • positionCaseInsensitiveUTF8

  • multiSearchAllPositions

  • multiSearchAllPositionsCaseInsensitive

  • multiSearchAllPositionsCaseInsensitiveUTF8

  • multiSearchAllPositionsUTF8

  • multiSearchFirstPosition

  • multiSearchFirstPositionCaseInsensitive

  • multiSearchFirstPositionCaseInsensitiveUTF8

  • multiSearchFirstPositionUTF8

  • multiSearchFirstIndex

  • multiSearchFirstIndexCaseInsensitive

  • multiSearchFirstIndexCaseInsensitiveUTF8

  • multiSearchFirstIndexUTF8

  • multiSearchAny

  • multiSearchAnyCaseInsensitive

  • multiSearchAnyCaseInsensitiveUTF8

  • multiSearchAnyUTF8

  • match

  • multiMatchAny

  • multiMatchAnyIndex

  • multiMatchAllIndices

  • multiFuzzyMatchAny

  • multiFuzzyMatchAnyIndex

  • multiFuzzyMatchAllIndices

  • extract

  • extractAll

  • extractAllGroupsHorizontal

  • extractAllGroupsVertical

  • extractGroups

  • like

  • ilike

  • notLike

  • notILike

  • locate

  • ngramDistance

  • ngramDistanceCaseInsensitive

  • ngramDistanceCaseInsensitiveUTF8

  • ngramDistanceUTF8

  • ngramSearch

  • ngramSearchCaseInsensitive

  • ngramSearchCaseInsensitiveUTF8

  • ngramSearchUTF8

  • countSubstrings

  • countSubstringsCaseInsensitive

  • countSubstringsCaseInsensitiveUTF8

  • countMatches

  • countMatchesCaseInsensitive

  • hasSubsequence

  • hasSubsequenceCaseInsensitive

  • hasSubsequenceCaseInsensitiveUTF8

  • hasSubsequenceUTF8

  • hasToken

  • hasTokenCaseInsensitive

  • hasTokenCaseInsensitiveOrNull

  • hasTokenOrNull

  • hasAllTokens

  • hasAnyTokens

  • regexpExtract

Replacing in strings

  • replace

  • replaceAll

  • replaceOne

  • replaceRegexpAll

  • replaceRegexpOne

  • regexpQuoteMeta

  • translate

  • translateUTF8

Conditional

  • if

  • multiIf

Mathematical

  • e

  • pi

  • exp

  • log

  • ln

  • exp2

  • log2

  • exp10

  • log10

  • sqrt

  • cbrt

  • erf

  • erfc

  • lgamma

  • tgamma

  • sin

  • cos

  • tan

  • asin

  • acos

  • atan

  • pow

  • power

  • intExp2

  • intExp10

  • cosh

  • acosh

  • sinh

  • asinh

  • atanh

  • atan2

  • hypot

  • log1p

  • sign

  • degrees

  • radians

  • factorial

  • width_bucket

Rounding

  • floor

  • ceil

  • trunc

  • round

  • roundBankers

  • roundToExp2

  • roundDuration

  • roundAge

  • roundDown

Maps

  • map

  • mapFromArrays

  • mapAdd

  • mapSubtract

  • mapPopulateSeries

  • mapContains

  • mapKeys

  • mapValues

  • mapContainsKeyLike

  • mapExtractKeyLike

  • mapApply

  • mapFilter

  • mapUpdate

Splitting strings

  • splitByChar

  • splitByString

  • splitByRegexp

  • splitByWhitespace

  • splitByNonAlpha

  • alphaTokens

  • extractAllGroups

  • ngrams

  • tokens

Bit

  • bitAnd

  • bitOr

  • bitXor

  • bitNot

  • bitShiftLeft

  • bitShiftRight

  • bitRotateLeft

  • bitRotateRight

  • bitSlice

  • bitTest

  • bitTestAll

  • bitTestAny

  • bitCount

  • bitHammingDistance

Bitmap

  • bitmapBuild

  • bitmapToArray

  • bitmapSubsetInRange

  • bitmapSubsetLimit

  • subBitmap

  • bitmapContains

  • bitmapHasAny

  • bitmapHasAll

  • bitmapCardinality

  • bitmapMin

  • bitmapMax

  • bitmapTransform

  • bitmapAnd

  • bitmapOr

  • bitmapXor

  • bitmapAndnot

  • bitmapAndCardinality

  • bitmapOrCardinality

  • bitmapXorCardinality

  • bitmapAndnotCardinality

URLs

  • protocol

  • domain

  • domainWithoutWWW

  • topLevelDomain

  • firstSignificantSubdomain

  • cutToFirstSignificantSubdomain

  • cutToFirstSignificantSubdomainWithWWW

  • port

  • path

  • pathFull

  • queryString

  • fragment

  • queryStringAndFragment

  • extractURLParameter

  • extractURLParameters

  • extractURLParameterNames

  • URLHierarchy

  • URLPathHierarchy

  • encodeURLComponent

  • decodeURLComponent

  • encodeURLFormComponent

  • decodeURLFormComponent

  • netloc

  • cutWWW

  • cutQueryString

  • cutFragment

  • cutQueryStringAndFragment

  • cutURLParameter

JSON

  • isValidJSON

  • JSONHas

  • JSONLength

  • JSONArrayLength

  • JSONType

  • JSONExtractUInt

  • JSONExtractInt

  • JSONExtractFloat

  • JSONExtractBool

  • JSONExtractString

  • JSONExtractKey

  • JSONExtractKeys

  • JSONExtractRaw

  • JSONExtractArrayRaw

  • JSONExtractKeysAndValuesRaw

  • JSON_VALUE

Geo

  • greatCircleDistance

  • geoDistance

  • greatCircleAngle

  • pointInEllipses

  • pointInPolygon

  • geohashDecode

  • geohashesInBox

  • h3IsValid

  • h3GetResolution

  • h3GetBaseCell

  • h3EdgeAngle

  • h3EdgeLengthM

  • h3EdgeLengthKm

  • geoToH3

  • h3ToGeo

  • h3ToGeoBoundary

  • h3kRing

  • h3HexAreaM2

  • h3HexAreaKm2

  • h3IndexesAreNeighbors

  • h3ToChildren

  • h3ToParent

  • h3ToString

  • stringToH3

  • h3IsResClassIII

  • h3IsPentagon

  • h3GetFaces

  • h3CellAreaM2

  • h3CellAreaRads2

  • h3ToCenterChild

  • h3ExactEdgeLengthM

  • h3ExactEdgeLengthKm

  • h3ExactEdgeLengthRads

  • h3NumHexagons

  • h3PointDistM

  • h3PointDistKm

  • h3PointDistRads

  • h3GetRes0Indexes

  • h3GetPentagonIndexes

  • h3Line

  • h3Distance

  • h3HexRing

  • h3GetUnidirectionalEdge

  • h3UnidirectionalEdgeIsValid

  • h3GetOriginIndexFromUnidirectionalEdge

  • h3GetDestinationIndexFromUnidirectionalEdge

  • h3GetIndexesFromUnidirectionalEdge

  • h3GetUnidirectionalEdgesFromHexagon

  • h3GetUnidirectionalEdgeBoundary

Nullable

  • isNull

  • isNotNull

  • coalesce

  • ifNull

  • nullIf

  • assumeNotNull

  • toNullable

Tuples

  • tuple

  • tupleElement

  • untuple

  • tupleHammingDistance

  • tupleToNameValuePairs

  • tuplePlus

  • tupleMinus

  • tupleMultiply

  • tupleDivide

  • tupleNegate

  • tupleMultiplyByNumber

  • tupleDivideByNumber

  • dotProduct

Time window

  • tumble

  • hop

  • tumbleStart

  • tumbleEnd

  • hopStart

  • hopEnd

Distance window

  • L1Norm

  • L2Norm

  • LinfNorm

  • LpNorm

  • L1Distance

  • L2Distance

  • LinfDistance

  • LpDistance

  • L1Normalize

  • L2Normalize

  • LinfNormalize

  • LpNormalize

  • cosineDistance

Window functions

  • lag or lagInFrame

  • lead or leadInFrame

  • rank

  • dense_rank

  • row_number

  • first_value

  • last_value

  • nth_value

Other

  • isFinite

  • isInfinite

  • ifNotFinite

  • isNaN

  • bar

  • transform

  • formatReadableDecimalSize

  • formatReadableSize

  • formatReadableQuantity

  • formatReadableTimeDelta

Community questions

Ask a question

Was this page useful?

HelpfulCould be better

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Created by
PostHog
Publisher
PostHog (Official)
Source
PostHog/skills · skills/posthog/tools-and-features/skills/hogql · commit a5d30e7
Licence
MIT License (redistribution allowed)
  • LICENSE (added by AutoGineer from LICENSE)
  • ATTRIBUTION.md (written by AutoGineer)
Synced
Also distributed in
PostHog/skills

AutoGineer is not affiliated with or endorsed by PostHog.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download tools-and-features-hogql.zip