AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

tools-and-features-hogql

HogQL queries for PostHog analytics

Original source
posthog
License
MIT
Pinned revision
cac7c80a9e5b73484d18eda1bffb47297c938e0c

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read posthog--tools-and-features-hogql at revision cac7c80a9e5b73484d18eda1bffb47297c938e0c, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 9

SKILL.md

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