6 min read

How to Set Up SQL Queries in PostHog

PostHog's standard insights cover most use cases, but sometimes you need to drill into your raw event data with custom logic. SQL queries let you do exactly that—write direct queries against your events table to calculate metrics your dashboards don't already handle. You'll need a PostHog account with SQL access enabled (available on all plans).

Access the SQL Editor

The SQL editor is built directly into PostHog's Insights view.

Navigate to Insights and Create a New Insight

Log into PostHog and click Insights in the left sidebar. Then click + New (top right) to create a new insight.

javascript
// Access PostHog insights via API
const response = await fetch('https://api.posthog.com/api/projects/{project_id}/insights/', {
  headers: {
    'Authorization': `Bearer ${POSTHOG_API_KEY}`,
    'Content-Type': 'application/json'
  }
});
const insights = await response.json();
List all insights in your PostHog project

Select SQL as Your Insight Type

In the new insight dialog, choose SQL from the insight type dropdown. You'll see a blank SQL editor open with sample syntax.

javascript
// Create a new SQL insight via API
const newInsight = await fetch('https://api.posthog.com/api/projects/{project_id}/insights/', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${POSTHOG_API_KEY}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    name: 'My First SQL Query',
    query: {
      kind: 'SqlQuery',
      query: 'SELECT event, COUNT(*) as count FROM events GROUP BY event LIMIT 10'
    }
  })
});
const insight = await newInsight.json();
Create a SQL insight programmatically

Write Your First SQL Query

PostHog events are stored in the events table. Each row is a single event capture with properties, user info, and timestamps.

Query the Events Table

Start with a simple query to count events by type. The events table has columns like event (event name), properties (JSON), timestamp, and distinct_id (user identifier).

javascript
// Simple query: count events by type
const query = `
  SELECT 
    event,
    COUNT(*) as event_count,
    COUNT(DISTINCT distinct_id) as unique_users
  FROM events
  WHERE timestamp > now() - interval 7 day
  GROUP BY event
  ORDER BY event_count DESC
  LIMIT 20
`;

const response = await fetch('https://api.posthog.com/api/projects/{project_id}/query/', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${POSTHOG_API_KEY}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({ query })
});
const results = await response.json();
Query last 7 days of events with distinct user counts

Filter by Custom Properties

Use properties to access custom event properties stored in JSON. Access nested properties with -> and ->> for text values.

javascript
// Query with custom property filtering
const query = `
  SELECT 
    properties->>'plan' as user_plan,
    event,
    COUNT(*) as event_count
  FROM events
  WHERE timestamp > now() - interval 30 day
    AND properties->>'plan' IS NOT NULL
    AND event = 'feature_used'
  GROUP BY properties->>'plan', event
  ORDER BY event_count DESC
`;

const response = await fetch('https://api.posthog.com/api/projects/{project_id}/query/', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${POSTHOG_API_KEY}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({ query })
});
const results = await response.json();
Count feature usage by user plan from custom properties

Run and Save Your Query

Click Run to preview your results in the editor. Once you're happy with the output, click Save to store it as a reusable insight with a name and description.

javascript
// Save a SQL query as a reusable insight
const saveInsight = await fetch('https://api.posthog.com/api/projects/{project_id}/insights/', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${POSTHOG_API_KEY}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    name: 'Daily Active Users by Plan',
    description: 'Count of unique users per day, broken down by subscription plan',
    query: {
      kind: 'SqlQuery',
      query: `SELECT 
        DATE_TRUNC('day', timestamp) as day,
        properties->>'plan' as plan,
        COUNT(DISTINCT distinct_id) as daily_active_users
      FROM events
      WHERE timestamp > now() - interval 90 day
      GROUP BY DATE_TRUNC('day', timestamp), properties->>'plan'
      ORDER BY day DESC`
    }
  })
});
const savedInsight = await saveInsight.json();
Save a SQL query for team reuse and dashboards
Watch out: Custom property names are case-sensitive. If your property is planType not plan, your filter will return NULL.

