Query your sources

Last updated:

|Edit this page

To see all of the sources available to query, go to the data warehouse tab. This page shows all the external and PostHog tables you can query through SQL insights along with saved views. Click on any of the tables, and then click "Query" to open an SQL insight.

Data warehouse tab in PostHog app

Querying sources

To start with querying your sources:

  1. Choose a table and click "Query" or create a new SQL insight.
  2. If you didn't already, choose a table to query FROM like stripe_charge or hubspot_contacts
  3. Write your SQL query using your table like SELECT * FROM hubspot_contacts
  4. Click "Update and run" to see the results.
  5. Modify your query using SQL commands as needed to get the data you want like SELECT email FROM hubspot_contacts WHERE city = 'Brisbane'.
  6. Save your query.
Querying a source in PostHog

Combining data sources

Much of the power of the data warehouse comes from combining multiple sources, like your Stripe or Hubspot data with your product analytics data. Two of the easiest ways of doing this are WHERE IN and JOIN SQL commands.

For example, to get a count of events for your Hubspot contacts you can filter events.distinct_id by email FROM hubspot_contacts like this:

SELECT COUNT() AS event_count, distinct_id
FROM events
WHERE distinct_id IN (SELECT email FROM hubspot_contacts)
GROUP BY distinct_id
ORDER BY event_count DESC

You can also use a JOIN such as INNER JOIN or LEFT JOIN to combine data. For example, to get a count of events for your Stripe customers you can INNER JOIN on distinct_id and email like this:

SELECT events.distinct_id, COUNT() AS event_count
FROM events
INNER JOIN prod_stripe_customer ON events.distinct_id = prod_stripe_customer.email
GROUP BY events.distinct_id
ORDER BY event_count DESC


Was this page useful?

Next article

Creating views

In the PostHog data warehouse, you can save your most used queries as views and reference them in subsequent queries. Creating a view Query views are created directly inside SQL insights. If the query has valid view characteristics, the "Save as view" button is enabled. When clicked, you are prompted to give the view a name which can then be referenced in other queries. For a query to be a valid view, all fields being accessed must be aliased (with the SQL as keyword). The alias names are…

Read next article