/querying-posthog-data

name: querying-posthog-data
description: >
  Explains how to choose typed queries or SQL for PostHog data.
  Read it before you write HogQL/SQL.
  Also read it before you call execute-sql against PostHog.
  Use it to find or aggregate PostHog entities.
  These entities include insights, dashboards, cohorts, feature flags, experiments,
  surveys, hog flows, warehouse data, and persons.
  Use it for trends, funnels, retention, lifecycle, paths, stickiness,
  web analytics, error tracking, logs, sessions, and LLM traces.
  Before you calculate a governed business or telemetry measure, check
  system.information_schema.metrics for an approved definition.
  Examples include MRR, activation, billable usage, active organizations, and failure rates.
  Use the approved definition before you derive a measure from raw events or use a typed domain tool.
  It also covers HogQL differences, system table schemas, functions, query examples, and schema discovery.

Querying data in PostHog

The [guidelines](./references/guidelines.md) explain SQL syntax and schema discovery. Read them when you choose `posthog:execute-sql`. You do not need them for typed queries.

Choose the query path

Choose the method from the requested calculation and output. Do not choose a method from the tool name. No method fits all tasks.

For governed measures, follow the semantic-layer workflow below before deriving a query. Reuse a matching approved metric or saved query when it defines the requested measure.

Typed query tools

Use a typed query when the task needs standard PostHog calculation rules or native insight controls:

• `posthog:query-trends` for native trends with series, breakdowns, formulas, and period comparisons.
• `posthog:query-funnel` for conversion rates, drop-off, and step completion.
• `posthog:query-retention` for users returning over time.
• `posthog:query-stickiness` for engagement frequency.
• `posthog:query-paths` for navigation flows.
• `posthog:query-lifecycle` for new, returning, resurrecting, and dormant users.

Do not approximate these analyses with SQL when the user expects PostHog's standard definitions. Confirm that the selected tool supports the required calculation and output.

SQL queries

Use `posthog:execute-sql` when:

• The request searches `system.*` tables for PostHog entities.
• The user requests SQL, record inspection, or changes to an existing SQL query.
• The analysis needs custom joins, CTEs, window functions, or warehouse SQL.
• SQL results must inform how you construct a later typed query. Typed query tools cannot accept SQL results as input.

When either method fits

For a new event-analytics query, prefer a typed query when both methods preserve the requested calculation and output. This includes simple counts, sums, and other supported aggregates. Use SQL directly when the task calls for it. You do not need to try a typed query first.

Keep a valid existing query when it fits the task. Choose the method again when the task changes. The previous tool call does not determine the method. Do not choose a method only because the user requests a chart or table. Both methods can support saved visualizations.

Render query results

Use the UI resource returned by the selected query tool. For example, `posthog:query-trends` returns the `query-results` UI resource. Do not call `posthog:render-ui` for the same result.

Keep a written summary with the visualization. If the query tool does not return a UI, follow the client's rendering instructions.

When to use this skill

Finding a specific PostHog entity

When the user wants to find a specific entity created in PostHog (insights, dashboards, cohorts, feature flags, experiments, surveys, hog flows, data warehouse items, etc.), or when a list/search tool returns too many results to narrow down:

1. Read the appropriate schema reference under Data Schema to understand the entity's table and columns. 2. Use `posthog:execute-sql` to query the system table and find the matching entity (typically returning its ID). 3. Use the dedicated read tool for that entity type (e.g. `posthog:insight-get`, `posthog:dashboard-get`) to retrieve the full entity by ID.

Don't try to reconstruct the entity from SQL — `execute-sql` is for discovery, the read tool is for retrieval.

Querying analytics data

When SQL is the selected method for an analytics request:

1. Look for a matching example under Analytics Query Examples. The list is not exhaustive — there may not be an example for every scenario. If one is a close fit (same domain, similar aggregation), read it; otherwise skip this step. 2. Adapt the example query (if one was found) to the user's request and run it via `posthog:execute-sql`. If no example fit, compose the query from scratch using the Data Schema and HogQL References.

Answering a headline business or telemetry measure (semantic layer)

When the user asks for a governed business or telemetry measure (MRR, activation rate, billable usage, active organizations, failure rates, ...), or asks how such a measure is defined ("what is our definition of an active org?"), check the data catalog's semantic layer before deriving it from raw data or calling a typed domain tool — the project may have a canonical, human-approved definition to reuse instead of guessing.

1. Inspect the complete catalog with `posthog:metric-list`, following pagination until every metric has been considered. Do this before the first `query-*`, `execute-sql`, or typed domain-tool call that would answer the question — whether that call produces a number or reconstructs a definition (for example, reading a saved insight's stored query). An empty catalog means no governed definition exists. An unknown-table error means this project has no data catalog at all, so there is nothing to add a metric to. Either way, derive the answer yourself and label it noncanonical.

2. For every candidate that might fit, call `posthog:metric-describe` t