How to Write Custom HogQL Queries for Analytics Data in PostHog

To write custom HogQL queries for PostHog analytics, use the Python AST API via parse_select and execute_hogql_query in posthog/hogql/query.py for type-safe construction, or send raw HogQL strings to the /api/projects/{id}/query endpoint with "kind": "HogQLQuery" for quick prototyping.

HogQL is PostHog's high-level query language that compiles to ClickHouse SQL, enabling direct analysis of event data. According to the PostHog/posthog source code, custom queries traverse three architectural layers: schema definition via Pydantic models, AST parsing in posthog/hogql/parser.py, and execution through execute_hogql_query in posthog/hogql/query.py.

Understanding the Three-Layer Architecture

PostHog's query engine separates concerns across distinct layers:

• Schema Layer (posthog/schema.py): Auto-generated Pydantic models derived from the TypeScript schema in frontend/src/queries/schema/schema-general.ts. Defines the JSON payload structure for the /query endpoint.
• Parser & AST Layer (posthog/hogql/parser.py): Converts raw HogQL strings into abstract syntax trees using parse_select and parse_expr, outputting nodes from posthog/hogql/ast.py.
• Executor Layer (posthog/hogql/query.py): The execute_hogql_query function traverses ASTs, resolves lazy joins (e.g., session.*, person.*), injects team-specific filters, and emits optimized ClickHouse SQL.

Method 1: Build Queries Using the AST API

For production code requiring type safety and automatic team-filter injection, construct queries programmatically using the AST builders.

from posthog.hogql import ast
from posthog.hogql.parser import parse_expr, parse_select
from posthog.hogql.query import execute_hogql_query

# Example: unique visitors per page for the last 7 days

num_days = 7

stmt = parse_select(
    """
    SELECT
        events.properties.$pathname AS page,
        uniq(person_id) AS unique_visitors
    FROM events
    WHERE {where}
    GROUP BY page
    """,
    {
        "where": parse_expr(
            "timestamp >= now() - interval {days} day",
            {"days": ast.Constant(value=num_days)},
        )
    },
)

result = execute_hogql_query(query=stmt, team=my_team, query_type="custom")
print(result.results)   # → list of rows

print(result.columns)   # → ['page', 'unique_visitors']

Key components of this approach:

• parse_select: Parses full SELECT statements and supports placeholder substitution using {parameter} syntax.
• parse_expr: Parses sub-expressions (like date filters) into AST nodes that can be safely injected.
• Lazy joins: Accessing events.person_id or session.* automatically resolves optimized argMax joins without manual JOIN clauses.
• Team isolation: The executor automatically injects team_id filters based on the team parameter.

Method 2: Execute Raw HogQL Strings via API

For ad-hoc analysis or external integrations, pass HogQL strings directly to the REST API endpoint.

curl -X POST https://app.posthog.com/api/projects/123/query \
  -H "Authorization: Bearer $POSTHOG_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
        "query": {
          "kind": "HogQLQuery",
          "query": "SELECT uniq(person_id) AS daily_users, countIf(event = '\''$pageview'\'') AS pageviews FROM events WHERE timestamp >= toDateTime('\''2025-10-01 00:00:00'\'') AND timestamp <= toDateTime('\''2025-10-31 23:59:59'\'') GROUP BY toStartOfDay(timestamp) LIMIT 10000"
        }
      }'

Important details:

• The kind field must be set to "HogQLQuery" to trigger the HogQL parser rather than higher-level query objects.
• The response includes results, columns, and the generated ClickHouse SQL in the hogql field for debugging.
• Set "async": true for long-running queries to receive a client_query_id for status polling.

Debugging Generated ClickHouse SQL

Verify query optimization by inspecting the compiled SQL before execution.

from posthog.hogql.printer import print_ast
from posthog.hogql.context import HogQLContext

# Build AST

from posthog.hogql import ast
stmt = ast.SelectQuery(
    select=[ast.Field(chain=["event"]), ast.Field(chain=["timestamp"])],
    select_from=ast.JoinExpr(table=ast.Field(chain=["events"])),
    limit=ast.Constant(value=10),
)

# Render to HogQL/ClickHouse

ctx = HogQLContext(team_id=my_team.pk, enable_select_queries=True)
print("Generated HogQL:", print_ast(stmt, context=ctx, dialect="hogql"))

For UI-based debugging, use the SQL Editor at /project/:project_id/hogql in the PostHog interface, which uses the same execution engine as the API.

Performance Optimization Techniques

Follow these patterns when writing custom HogQL queries to ensure efficient ClickHouse execution:

• Scope date ranges explicitly: ClickHouse performs partition pruning by timestamp. Always include timestamp >= toDateTime(...) and timestamp <= toDateTime(...) bounds.
• Leverage lazy tables: Access session.* and person.* properties directly; the executor automatically generates optimized argMax joins. Manual joins bypass these optimizations.
• Limit result sets: Use LIMIT clauses (default 50,000 rows) to prevent memory issues.
• Use async execution: For queries scanning large time ranges, include "async": true in the API payload to avoid gateway timeouts.

Adapting Built-in Analytics Queries

To customize existing web analytics queries, reference the snapshot files containing their exact HogQL implementations:

posthog/hogql_queries/web_analytics/test/__snapshots__/test_sample_web_analytics_queries.hogql.ambr

These snapshots contain the generated HogQL for query types like WebStatsTableQuery. Copy the query into the SQL Editor, modify filters or aggregations, and use the result as a template for your HogQLQuery payloads.

Summary

• AST construction via parse_select and execute_hogql_query provides type-safe queries with automatic team filtering and lazy join resolution.
• Raw string execution through the /query endpoint with "kind": "HogQLQuery" enables rapid prototyping and external integrations.
• Lazy joins for person and session tables eliminate the need for manual JOIN syntax while maintaining query performance.
• Debugging tools like print_ast and the SQL Editor reveal the exact ClickHouse SQL generated from your HogQL.
• Performance depends on explicit date range filtering, lazy table usage, and appropriate LIMIT clauses.

Frequently Asked Questions

What is the difference between HogQL and ClickHouse SQL?

HogQL is a higher-level abstraction that compiles to ClickHouse SQL. According to the PostHog source code, HogQL provides lazy joins (automatic argMax resolution for person/session data), team-scoped security filters, and simplified syntax, while ClickHouse SQL is the raw database query language executed on the cluster.

How do I handle parameterized queries safely in HogQL?

Use the parse_select and parse_expr functions with placeholder dictionaries. Pass parameters as AST nodes (e.g., ast.Constant(value=7)) rather than string concatenation. This prevents injection attacks and ensures proper type handling during AST compilation in posthog/hogql/parser.py.

Where does HogQL resolve table schemas and field mappings?

Table definitions and lazy join configurations reside in posthog/hogql/database.py. This file centralizes the HogQL schema, defining available tables (like events), their fields, and how properties like person_id resolve to underlying ClickHouse structures via argMax aggregations.

Can I execute HogQL queries asynchronously for large datasets?

Yes. Add "async": true to your JSON payload when posting to /api/projects/{id}/query. This returns a client_query_id immediately, allowing you to poll for results later without risking gateway timeouts on long-running aggregations.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}