Common SQL Query Patterns

Here are patterns you'll use constantly when analyzing PostHog data.

Count Unique Users Over Time

Use DATE_TRUNC and COUNT(DISTINCT distinct_id) to track DAU (daily active users) or other time-series metrics.

javascript
// Daily active users (DAU) trend
const dauQuery = `
  SELECT 
    DATE_TRUNC('day', timestamp) as date,
    COUNT(DISTINCT distinct_id) as dau
  FROM events
  WHERE timestamp > now() - interval 60 day
  GROUP BY DATE_TRUNC('day', timestamp)
  ORDER BY date DESC
`;

const response = await fetch('https://api.posthog.com/api/projects/{project_id}/query/', {
  method: 'POST',
  headers: { 'Authorization': `Bearer ${POSTHOG_API_KEY}`, 'Content-Type': 'application/json' },
  body: JSON.stringify({ query: dauQuery })
});
const dau = await response.json();
Track daily active users over a 60-day window

Calculate Conversion Funnels with Self-Joins

Use CTEs (Common Table Expressions) and self-joins to track users moving between events. Filter for users who hit step A, then step B within a time window.

javascript
// Conversion funnel: signup → payment → feature_used
const funnelQuery = `
  WITH signup_events AS (
    SELECT DISTINCT distinct_id, MIN(timestamp) as signup_time
    FROM events
    WHERE event = 'user_signup' AND timestamp > now() - interval 90 day
    GROUP BY distinct_id
  ),
  payment_events AS (
    SELECT DISTINCT distinct_id, MIN(timestamp) as payment_time
    FROM events
    WHERE event = 'payment_processed' AND timestamp > now() - interval 90 day
    GROUP BY distinct_id
  ),
  feature_events AS (
    SELECT DISTINCT distinct_id, MIN(timestamp) as feature_time
    FROM events
    WHERE event = 'feature_used' AND timestamp > now() - interval 90 day
    GROUP BY distinct_id
  )
  SELECT 
    COUNT(DISTINCT s.distinct_id) as signups,
    COUNT(DISTINCT p.distinct_id) as payments,
    COUNT(DISTINCT f.distinct_id) as feature_users,
    ROUND(100.0 * COUNT(DISTINCT p.distinct_id) / COUNT(DISTINCT s.distinct_id), 2) as signup_to_payment_pct
  FROM signup_events s
  LEFT JOIN payment_events p ON s.distinct_id = p.distinct_id AND p.payment_time <= s.signup_time + interval 30 day
  LEFT JOIN feature_events f ON p.distinct_id = f.distinct_id AND f.feature_time <= p.payment_time + interval 7 day
`;

const response = await fetch('https://api.posthog.com/api/projects/{project_id}/query/', {
  method: 'POST',
  headers: { 'Authorization': `Bearer ${POSTHOG_API_KEY}`, 'Content-Type': 'application/json' },
  body: JSON.stringify({ query: funnelQuery })
});
const funnel = await response.json();
Multi-step funnel with conversion rates between each step

Common Pitfalls

  • Forgetting LIMIT — large queries without a limit can timeout. Always start with LIMIT 100 or LIMIT 1000 while testing.
  • Confusing distinct_id (PostHog's user identifier) with user IDs from your database. They're not the same unless you explicitly set them.
  • Using COUNT(distinct_id) instead of COUNT(DISTINCT distinct_id). The latter counts unique users; the former just counts rows.
  • Not filtering by date range. Queries on the entire events table without a timestamp filter will be slow. Always add WHERE timestamp > now() - interval X day.

Wrapping Up

You now have the tools to write custom SQL queries against your PostHog event data. You can drill into user behavior, calculate custom funnels, and answer questions that standard insights can't tackle. If you want to track these metrics automatically across all your tools and compare them side-by-side, Product Analyst can help.

Track these metrics automatically

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

Try Product Analyst — Free