Query clauses and features

Clauses and query constructs you can use to shape a segment query. Every segment query must return a distinct_id column.

WHERE / ORDER BY / LIMIT / OFFSET

The core row-shaping clauses. WHERE filters rows before grouping, ORDER BY sorts the result, LIMIT caps the number of rows returned, and OFFSET skips a number of leading rows (useful with ORDER BY for paging). Syntax

SELECT ...
FROM ...
WHERE condition
ORDER BY expression [ASC | DESC]
LIMIT count
OFFSET start

Example

Most recent purchasers in the last 30 days

SELECT distinct_id
FROM events
WHERE event_name = 'PURCHASE'
  AND requested_at >= now() - interval '30 days'
ORDER BY requested_at DESC
LIMIT 100
OFFSET 0

GROUP BY … HAVING

Aggregates rows into groups and then filters those groups with a HAVING condition. Unlike WHERE, HAVING can reference aggregate results such as count(*). Syntax

SELECT ...
FROM ...
GROUP BY column_list
HAVING aggregate_condition

Example

Users with more than three failed payments

SELECT distinct_id
FROM events
WHERE event_name = 'PAYMENT_FAILED'
  AND requested_at >= NOW() - INTERVAL '30 days'
GROUP BY distinct_id
HAVING count(*) > 3

IN (subquery)

Keeps rows whose value appears in the result of a subquery. A common way to include users who match a condition captured in another table. Syntax

SELECT ...
WHERE column IN (SELECT ... FROM ...)

Example

Users who have triggered a purchase

SELECT distinct_id
FROM users
WHERE distinct_id IN (
  SELECT distinct_id
  FROM events
  WHERE event_name = 'PURCHASE'
    AND requested_at >= NOW() - INTERVAL '30 days'
)

JOIN / LEFT JOIN

Combines rows from two tables on a join condition. A plain (inner) JOIN keeps only matching rows; a LEFT JOIN keeps every row from the left table. Unmatched right-side columns are filled with their type’s default value (an empty string for text), not NULL, so IS NULL can’t find rows without a match; use NOT IN (subquery) to build anti-joins. Syntax

SELECT ...
FROM left_table
[LEFT] JOIN right_table ON join_condition

Example

Pro-plan users who opened the app (inner join)

SELECT DISTINCT u.distinct_id
FROM users u
JOIN events e ON e.distinct_id = u.distinct_id
WHERE u.user_properties->>'plan' = 'pro'
  AND e.event_name = 'APP_OPENED'
  AND e.requested_at >= NOW() - INTERVAL '30 days'

Users with no purchase in the last 30 days (NOT IN anti-join)

SELECT distinct_id
FROM users
WHERE distinct_id NOT IN (
  SELECT distinct_id
  FROM events
  WHERE event_name = 'PURCHASE'
    AND requested_at >= NOW() - INTERVAL '30 days'
)

Derived table (subquery in FROM)

A subquery placed in the FROM clause and given an alias, so you can filter or aggregate over its result as if it were a table. Syntax

SELECT ...
FROM (SELECT ...) AS alias

Example

Users with at least ten logins

SELECT distinct_id
FROM (
  SELECT distinct_id, count(*) AS logins
  FROM events
  WHERE event_name = 'LOGIN'
    AND requested_at >= NOW() - INTERVAL '30 days'
  GROUP BY distinct_id
) AS login_stats
WHERE logins >= 10

CTE (WITH … AS)

A common table expression defines a named, temporary result set that you can reference later in the same query. Use it to break a complex segment into readable, composable steps. Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT ... FROM cte_name

Example

Users with more than five purchases

WITH purchase_counts AS (
  SELECT distinct_id, count(*) AS purchases
  FROM events
  WHERE event_name = 'PURCHASE'
    AND requested_at >= NOW() - INTERVAL '30 days'
  GROUP BY distinct_id
)
SELECT distinct_id
FROM purchase_counts
WHERE purchases > 5

DISTINCT

Removes duplicate rows from the result set, returning each unique combination of the selected columns once. Syntax

