Query your sources

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.

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.

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