Tools And Features Hogql

PostHogOfficialCreated by PostHog

Skill
Tool integration
Data and AI
MIT
No issues found

Download ZIP

From the author

HogQL queries for PostHog analytics

— the description by PostHog

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

HogQL is PostHog's SQL dialect, designed as a wrapper around ClickHouse SQL to simplify data access and analytics within the PostHog platform. This skill provides the essential reference material for constructing queries, including how to access event and person properties, utilize PostHog-specific functions, and integrate UI-based filters into your SQL insights. It emphasizes performance and efficiency, offering guidelines on aggregation, time-range filtering, and query structure to help users build effective analytics dashboards.

What it does

Provides syntax patterns for querying events, persons, and sessions.
Explains how to access event and person properties using specific dot notation.
Details the use of the {filters} placeholder for dynamic dashboard filtering.
Lists supported ClickHouse functions and aggregation methods for data analysis.
Offers performance optimization tips like using uniq() over count(distinct).
Provides guidance on using cohort and action-based filtering in SQL.

When to use it

When you need to create custom SQL insights in PostHog.
When you want to perform complex data analysis beyond standard UI-based reports.
When you need to optimize existing queries for better performance.
When you are building dashboards that require dynamic date ranges or user-defined filters.

Advantages

Simplifies complex ClickHouse SQL for common analytics tasks.
Enables seamless integration with PostHog's UI-based filtering system.
Provides access to specialized PostHog functions like windowFunnel and sequenceMatch.
Promotes efficient query patterns to reduce load on the underlying database.

Usage examples

“Write a HogQL query to count pageviews by browser.”: SELECT properties.$browser AS browser, count() FROM events WHERE event = '$pageview' AND {filters} GROUP BY browser
“How do I count unique users in a HogQL query?”: Use the uniq() function instead of count(distinct) for better performance, for example: SELECT uniq(person_id) FROM events WHERE {filters}

Install

Download the skill as a ZIP:
https://mp-staging.autogineer.com/agent-skills/posthog/tools-and-features-hogql/download

Unzip it into the cross-client skills folder of your project:

mkdir -p .agents/skills && unzip tools-and-features-hogql.zip -d .agents/skills

Documentation by PostHog

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

Files

ATTRIBUTION.mdAdded by AutoGineer972 B
LICENSELicence file added by AutoGineer1.0 KiB
SKILL.md3.0 KiB
references/aggregations.md3.8 KiB
references/clickhouse-functions.md12.3 KiB
references/data-access.md4.4 KiB
references/expressions.md7.8 KiB
references/posthog.md13.3 KiB
references/sessions.md12.5 KiB
references/useful-functions.md13.1 KiB
references/variables.md2.8 KiB

references/posthog.md

Linking PostHog as a data warehouse source - Docs

Much of your PostHog data is available in the data warehouse by default. This includes data like events, persons, sessions, groups, and the query log.

Using our SDKs or API to capture events, identify users, create groups, and add properties adds data to these tables, that you can query using the SQL editor.

PostHog data warehouse tables

`events`

Events are the core unit of data in PostHog, so you'll likely have a lot of them. This also makes the events table one of the most useful.

Some of the columns in the events table are:

Column	Description
uuid	Unique ID of the event
event	Name of the event
properties	Object containing all properties of the event (stored as a String). You can see the default properties in our data docs.
timestamp	Time the event was captured (ISO 8601 format)
distinct_id	Unique identifier tying together events from the same person
session_id	Unique identifier for the session the event belongs to
person_id	Unique identifier for the person

event names and properties can be set to any value you want, so you might many potential values for each.

Some products have special event names and properties that can be useful to know. For example:

Autocaptured events are named $autocapture and have the elements_chain property.
LLM analytics captures events like $ai_generation, $ai_span, $ai_embedding with properties like $ai_model, $ai_input_tokens, and $ai_total_cost_usd.
Error tracking captures $exception events with properties like $exception_list and $exception_fingerprint both with a specific expected schema.

`persons`

The users behind your events are known as persons in PostHog.

Some of the columns in the persons table are:

Column	Description
id	UUID of the person. Relates to person_id in the events table.
created_at	Date and time when the person was first created (ISO 8601 format).
last_seen_at	Date and time of the person's most recent event (ISO 8601 format), rounded to the hour. See skipping last_seen_at updates.
properties	Object containing all properties associated with the person (stored as a String).
is_identified	Indicates if the person is identified (1) or anonymous (0).

