Querying the data warehouse with SQL

PostHog provides the full flexibility of SQL to query your data warehouse using the SQL editor.

To create a query, go to the SQL editor. Here you can see and search the schema of all available sources and PostHog tables as well as saved views.

Here you can:

1. Write your SQL query using your table like SELECT * FROM stripe.prod.charge
2. Click Run to see the results.
3. Modify your query using SQL commands and functions as needed to get the data you want like select amount / 100 as dollar_charge, status from stripe.prod.charge.

4. Click Create insight.
5. Choose and customize your visualization.
6. Click Save insight.

SQL visualizations

SQL queries have multiple different visualization options including:

• Table (default)
• Big number
• Line chart
• Bar chart
• Stacked bar chart
• Area chart

By clicking on the Visualization tab below the query, you can customize the X-axis, Y-axis, legend, scale, goal lines, and more. Each visualization type has its own set of customization options.

For example, with tables, you can add conditional formatting rules. These enable you to highlight cells based on their value and are set up in the Conditional formatting tab.

Querying multiple sources together

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

To learn more about joining data, see our guide on joining data.