5 min read

What Is SQL Queries in PostHog

PostHog's standard insights UI is built for common analyses—funnels, retention, trends. But what happens when you need something custom? SQL Queries in PostHog let you write direct SQL against your event data, bypassing the UI entirely. It's where analysts go when the standard tools won't cut it.

What SQL Queries Are and How They Work

SQL Queries is PostHog's escape hatch for custom analysis. Instead of clicking through the insights builder, you write raw SQL.

SQL Queries Query Your Event Table Directly

Every event PostHog captures lands in a table. SQL Queries let you write SELECT statements against that table. You get columns like timestamp, event, properties, distinct_id, and more. It's the same data the UI uses, but you have full control.

javascript
// Example SQL Query structure in PostHog
SELECT 
  event,
  COUNT(*) as event_count,
  COUNT(DISTINCT distinct_id) as unique_users
FROM events
WHERE timestamp > now() - interval '7 days'
GROUP BY event
ORDER BY event_count DESC;
Basic query structure. The events table contains all your event data.

They're Different from the Standard Insights UI

The insights UI (trends, funnels, retention) handles filtering and aggregation for you. SQL Queries require you to write that logic. Trade-off: more flexibility, but more responsibility to write correct SQL.

javascript
// Same analysis, but in SQL Queries
// Instead of clicking Insights > Trends > Filter by event
SELECT 
  DATE_TRUNC('day', timestamp) as day,
  COUNT(*) as count
FROM events
WHERE event = 'user_signup'
  AND timestamp > now() - interval '30 days'
GROUP BY DATE_TRUNC('day', timestamp)
ORDER BY day DESC;
The SQL Queries equivalent of a basic trend chart.
Tip: If you can build what you need in the insights UI, do that first. SQL Queries are for analyses the UI doesn't support.

Why SQL Queries Matter

You'll reach for SQL Queries when the standard UI limitations get in the way.

Complex Multi-Step Logic

Suppose you need to find users who clicked a button, then visited a page, then completed a purchase—but only if they didn't churn. The funnel UI can't express that. SQL can.

javascript
-- Find users with specific event sequence, excluding churned users
WITH user_events AS (
  SELECT 
    distinct_id,
    event,
    timestamp
  FROM events
  WHERE event IN ('button_click', 'page_view', 'purchase')
    AND timestamp > now() - interval '30 days'
)
SELECT COUNT(DISTINCT distinct_id) as users
FROM user_events
WHERE distinct_id NOT IN (
  SELECT distinct_id FROM events WHERE event = 'churn_flag'
);
SQL handles conditional logic and subqueries the UI can't express.

Custom Segmentation and Filtering

You might need to segment by a calculation—users whose lifetime value exceeds their acquisition cost, or whose last purchase was more than 6 months ago. SQL Queries let you define that logic exactly.

javascript
-- Users with no purchase in the last 6 months
SELECT 
  distinct_id,
  MAX(timestamp) as last_purchase,
  NOW() - MAX(timestamp) as days_since_purchase
FROM events
WHERE event = 'purchase'
GROUP BY distinct_id
HAVING MAX(timestamp) < now() - interval '6 months'
ORDER BY last_purchase DESC;
Define custom segments that the UI can't build.
Watch out: The data in SQL Queries can have gaps or delays depending on your event pipeline. Always verify results with a time window you understand.

Accessing SQL Queries in PostHog

SQL Queries live in the insights section alongside trends and funnels.

Navigate to SQL Queries Editor

Go to Insights, then select SQL from the query type options at the top. PostHog opens an editor with syntax highlighting and your database schema on the right.

javascript
// Minimal valid PostHog SQL Query
SELECT COUNT(*) as total_events
FROM events
LIMIT 100;
Start with a simple query to verify connectivity.

Understand Event Properties as JSON

Event properties in PostHog are stored as JSON. To access them, use the properties column and JSON operators. The syntax depends on your database backend.

javascript
-- Access event properties (JSON)
SELECT 
  event,
  properties->>'plan' as user_plan,
  COUNT(*) as count
FROM events
WHERE properties->>'plan' IN ('pro', 'enterprise')
GROUP BY event, properties->>'plan'
LIMIT 100;
Use JSON operators to filter and extract property values.
Tip: Use LIMIT on early queries to avoid processing huge datasets. Remove it once you've validated the query.

Common Pitfalls

  • Forgetting that properties are JSON—trying to filter properties.plan = 'pro' instead of properties->>'plan' = 'pro' will fail.
  • Writing queries without date filters—SQL Queries can process millions of events, so always add a timestamp constraint.
  • Assuming SQL Queries return real-time data—there can be a delay (usually minutes) between an event firing and appearing in the table.
  • Not checking your database schema—PostHog's column names and JSON structure differ between self-hosted and Cloud. Verify in the schema browser.

Wrapping Up

SQL Queries in PostHog give you the control the standard UI can't provide. You can write custom analyses, segment by complex logic, and get exact answers to questions the insights UI wasn't built for. If you want to track this automatically across tools, Product Analyst can help.

Track these metrics automatically

Product Analyst connects to your stack and surfaces the insights that matter.

Try Product Analyst — Free