HogQL queries for PostHog

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

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