Generated banner for Tools And Features Hogql
Generated logo for Tools And Features Hogql

Tools And Features Hogql

PostHogOfficialCreated by PostHog

  • Skill
  • Tool integration
  • Data and AI
  • MIT
  • No issues found

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

  1. Download the skill as a ZIP:

    https://autogineer.com/agent-skills/posthog/tools-and-features-hogql/download
  2. 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

references/sessions.md

Sessions - Docs

A session is a set of events that try to capture a single use of your product or visit to your website. They are used in multiple areas of PostHog:

  • Session replays

  • Web analytics

  • Session aggregations

  • Session properties

Sessions enable you to analyze how exactly users are using your product, their entry and exit points, how long they spend on your site, how active they are, bounce rate, and more.

How does PostHog define a session?

Our JavaScript Web library and mobile SDKs (Android, iOS, React Native, and Flutter) add a $session_id property to each event. Events captured from the same user, browser, and device get the same $session_id within the same session, until a new session is started. A new session is started by default when either there's no activity for 30 minutes or the session has reached the maximum session duration of 24 hours. Subsequent events are grouped into a new session and a new session triggers a new session replay.

A session can span multiple tabs and windows, but only on the same browser and device. For example, if a user goes from one Chrome tab to another, it counts as a single session. If they go from Chrome to Firefox, a new session is created. You can also create a new session by calling posthog.reset() (which also starts a new session replay).

Activity is defined as any event sent from the client including autocapture events and replay activity like mouse movement. The inactivity period can be configured using the session_idle_timeout_seconds configuration option.

Note: If you are migrating data or using a CDP, you may need to manually set the $session_id property if you want to query sessions or connect them to session replays. If you are using the JavaScript Web SDK alongside your CDP, you can get this value by calling posthog.get_session_id().

Session properties

Session properties are data about the session that is autocaptured by PostHog and separate from event or person data. They can be queried and used like other properties in aggregations, filters, breakdowns, and more. Session properties include:

Session propertyDescription
$session_idUnique identifier for a session, persists across events in the same session
$start_timestampTimestamp when the session started
$end_timestampTimestamp when the session ended
$entry_current_urlURL of the page where the session started
$entry_pathnamePathname of the page where the session started
$exit_current_urlURL of the page where the session ended
$exit_pathnamePathname of the page where the session ended
$entry_utm_sourceUTM source of the session start page
$entry_utm_campaignUTM campaign of the session start page
$entry_utm_mediumUTM medium of the session start page
$entry_utm_termUTM term of the session start page
$entry_utm_contentUTM content of the session start page
$entry_referring_domainReferring domain of the session start page
$entry_gclidGoogle Click ID when the session started
$entry_gad_sourceGoogle Ads source when the session started
$pageview_countTotal number of pageviews during the session
$autocapture_countTotal number of autocapture events during the session
$channel_typeType of channel through which the session was initiated
$session_durationTotal duration of the session
$is_bounceIndicates if the session was a bounce (single pageview session)

You can query all of these from the sessions table using SQL insights.

Session entry properties on events

For ease of use, some session properties are also available as event properties. These properties are prefixed with $session_entry_ and are captured at the start of the session. They remain constant on all events for the duration of the session.

The following session entry properties are automatically captured:

Event propertyDescription
$session_entry_urlFull URL captured at the start of the session
$session_entry_hostHost captured at the start of the session
$session_entry_pathnamePathname captured at the start of the session
$session_entry_referrerReferrer captured at the start of the session

Additionally, UTM parameters and other attribution properties (like gclid) are also available as event properties with the $session_entry_ prefix. This means you can access these properties directly when querying events, without needing to join with the sessions table.

Using session data

The easiest way to analyze a session is to view a session replay. This enables you to playback and watch exactly what happened in that session. This includes events, navigation, mouse movement, console logs, and more.

Web analytics also gives you an easily accessible overview. The top section contains sessions, session duration, and bounce rate, while further down provides more details on entry paths, exit paths, top channels, device types, and more.

Image: Session data in web analyticsImage: Session data in web analytics

For more detailed analysis, you can use insights to filter, break down, and aggregate session data.

Session aggregation

You can aggregate trends by Unique sessions just as would Unique users or Weekly active users.