SELECT DISTINCT column_list
FROM ...

Example

Unique users who opened the app

SELECT DISTINCT distinct_id
FROM events
WHERE event_name = 'APP_OPENED'
  AND requested_at >= NOW() - INTERVAL '30 days'

UNION / UNION ALL

Combines the rows of two or more queries with matching columns. UNION removes duplicate rows (it is treated as UNION DISTINCT on ClickHouse), while UNION ALL keeps every row, including duplicates. Syntax

SELECT ...
UNION [ALL]
SELECT ...

Example

Users who purchased or started a subscription

SELECT distinct_id
FROM events
WHERE event_name = 'PURCHASE'
  AND requested_at >= NOW() - INTERVAL '30 days'
UNION
SELECT distinct_id
FROM events
WHERE event_name = 'SUBSCRIPTION_STARTED'
  AND requested_at >= NOW() - INTERVAL '30 days'

DISTINCT ON

Returns the first row for each distinct value of the given expressions. Pair it with ORDER BY to control which row is kept per group (for example, the most recent event per user). Syntax

SELECT DISTINCT ON (expression) column_list
FROM ...
ORDER BY expression, sort_key

Example

One row per user, keyed to their latest event

SELECT DISTINCT ON (distinct_id) distinct_id
FROM events
WHERE requested_at >= NOW() - INTERVAL '30 days'
ORDER BY distinct_id, requested_at DESC

GROUP BY CUBE

Produces grouping sets for every combination of the listed expressions, including subtotal rows where a grouped expression is rolled up to NULL. Filter with HAVING to keep only the rows you want in the segment. Syntax

SELECT ...
FROM ...
GROUP BY CUBE (expression_1, expression_2)

Example

Users grouped across page dimensions, keeping per-user rows

SELECT DISTINCT distinct_id
FROM events
WHERE event_name = 'PAGE_VIEW'
  AND requested_at >= NOW() - INTERVAL '30 days'
GROUP BY CUBE (distinct_id, properties->>'page')
HAVING distinct_id IS NOT NULL

GROUP BY ROLLUP

Produces grouping sets in a hierarchy from most to least detailed, adding subtotal rows as each trailing expression is rolled up to NULL. Filter with HAVING to keep only the rows you want in the segment. Syntax

SELECT ...
FROM ...
GROUP BY ROLLUP (expression_1, expression_2)

Example

Users grouped by purchase category, keeping per-user rows

SELECT DISTINCT distinct_id
FROM events
WHERE event_name = 'PURCHASE'
  AND requested_at >= NOW() - INTERVAL '30 days'
GROUP BY ROLLUP (distinct_id, properties->>'category')
HAVING distinct_id IS NOT NULL

GETTING STARTED

CORE CONCEPTS

WORKFLOW BUILDER

Notification Inbox

PREFERENCE CENTRE

VENDOR INTEGRATION GUIDE

INTEGRATIONS

MONITORING & DEBUGGING

MANAGE YOUR ACCOUNT

WHERE / ORDER BY / LIMIT / OFFSET

GROUP BY … HAVING

IN (subquery)

JOIN / LEFT JOIN

Derived table (subquery in FROM)

CTE (WITH … AS)

DISTINCT

UNION / UNION ALL

DISTINCT ON

GROUP BY CUBE

GROUP BY ROLLUP

​WHERE / ORDER BY / LIMIT / OFFSET

​GROUP BY … HAVING

​IN (subquery)

​JOIN / LEFT JOIN

​Derived table (subquery in FROM)

​CTE (WITH … AS)

​DISTINCT

​UNION / UNION ALL

​DISTINCT ON

​GROUP BY CUBE

​GROUP BY ROLLUP

WHERE / ORDER BY / LIMIT / OFFSET

GROUP BY … HAVING

IN (subquery)

JOIN / LEFT JOIN

Derived table (subquery in FROM)

CTE (WITH … AS)

DISTINCT

UNION / UNION ALL

DISTINCT ON

GROUP BY CUBE

GROUP BY ROLLUP