Tools And Features Hogql
PostHogOfficialCreated by PostHog
- Skill
- Tool integration
- Data and AI
- MIT
- No issues found
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://autogineer.com/agent-skills/posthog/tools-and-features-hogql/downloadUnzip 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 - docsreferences/aggregations.md- Supported aggregations - docsreferences/clickhouse-functions.md- Supported clickhouse functions - docsreferences/data-access.md- Accessing data using sql - docsreferences/variables.md- Sql variables - docsreferences/useful-functions.md- Useful sql functions - docsreferences/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_namefor event properties andperson.properties.$property_namefor person propertiesNull handling: HogQL has simplified null handling compared to raw ClickHouse SQL
Filters placeholder: Use
{filters}in queries to allow UI-based filtering in PostHog dashboardsAggregations: 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() DESCProperty breakdowns
SELECT properties.$browser AS browser, count()
FROM events
WHERE event = '$pageview' AND {filters}
GROUP BY browserPerson properties
SELECT person.properties.email, count()
FROM events
WHERE {filters}
GROUP BY person.properties.emailFramework 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/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_idproperty 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 callingposthog.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 property | Description |
|---|---|
| $session_id | Unique identifier for a session, persists across events in the same session |
| $start_timestamp | Timestamp when the session started |
| $end_timestamp | Timestamp when the session ended |
| $entry_current_url | URL of the page where the session started |
| $entry_pathname | Pathname of the page where the session started |
| $exit_current_url | URL of the page where the session ended |
| $exit_pathname | Pathname of the page where the session ended |
| $entry_utm_source | UTM source of the session start page |
| $entry_utm_campaign | UTM campaign of the session start page |
| $entry_utm_medium | UTM medium of the session start page |
| $entry_utm_term | UTM term of the session start page |
| $entry_utm_content | UTM content of the session start page |
| $entry_referring_domain | Referring domain of the session start page |
| $entry_gclid | Google Click ID when the session started |
| $entry_gad_source | Google Ads source when the session started |
| $pageview_count | Total number of pageviews during the session |
| $autocapture_count | Total number of autocapture events during the session |
| $channel_type | Type of channel through which the session was initiated |
| $session_duration | Total duration of the session |
| $is_bounce | Indicates 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 property | Description |
|---|---|
| $session_entry_url | Full URL captured at the start of the session |
| $session_entry_host | Host captured at the start of the session |
| $session_entry_pathname | Pathname captured at the start of the session |
| $session_entry_referrer | Referrer 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