This provides the count of unique sessions where the specified event occurred. This can be helpful to remove the noise if you have specific events that are sent a bunch of times within a single session.

To do this, select Unique sessions from the aggregation dropdown.

Image: Session countsImage: Session counts

This can then be used in a formula to understand things like the average number of sessions per user or the average number of events per session.

Image: Session formulaImage: Session formula

Filtering by session properties

You can use any of the session properties to filter your data. For example, to remove sessions that are shorter than 10 seconds, you can filter for sessions where duration is greater than 10 seconds.

Image: Session duration filterImage: Session duration filter

Plotting session properties

You can plot session properties over time like you would with an event count. To do this, select one of the property aggregators (e.g. median, 90th percentile, etc.) and then select the session property to plot. For example, to plot the median session duration, select Property value median and then select Session duration as the property.

Image: Session median durationImage: Session median duration

Breaking down by session properties

You can view the distribution of different session property values by breaking down by that property.

This is useful for understanding entry URLs or UTMs. For example, to break down sessions by entry URL, click Property value and then select Entry URL.

Image: Entry URL breakdownImage: Entry URL breakdown

Server SDKs and sessions

Our server-side SDKs do not send $session_id by default. If you want to use sessions with server-side events, you can manually set the $session_id property. This lets you use session aggregations for these events and can be useful if you want to connect server-side events to session replays, exceptions, or web analytics sessions.

You can either pass the session ID from posthog-js into your back end, or generate a new session ID. You can then use this session ID in your server-side events.

If the events are logically part of the same session, e.g. a user starts a purchase on the front end and you receive confirmation on the back end, it's better to re-use the session ID from the front end. On the other hand, if the events have no corresponding front-end session, you can generate a new session ID.

Automatically sending session IDs

If you enable tracing headers within the JavaScript SDK, the session ID will be sent on every request. You can enable tracing headers by adding the __add_tracing_headers option to your posthog.init call:

TypeScript

PostHog AI

posthog.init('<ph_project_token>', {
    __add_tracing_headers: ['your-backend-domain.com']
})

If you are using the Python SDK with Django, you can add the middleware integration which correctly extracts the session ID and adds it to every captured event.

Passing Session IDs from client-side code into server-side events

Get the session ID from posthog-js with posthog.get_session_id() and send it to your server with custom code. Then, you can set the session ID on the server-side event with posthog.capture({ ..., '$session_id': session_id }).

Here's an example of how you might do this:

JavaScript

PostHog AI

// Client side code
// Get the session ID
const posthog_session_id = posthog.get_session_id()
// Make a request to your server. This is just an example, you should replace this with your own code.
fetch('https://your-api.example.com/purchase', {
    method: 'POST',
    body: '[your request body]', // replace with your own request body
    headers: {
        'Content-Type': 'application/json',
        'X-POSTHOG-SESSION-ID': posthog_session_id // include the posthog session ID in a header
    }
})

JavaScript

PostHog AI

// Server side code
// This is an example route handler using Express, the actual implementation will depend on your server framework and programming language
app.post('/purchase', (req, res) => {
    const { posthog_distinct_id } = handleUserAuthentication(req) // replace with your own user authentication logic
    const posthog_session_id = req.header('X-POSTHOG-SESSION-ID') // get the session ID from the header
    // Handle the purchase
    handlePurchase(req.body) // replace with your own business logic
    // Send a custom event, including the session ID
    posthog.capture({
        distinct_id: posthog_distinct_id,
        event: 'Purchase Succeeded',
        properties: {
            '$session_id': posthog_session_id
        }
    })
    res.send('OK')
})
Custom session IDs

Custom session IDs must meet the following requirements:

  • Must be consistent across events in the same session

  • Must not be reused by a different user or session

  • Must be a valid UUIDv7

  • The timestamp part of the UUIDv7 must be equal to or before the timestamp of the first event in the session

  • The timestamp part of the UUIDv7 plus 24 hours must be after the timestamp of the last event in the session

Any events with session IDs that do not meet these requirements are not included in session aggregations.

Our JavaScript Web library and mobile SDKs (Android, iOS, React Native, and Flutter) automatically create session IDs for you, but if you override them, you also must ensure they meet these requirements.

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
Also distributed in
PostHog/skills

AutoGineer is not affiliated with or endorsed by PostHog or PostHog.

Download ZIP

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