Tools And Features Hogql

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