HogQL

Last updated:

|Edit this page

On this page

HogQL is our take on SQL (Structured Query Language), a language used to manage and access data. It is effectively a wrapper around ClickHouse SQL, with tweaks such as simplified event and person property access, null handling, and visualization integrations.

HogQL is currently in public beta. This means it's not yet a perfect experience, and the language itself may still change. Follow along with the development here.

HogQL expressions

HogQL expressions enable you to use database identifiers and functions to directly access, aggregate, filter, transform, and breakdown your data.

They can be used by selecting the "HogQL" tab or "HogQL expression" option in filters, breakdowns, dashboards, trends, funnels (aggregating by), user paths (event types), the activity tab (columns), and more.

For example, to group pageviews into "desktop" or "mobile," you can breakdown with the expression multiIf(properties.$os == 'Android', 'mobile', properties.$os == 'iOS', 'mobile', 'desktop')

HogQL expression example

SQL insights

SQL insights enable you to directly query your data with SQL commands like SELECT, FROM, JOIN, WHERE, GROUP BY along with many of ClickHouse SQL's function. This enables more complex and customizable queries and results than other insights.

For example, to get a count of the most popular first pageview current_url values, we can use an SQL query like this:

SQL
SELECT 
    properties.$current_url AS current_url,
    count() AS url_count
FROM events
WHERE event = '$pageview'
    AND (distinct_id, timestamp) IN (
        SELECT distinct_id, min(timestamp)
        FROM events
        WHERE event = '$pageview'
        GROUP BY distinct_id
    )
    AND {filters}
GROUP BY current_url
ORDER BY url_count DESC
HogQL SQL insight

You can use SQL insights within notebooks and with external sources using the data warehouse.

Query API

To query events using HogQL via the PostHog API, get your project ID, a personal API key with the project query read permission and make a POST request to /api/projects/:project_id/query endpoint with the following JSON payload:

JSON
{"query": {"kind": "HogQLQuery", "query": "select * from events"}}

For example, to get a count of the most common event values, you can make a request like this (change us.posthog.com to eu.posthog.com if you're on EU cloud):

curl -X POST "<ph_app_host>/api/projects/:project_id/query" \
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer <personal_api_key>" \
     -d '{
            "query": {
              "kind": "HogQLQuery", 
              "query": "SELECT event, COUNT() FROM events GROUP BY event ORDER BY COUNT() DESC"
            }
         }'

The response is in the format:

TypeScript
export interface HogQLQueryResponse {
    /** The input query */
    query?: string
    /** An array of result arrays */
    results?: any[][]
    /** Returned column types */
    types?: string[]
    /** Returned column names/aliases */
    columns?: string[]
    /** Generated HogQL query with expressions inlined */
    hogql?: string
    /** Generated ClickHouse query for debugging */
    clickhouse?: string
}

While in the public beta, the response format may still change.

Will there be API pricing? The HogQL API is free to use while it's in the public beta and we work out the details. After we launch for real, we plan to charge a competitive rate for heavy usage. Stay tuned.

Data warehouse

To get a list of all the sources you can query with HogQL, enable the data warehouse beta and check out the "Data warehouse" tab. You can click on every table listed to see the data included and query them.

The data warehouse also enables you to add external sources, like Stripe and Hubspot, and query them alongside your PostHog data.

