AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

querying-posthog-data

Explains how to query PostHog data, defaulting to typed runners for supported product analytics. 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.

Original source
posthog
License
MIT
Pinned revision
cac7c80a9e5b73484d18eda1bffb47297c938e0c

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read posthog--querying-posthog-data at revision cac7c80a9e5b73484d18eda1bffb47297c938e0c, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 65

references/example-trends-unique-users.md

Daily unique users over the last 30 days

Native trends insight

First, check the event with posthog:read-data-schema. Then call posthog:query-trends with this input for a native daily trends insight:

{
  "kind": "TrendsQuery",
  "series": [{ "kind": "EventsNode", "event": "chat with ai", "math": "dau" }],
  "dateRange": { "date_from": "-30d" },
  "interval": "day"
}

The omitted filterTestAccounts field follows the project's "Filter out internal and test users" setting. Set it only when the request explicitly asks to override that setting.

Use the native insight controls for breakdowns or period comparisons. Do not generate SQL only to render this insight.

If the harness displays the interactive chart inline, summarize it without rendering again. If a successful exec call returns data without a chart and the top-level render-ui tool includes query-trends in its enum, call it directly with the same input:

{
  "tool_name": "query-trends",
  "tool_input": {
    "kind": "TrendsQuery",
    "series": [{ "kind": "EventsNode", "event": "chat with ai", "math": "dau" }],
    "dateRange": { "date_from": "-30d" },
    "interval": "day"
  }
}

If there is no supported UI tool, follow the harness's presentation instructions or summarize the result. Keep the typed query.

SQL representation

This build-time SQL represents the same aggregation and time range. It cannot inherit the project's test-account setting. Before running or adapting it, apply the project's test-account filters when "Filter out internal and test users" is enabled.

SELECT
    arrayMap(number -> plus(toStartOfInterval(assumeNotNull(toDateTime('2025-11-10 00:00:00')), toIntervalDay(1)), toIntervalDay(number)), range(0, plus(coalesce(dateDiff('day', toStartOfInterval(assumeNotNull(toDateTime('2025-11-10 00:00:00')), toIntervalDay(1)), toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)))), 1))) AS date,
    arrayMap(_match_date -> arraySum(arraySlice(groupArray(ifNull(count, 0)), indexOf(groupArray(day_start) AS _days_for_count, _match_date) AS _index, plus(minus(arrayLastIndex(x -> equals(x, _match_date), _days_for_count), _index), 1))), date) AS total
FROM
    (SELECT
        sum(total) AS count,
        day_start
    FROM
        (SELECT
            count(DISTINCT e.person_id) AS total,
            toStartOfDay(timestamp) AS day_start
        FROM
            events AS e
        WHERE
            and(greaterOrEquals(timestamp, toStartOfInterval(assumeNotNull(toDateTime('2025-11-10 00:00:00')), toIntervalDay(1))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59'))), equals(event, 'chat with ai'))
        GROUP BY
            day_start)
    GROUP BY
        day_start
    ORDER BY
        day_start ASC)
ORDER BY
    arraySum(total) DESC
LIMIT 50000

Simple aggregate: default to a typed query

For "Count chat with ai events in the last seven days", either a typed total or SQL can answer the question. Use the typed query for a new analysis when both methods preserve the requested calculation and output. Use posthog:query-trends with math: "total", compareFilter.compare: true, trendsFilter.display: "Metric", trendsFilter.metricSummary: "total", and the requested time bounds. Resolve both rolling bounds to ISO 8601 timestamps and pass them as date_from and date_to when matching the SQL window below.

The SQL form remains valid when the user requests SQL or an existing SQL query already fits:

SELECT count() AS event_count
FROM events
WHERE event = 'chat with ai'
  AND timestamp >= now() - INTERVAL 7 DAY
  AND timestamp <= now()

Keep test-account filtering consistent. Reuse either valid form when it already fits the task. A single number does not require a method change.