# How to Write Custom HogQL Queries for Analytics Data in PostHog

> Learn how to write custom HogQL queries for PostHog analytics. Use the Python AST API for type-safe construction or send raw HogQL strings for rapid prototyping.

- Repository: [PostHog/posthog](https://github.com/PostHog/posthog)
- Tags: how-to-guide
- Published: 2026-04-25

---

**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`](https://github.com/PostHog/posthog/blob/main/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`](https://github.com/PostHog/posthog/blob/main/posthog/hogql/parser.py), and execution through `execute_hogql_query` in [`posthog/hogql/query.py`](https://github.com/PostHog/posthog/blob/main/posthog/hogql/query.py).

## Understanding the Three-Layer Architecture

PostHog's query engine separates concerns across distinct layers:

- **Schema Layer** ([`posthog/schema.py`](https://github.com/PostHog/posthog/blob/main/posthog/schema.py)): Auto-generated Pydantic models derived from the TypeScript schema in [`frontend/src/queries/schema/schema-general.ts`](https://github.com/PostHog/posthog/blob/main/frontend/src/queries/schema/schema-general.ts). Defines the JSON payload structure for the `/query` endpoint.
- **Parser & AST Layer** ([`posthog/hogql/parser.py`](https://github.com/PostHog/posthog/blob/main/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`](https://github.com/PostHog/posthog/blob/main/posthog/hogql/ast.py).
- **Executor Layer** ([`posthog/hogql/query.py`](https://github.com/PostHog/posthog/blob/main/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.

```python
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.

```bash
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.

```python
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`](https://github.com/PostHog/posthog/blob/main/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`](https://github.com/PostHog/posthog/blob/main/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.