Beyond storing demographic data about your users, the persons table is powerful as it can connect to many other tables. For example, the persons table can be queried through the events using the person field like SELECT person.properties.$initial_browser FROM events. It can also be joined to external sources to create extended person properties.

`groups`

Groups enable you to aggregate events based on an entity like an organization, project, or team if you have set up group analytics.

Some of the columns in the groups table are:

Column	Description
index	Numeric index of the group type. You can view the group types in the People tab. It is zero-based, so the first group type listed there is 0.
key	Unique string identifier for the group. Relates to one of the group keys on the events table, such as $group_0, depending on the index.
created_at	Date and time when the group was first created (ISO 8601 format).
updated_at	Date and time when the group was last updated (ISO 8601 format).
properties	Object containing all properties associated with the group (stored as a String).

`sessions`

If you are using PostHog's snippet or JavaScript Web SDK, PostHog automatically captures session data which you can query using the sessions table.

Unlike the other tables, the sessions table stores all its properties as separate columns. This means there are a lot more of them. Some of them include:

Column	Description
session_id	Unique identifier for the session. Relates to $session_id on the events table.
distinct_id	Unique identifier for the user (person). Relates to $distinct_id on the events table.
$start_timestamp	Timestamp when the session started
$end_timestamp	Timestamp when the session ended
$num_uniq_urls	Number of unique URLs visited in the session
$entry_current_url	URL of the page where the session started
$end_current_url	URL of the page where the session ended
$entry_referring_domain	Referring domain for the session's entry
$entry_utm_source	UTM source for the session's entry
$pageview_count	Number of pageviews in the session
$autocapture_count	Number of autocapture events in the session
$screen_count	Number of screen events in the session
$channel_type	Channel type for the session's entry (e.g., organic, paid)
$session_duration	Duration of the session in seconds
$is_bounce	Indicates if the session was a bounce (1 = bounce, 0 = not)
$last_external_click_url	Last external click URL in the session
$vitals_lcp	Largest Contentful Paint (LCP) web vital for the session
View additional session properties	See full list of session properties

The sessions table is heavily used in web analytics. It is also useful for marketing analytics and session-based metrics more broadly.

`query_log`

The query_log table provides access to query execution metadata and performance metrics. You can learn more about it in the query log data docs.

Like the sessions table, the query_log table stores all its properties as separate columns. Some of them include:

Field	Description
event_date	Date when the query was executed
event_time	Exact timestamp when query started
query_id	Unique identifier for the query
endpoint	API endpoint that triggered the query
query	The actual HogQL/SQL query that was executed
query_start_time	When query execution began
query_duration_ms	Query execution time in milliseconds
created_by	User ID who initiated the query
read_rows	Number of rows processed
read_bytes	Bytes read during execution
result_rows	Number of rows returned
result_bytes	Size of result set in bytes
memory_usage	Peak memory usage in bytes
exception_code	Exception code if the query failed
is_personal_api_key_request	Whether request used personal API key
api_key_mask	Masked API key used
cpu_microseconds	CPU time consumed (for computation, excluding waiting)
RealTimeMicroseconds	Wall clock time in microseconds
View additional query log fields	See full list of query log properties

The query_log table is the only table with data on you and your team's internal usage of PostHog, making it useful for security, governance, and performance optimization.

System tables

PostHog also provides access to system tables that contain metadata about your PostHog project. These tables are accessed using the system. prefix in your queries, like SELECT * FROM system.actions.

`system.actions`

The system.actions table contains action definitions created in your project.

Column	Description
id	Unique identifier for the action
team_id	Team ID the action belongs to
name	Name of the action
description	Description of the action
deleted	Whether the action is deleted (1 = yes, 0 = no)
created_at	When the action was created
updated_at	When the action was last updated
steps_json	JSON containing the action's step definitions

`system.activity_logs`

The system.activity_logs table tracks user and system actions across PostHog entities, providing an audit trail of changes to feature flags, insights, dashboards, experiments, and more.

Column	Description
id	Unique identifier for the activity log entry
team_id	Team ID the activity belongs to
activity	Action performed (e.g., created, updated, deleted)
item_id	ID of the entity being logged
scope	Entity type being logged (e.g., FeatureFlag, Insight)
detail	JSON containing structured details about the change
created_at	When the activity occurred

Common scopes include FeatureFlag, Insight, Dashboard, Experiment, Cohort, Survey, Notebook, Action, Person, Replay, and ErrorTrackingIssue.

Note: Only team-scoped activity logs are visible. Organization-level logs (like membership changes) are not included because they are not associated with a specific team.

`system.annotations`

The system.annotations table contains annotation data from your project. Annotations are timestamped notes used to mark product changes, incidents, or releases on charts.

