Skip to main content
All CollectionsJune AI
How to use SQL to explore your data in June
How to use SQL to explore your data in June
Updated this week

The AI explorer uses GPT-4 to translate questions about your product into SQL queries that can be executed on our database. While the AI explorer is in alpha phase, it's possible that it will sometimes generate SQL queries that don't really work with our database schema.

When that happens, you can refer this guide to get a sense of our database schema and the types of queries you can run on it.

We use ClickHouse as our analytics database, and give you read access to 3 tables: events, users, and companies. This means that you can run SELECT queries on these tables and use ClickHouse functions depending on the data types of the columns you're working with. In most cases, you will use DateTime, String, and Integer functions.

Schema

events

This table contains all the TRACK and PAGE events that you've sent to us.

type

Integer

name

String

timestamp

DateTime

group_id

String

user_id

String

properties

String

context

String

users

This table contains all the users you've sent to us, along with all of their traits and values, and all the groups associated with them.

companies

This table contains all the companies you've sent to us, along with all of their traits and values, and all the users associated with them.

Example queries

Count all track events

SELECT COUNT() FROM events WHERE type = 2

Count all page events

SELECT COUNT() FROM events WHERE type = 0

Count all distinct users

SELECT COUNT(DISTINCT user_id)
FROM users FINAL
WHERE notEmpty(user_id)

Get all users who belong to a company

SELECT DISTINCT user_id
FROM users FINAL
WHERE has(gids, toIntOrHash('A'))

Get all users where trait staff is true

SELECT DISTINCT user_id
FROM users FINAL
WHERE finalizeTraits(traits)['staff'] = 'true'

Get all companies where trait is_paying is true

SELECT DISTINCT group_id
FROM companies FINAL
WHERE group_type = 1 AND finalizeTraits(traits)['is_paying'] = 'true'

Count the events performed by non-staff users between a date range

SELECT
COUNT()
FROM events
WHERE timestamp BETWEEN '2023-01-01' AND '2023-02-01'
AND user_id IN (
SELECT user_id
FROM users FINAL
WHERE finalizeTraits(traits)['staff'] != 'true'
)

Count the events sent with a specific property

SELECT COUNT()
FROM events
WHERE (name = 'signed_up') AND (JSONExtractString(properties, 'source') = 'invited')

πŸ”΄ We do not share any user data with OpenAI. We just share the names of your events and the names of your traits πŸ”΄
​

FAQ:
- What exactly does is_active mean in the generated queries?
It's simply a condition that checks whether that particular event was deleted or not

Did this answer your question?