> AI agents: this is one page from PostHog's docs. Full index of Markdown docs for LLMs: https://posthog.com/llms.txt # Accessing data using SQL Canonical URL: https://posthog.com/docs/data-warehouse/sql/data-access > **Cloud region:** This page uses US Cloud hosts. For an EU Cloud project, use `https://eu.posthog.com` instead of `https://us.posthog.com` (app host for links). ## On this page - [Strings and quotes](#strings-and-quotes) - [Types](#types) - [Property access](#property-access) - [Actions](#actions) - [Cohorts](#cohorts) ## Strings and quotes Quotation symbols work the same way they would work with ClickHouse, which inherits from ANSI SQL: - **S**ingle quotes (`'`) for **S**tring literals. - **D**ouble quotes (`"`) and **B**ackticks (\`) for **D**ata**B**ase identifiers. For example: ```sql SELECT * FROM events WHERE properties.`$browser` = 'Chrome' ``` ## Types Types (and names) for the accessible data can be found in the [database](https://us.posthog.com/data-management/database), [properties](https://us.posthog.com/data-management/properties) tabs in data management as well as in the [SQL tab](https://us.posthog.com/sql) for external sources. They include: - `STRING` (default) - `JSON` (accessible with dot or bracket notation) - `DATETIME`(in `ISO 8601`, [read more in our data docs](https://posthog.com/docs/data/timestamps.md)) - `INTEGER` - `FLOAT` - `BOOLEAN` For example: ```sql SELECT round(properties.$screen_width * properties.$screen_height / 1000000, 2) as `Screen MegaPixels` FROM events LIMIT 100 ``` This works because `$screen_width` and `$screen_height` are both defined as numeric properties. Thus you can multiply them. To cast a string property into a different type, use type conversion functions, such as`toString`, `toDate`, `toFloat`, `JSONExtractString`, `JSONExtractInt`, and more. ## Property access To access a property stored on an event or person, just use dot notation. For example `properties.$browser` or `person.properties.$initial_browser`. You can also use bracket notation like `properties['$feature/cool-flag']`. Nested property or JSON access, such as `properties.$some.nested.property`, works as well. > PostHog's properties include always include `$` as a prefix, while custom properties do not (unless you add it). Property identifiers must be known at query time. For dynamic access, use the JSON manipulation functions from below on the `properties` field directly. Some queries can error when accessing null values. To avoid this, use the `COALESCE` function to replace null values with a default value or filter `NULL` values with `IS NOT NULL` and use `assumeNotNull` to cast a column to a non-null type. ## Actions To use [actions](https://posthog.com/docs/data/actions.md) in SQL insights, use the `matchesAction()` function. For example, to get a count of the action `clicked homepage button`, you can do: ```sql SELECT count() FROM events WHERE matchesAction('clicked homepage button') ``` For more customization when using actions, start by selecting you action in the [actions tab](https://us.posthog.com/data-management/actions) under data management. In the action details under "Matching events," click the export dropdown and select "Edit SQL directly." ![Action SQL](https://res.cloudinary.com/dmukukwp6/image/upload/v1714065991/posthog.com/contents/images/docs/product-analytics/action-sql-light.png) This opens an SQL insight using the action. You can then copy parts of the SQL, like the `WHERE` filter or columns under `SELECT`, to use in your own insights. ## Cohorts To use [cohorts](https://posthog.com/docs/data/cohorts.md) in SQL insights, simply filter where `person_id IN COHORT '{name}'`. For example, to get a count of users in the `Power user` cohort: ```sql select count() from persons where id IN COHORT 'Power users' ``` To get a count of events for users in the `Power user` cohort: ```sql select count() from events where person_id IN COHORT 'Power user' ```