tools-and-features-hogql

Included with Lifetime

$97 forever

HogQL queries for PostHog analytics

Data & Analytics

What this skill does


# 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
```sql
SELECT event, count()
FROM events
WHERE {filters}
GROUP BY event
ORDER BY count() DESC
```

### Property breakdowns
```sql
SELECT properties.$browser AS browser, count()
FROM events
WHERE event = '$pageview' AND {filters}
GROUP BY browser
```

### Person properties
```sql
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: 9

Size: 73.1 KB

Complexity: 54/100

Category: Data & Analytics

Source: https://github.com/posthog/skills/tree/main/skills/posthog/all/skills/tools-and-features-hogql

Related in Data & Analytics

clawarr-suite

Included

Comprehensive management for self-hosted media stacks (Sonarr, Radarr, Lidarr, Readarr, Prowlarr, Bazarr, Overseerr, Plex, Tautulli, SABnzbd, Recyclarr, Unpackerr, Notifiarr, Maintainerr, Kometa, FlareSolverr). Deep library exploration, analytics, dashboard generation, content management, request handling, subtitle management, indexer control, download monitoring, quality profile sync, library cleanup automation, notification routing, collection/overlay management, and media tracker integration (Trakt, Letterboxd, Simkl).

Data & Analyticsscripts

querying-soql

Included

SOQL query generation, optimization, and analysis with 100-point scoring. Use this skill when the user needs SOQL/SOSL authoring or optimization: natural-language-to-query generation, relationship queries, aggregates, query-plan analysis, and performance or safety improvements for Salesforce queries. TRIGGER when: user writes, optimizes, or debugs SOQL/SOSL queries, touches .soql files, or asks about relationship queries, aggregates, or query performance. DO NOT TRIGGER when: bulk data operations (use handling-sf-data), Apex DML logic (use generating-apex), or report/dashboard queries.

Data & Analyticsscripts

app-store-optimization

Included

App Store Optimization (ASO) toolkit for researching keywords, analyzing competitor rankings, generating metadata suggestions, and improving app visibility on Apple App Store and Google Play Store. Use when the user asks about ASO, app store rankings, app metadata, app titles and descriptions, app store listings, app visibility, or mobile app marketing on iOS or Android. Supports keyword research and scoring, competitor keyword analysis, metadata optimization, A/B test planning, launch checklists, and tracking ranking changes.

Data & Analyticsscripts

habit-flow

Included

AI-powered atomic habit tracker with natural language logging, streak tracking, smart reminders, and coaching. Use for creating habits, logging completions naturally ("I meditated today"), viewing progress, and getting personalized coaching.

Data & Analyticsscripts

app-store-optimization

Included

Data & Analyticsscripts

visualizing-data

Included

Builds dashboards, reports, and data-driven interfaces requiring charts, graphs, or visual analytics. Provides systematic framework for selecting appropriate visualizations based on data characteristics and analytical purpose. Includes 24+ visualization types organized by purpose (trends, comparisons, distributions, relationships, flows, hierarchies, geospatial), accessibility patterns (WCAG 2.1 AA compliance), colorblind-safe palettes, and performance optimization strategies. Use when creating visualizations, choosing chart types, displaying data graphically, or designing data interfaces.

Data & Analyticsscripts