Column	Description
id	Unique identifier for the annotation
team_id	Team ID the annotation belongs to
content	Text content of the annotation
scope	Scope of the annotation: project, organization, dashboard, or dashboard_item
creation_type	How the annotation was created: USR (user) or GIT (automated)
date_marker	Timestamp shown on charts (ISO 8601 format)
deleted	Whether the annotation is deleted (1 = yes, 0 = no)
dashboard_item_id	Linked insight ID (if scoped to an insight)
dashboard_id	Linked dashboard ID (if scoped to a dashboard)
created_by_id	User ID of the annotation creator
created_at	When the annotation was created
updated_at	When the annotation was last updated

`system.cohort_calculation_history`

The system.cohort_calculation_history table tracks cohort calculation runs, useful for monitoring cohort computation status and debugging.

Column	Description
id	Unique identifier for the calculation run
team_id	Team ID the cohort belongs to
cohort_id	ID of the cohort that was calculated
count	Number of users in the cohort after calculation
started_at	When the calculation started
finished_at	When the calculation finished
error_code	Error code if the calculation failed

`system.data_warehouse_tables`

The system.data_warehouse_tables table contains metadata about data warehouse tables in your project.

Column	Description
id	Unique identifier for the table
team_id	Team ID the table belongs to
name	Name of the data warehouse table
columns	JSON containing column definitions
row_count	Number of rows in the table
external_data_source_id	ID of the external data source
created_at	When the table was created
updated_at	When the table was last updated
deleted	Whether the table is deleted (1 = yes, 0 = no)
deleted_at	When the table was deleted

`system.error_tracking_issues`

The system.error_tracking_issues table contains error tracking issues from your project.

Column	Description
id	Unique identifier for the issue
team_id	Team ID the issue belongs to
created_at	When the issue was first created
status	Current status of the issue
name	Name of the issue
description	Description of the issue

`system.hog_flows`

The system.hog_flows table contains workflow definitions from your project.

Column	Description
id	Unique identifier for the workflow
team_id	Team ID the workflow belongs to
name	Name of the workflow
description	Description of the workflow
status	Current status of the workflow
version	Version number of the workflow
exit_condition	Exit condition for the workflow
trigger	JSON containing trigger configuration
edges	JSON containing workflow edges
actions	JSON containing workflow actions
created_at	When the workflow was created
updated_at	When the workflow was last updated

`system.notebooks`

The system.notebooks table contains notebook metadata from your project.

Column	Description
id	Unique identifier for the notebook
short_id	Short identifier for the notebook
team_id	Team ID the notebook belongs to
title	Title of the notebook
content	JSON containing the notebook's content
text_content	Plain text content of the notebook
deleted	Whether the notebook is deleted (1 = yes, 0 = no)
visibility	Visibility setting for the notebook
version	Version number of the notebook
created_at	When the notebook was created
last_modified_at	When the notebook was last modified

Community questions

Ask a question

Was this page useful?

HelpfulCould be better

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Created by

PostHog

Publisher

PostHog (Official)

Source

PostHog/skills · skills/posthog/tools-and-features/skills/hogql · commit a5d30e7

Licence

MIT License (redistribution allowed)

LICENSE (added by AutoGineer from LICENSE)
ATTRIBUTION.md (written by AutoGineer)

Synced

2026-11-08

Also distributed in

PostHog/skills

AutoGineer is not affiliated with or endorsed by PostHog.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download tools-and-features-hogql.zip

From the author

AutoGineer summary

What it does

When to use it

Advantages

Usage examples

Install

Documentation by PostHog

HogQL queries for PostHog

Reference files

Key principles

Common patterns

Event queries

Property breakdowns

Person properties

Framework guidelines

Files

references/posthog.md

Linking PostHog as a data warehouse source - Docs

PostHog data warehouse tables

events

persons

groups

sessions

query_log

System tables

system.actions

system.activity_logs

system.annotations

system.cohort_calculation_history

system.data_warehouse_tables

system.error_tracking_issues

system.hog_flows

system.notebooks

Community questions

Was this page useful?

Requirements and compatibility

Security

Attribution and licence

Related skills

Anndata

Liteparse

Usfiscaldata

Geopandas

Markitdown

Zarr Python

Download ZIP

`events`

`persons`

`groups`

`sessions`

`query_log`

`system.actions`

`system.activity_logs`

`system.annotations`

`system.cohort_calculation_history`

`system.data_warehouse_tables`

`system.error_tracking_issues`

`system.hog_flows`

`system.notebooks`