Questions?

  • Artem    5 months ago

    Number of users who make a certain number of sessions per month

    I can't figure out where the error is in the query to calculate the number of users who make a certain number of sessions per month:

    WITH session_counts AS (
        SELECT
            distinct_id,
            COUNT(session_id) AS session_count
        FROM
            sessions
        WHERE
            $start_timestamp >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
        GROUP BY
            distinct_id
    ),
    session_frequency AS (
        SELECT
            distinct_id,
            CASE
                WHEN session_count = 1 THEN '1'
                WHEN session_count < 1 THEN '<1'
                WHEN session_count BETWEEN 2 AND 4 THEN '2-4'
                ELSE '>4'
            END AS frequency
        FROM
            session_counts
    )
    SELECT
        frequency,
        COUNT(distinct_id) AS user_count
    FROM
        session_frequency
    GROUP BY
        frequency
  • Alexander    6 months ago

    Basic Where Statement

    How do you do:

    select * from table where name = 'foo'

    • Alexander      Author6 months ago

      Help Max

    • Max AI
      6 months ago

      The basic WHERE statement in PostHog SQL is similar to standard SQL. Here's how you can structure a query with a WHERE clause:

      SELECT * FROM events WHERE event = '$pageview'

      (1)

      This query selects all columns from the 'events' table where the 'event' column equals '$pageview'. You can modify this to fit your specific needs, such as:

      SELECT * FROM events WHERE properties.$browser = 'Chrome'

      (1)

      Remember, PostHog uses single quotes for string literals and backticks for database identifiers(1). Also, PostHog SQL queries don't require a trailing semicolon(1).

      For more complex conditions, you can use logical operators like AND, OR, or NOT(1). For example:

      SELECT * FROM events 
      WHERE event = '$pageview'  
      AND toDate(timestamp) = today()  
      AND properties.$current_url LIKE '%/blog%'

      (1)

      This query filters for pageview events that occurred today and have a current URL containing '/blog'.

  • Linus    7 months ago

    Bad request

    We have build a proxy that uses Axios against our backend with this. It works great for 99% of the time. But from time to time we get a status 512 response from Posthog. Can we look more into this in some way? I haven't found any documentation that explains this.

  • Avirup    10 months ago

    Can we create a dynamic cohort using HogQL?

    I have an event X, which has a property Y, and I need to create a cohort where all users have generated event X and have count(distinct(Y)) >= 3 in the last N days. I need this to create a dynamic cohort. This is currently an activation metric in our organization, and am trying to figure out how to achieve this using Posthog. Can anyone help?

  • Aneesa    a year ago

    there is no refresh button on dashboard

    image.png

    • Paul(he/him)      a year agoSolution

      Hey,

      There's a single refresh button for the dashboard at the top.

      Screenshot 2024-05-16 at 09.50.18.png

    • Aneesa      Authora year ago

      but for other widgets there is a separate refresh button. Oh yeah, HogQL also can't use global filters, I've used the where {filters} query to be able to use global filters, but it can't filter.

  • Yuvraj    a year ago

    How to extract data which is in the form of JSON

    image.png

    This is the data I have to extract Feedback that is in the form of JSON please help

    • Will      7 months ago

      Documentation here is really quite poor

  • Hamza    a year ago

    Column Creation

    Hi!

    I would like to know how can I create a column which would display the "name" associated with the "person" on the events page.

    For context, I am attaching a screenshot which shows where the value "name" is located. Screenshot 2024-03-31 at 4.28.54 PM.png

    • Marcus      a year ago

      Hey Hamza, could you share more context on the entire HogQL query, you are trying to get? You could use the following HogQL query to show each event alongside the name and email of the person:

      select event,
               person.properties.email,
               person.properties.name
         from events
         where {filters} -- replaced with global date and property filters
         and person.properties.email is not null
         limit 100
    • Daruá      a year ago

      Hazam, I manage to solve this problem adding a breakdown. So, filter the Propertie and call it in breakdown section. Look at my screenshot propertie in column.png

  • Nihar    a year ago

    How to breakdown by 2 properties?

    Hey there, I am trying to use the breakdowns, for example I have button clicks in my app, I want to break it down by diff. user groups (diff. white label users), and diff. clicks.

    So basically a bar graph of breakdown by diff. groups for diff. button clicks.

    • Marcus      a year agoSolution

      Hey Nihar, that could be achieved using the following HogQL expression: concat(properties.$browser, ' - ', properties.$os).

  • Mark    a year ago

    How can i Install HogQL

    Hi, how can i install HogQL?

    • Andy      a year ago

      To confirm, HogQL isn't part of the open source release atm as it's a beta feature.

  • Zarar    a year ago

    SQL or HogQL?

    What is the recommended way to query data for use in my own app (e.g., graphs) - using SQL or HogQL? Do both use the same endpoint?

    Do both have the same rate limits? I saw the docs on HoqQL rate limiting, but does SQL also have the same rate limits?

  • Philip    a year ago

    SQL insight not accesable through api

    I've created an sql insight in the dashboard that works as expected but when trying to access it through an API call I get a null response. Is this due to it being in beta or do I need to enable something? We also tested a similar call with a default insight and it was returned so it shouldn't be an issue with the person API token. Ex. api call: https://app.posthog.com/api/projects/<project_id>/insights/?refresh=true&short_id=<short_id>

  • Alex    2 years ago

    SQL Insight params

    When creating an SQL insight, is there a way to include the dashboard filter inside the query? For example if the filter is Last 30 days to be able to use the date range in the SQL query?

    • Cameron      2 years agoSolution

      Hey Alex,

      At the moment, we are in between two query systems/endpoints for different parts of the product. A major Q3 goal for the team that owns this is to solve exactly this problem, as part of a larger chunk of work to port everything to the use the endpoint that the SQL insight does (HogQL). The full to do list is here if interested. Stay tuned!

    • Alex      Author2 years ago

      That sounds awesome. Can't wait see it finished :D

Was this page useful?

Next article

HogQL expressions

HogQL expressions enable you to directly access, modify, and aggregate data in many places in PostHog including: Filters Trends series Breakdowns Funnel aggregations User paths Session replays Dashboards The activity tab Tip: If you're having trouble getting results from your expression, try debugging by using a different visualization (trends table often works best as it shows all values returned) or breaking down your expression into pieces and testing each one. Accessible data HogQL…

Read next article

PostHog.com doesn't use third party cookies - only a single in-house cookie.

No data is sent to a third party.

Ursula von der Leyen, President of the European Commission