Introduction to HogQL

HogQL is a query language developed by PostHog as a simplified interface over ClickHouse SQL, designed for advanced analytics within the PostHog platform. It offers familiar SQL functions like SELECT, JOIN, WHERE, and aggregation, with custom enhancements to handle user and event data more easily (think things like event properties, tracking session data and segmenting users by custom attributes).

Long story short, HogQL is really useful for businesses looking to analyse user behaviour in ways that the standard UI doesn't allow. If you're currently a Google Analytics user, check out our article on reasons why PostHog is better.

For more information see Introducing HogQL and the HogQL docs.

Here are some examples.

1 Filter URLs based on language

Here's an expression that filters for URLs containing /de/ using the LIKE operator (similar to SQL).

SELECT *

FROM events

WHERE properties.$current_url LIKE '%/de/%'

2 Analysing button text

To add button text to a content report we can add the elements_chain HogQL expression to an insight. Elements chain shows the complete chain for an autocapture event (for more info see https://posthog.com/tutorials/hogql-autocapture), to see it use:

elements_chain

Now use the extract expression to check whether a string matches a regular expression pattern as per https://posthog.com/docs/hogql/expressions the function is:

extract(haystack, pattern)

PostHog automatically adds attr__ to the attributes in the element chain, so we must include that in our regex. So to get all button text stored in 'attr_value' use:

extract(elements_chain, '[:|"]attr__value="(.*?)"')

3 Split out language from URL

If you URLs include language e.g. www.yourdomain.com/de/, www.yourdomain.com/fr/ etc and you want to provide a language breakdown

Use multiIf to group events based on properties as follows:

multiIf(properties.$current_url LIKE '%/ja%','Japanese', properties.$current_url LIKE '%/fr%','French', properties.$current_url LIKE '%/it%','Italian',properties.$current_url LIKE '%/de%','German',properties.$current_url LIKE '%/es%','Spanish','English')

4 Product page views by site

To get the total number of pageviews per language site use a SUM statement as below.

SELECT

SUM(CASE WHEN properties.$pathname NOT LIKE '%/fr/product/%' OR properties.$pathname NOT LIKE '%/it/product/%' OR properties.$pathname NOT LIKE '%/de/product/%' OR properties.$pathname NOT LIKE '%/es/product/%' OR properties.$pathname NOT LIKE '%/ja/product/%' THEN 1 ELSE 0 END) AS UK,

SUM(CASE WHEN properties.$pathname LIKE '%/ja/product/%' THEN 1 ELSE 0 END) AS JA,

SUM(CASE WHEN properties.$pathname LIKE '%/de/product/%' THEN 1 ELSE 0 END) AS DE,

SUM(CASE WHEN properties.$pathname LIKE '%/fr/product/%' THEN 1 ELSE 0 END) AS FR,

SUM(CASE WHEN properties.$pathname LIKE '%/it/product/%' THEN 1 ELSE 0 END) AS IT,

SUM(CASE WHEN properties.$pathname LIKE '%/es/product/%' THEN 1 ELSE 0 END) AS ES,

FROM events

WHERE properties.$pathname LIKE '%/product/%'

5 Analysing content categories

If your content groups include multiple categories in a single field, you can split the field using HogQL. For example, to break down pageviews by category in an insight use HogQL as follows:

splitByChar(',',properties.category) 

Other helpful things to know

The 'Edit SQL directly' option

A quick tip before we start - clicking 'Edit SQL directly' on PostHog insights in the UI allows you to edit the SQL and create a SQL insight from it. This allows you to tweak existing queries to analyse your data in ways that may not be possible with the pre-built insight types.

For exactly, if an insight current orders by total DESC you can edit the SQL directly and change to total DESC.

Note: when you edit the SQL directly, you're essentially creating a new, custom analysis.

Using 'With ... AS' in SQL

Using WITH ... AS in SQL is a common way to define a Common Table Expression (CTE), which allows you to create a temporary result set that can be referred to within a query.

In the example below, the data CTE is a temporary table that exists for the duration of the query.

WITH data AS (

SELECT column1, column2, ...

FROM table_name

WHERE condition

)

SELECT * FROM data;

Happy analysing 😀