Query syntax | ScopeDB Docs

ScopeQL queries are pipelines. Each clause takes the rows produced by the preceding clause, so you can filter a calculated column or aggregate a filtered result without wrapping it in a subquery.

Parenthesized queries can also start a pipeline, as in the UNION ALL example.

The outline below shows a common clause order. Clauses such as SELECT and WHERE can appear more than once.

[ FROM ... [ SAMPLE ... PERCENT ] | VALUES ... ]
[ [ INNER | LEFT | RIGHT | FULL ] JOIN ... [ ON ... ] ]
[ SELECT ... ]
[ WHERE ... ]
[ DISTINCT ON ... [ BY ... ] ]
[ [ GROUP BY ... ] [ WITHIN GROUP ORDER BY ... ] WINDOW ... ]
[ [ GROUP BY ... ] [ WITHIN GROUP ORDER BY ... ] AGGREGATE ... ]
[ ORDER BY ... ]
[ LIMIT ... [ OFFSET ... ] ]

Sample data

The table examples on this page use four request events. Run these two statements once before trying them:

CREATE TABLE reference_requests (
    event_id string,
    event_time timestamp,
    service string,
    status int,
    latency_ms int
)

VALUES
    ('req-1', '2026-06-01T10:00:00Z'::timestamp, 'checkout', 200, 120),
    ('req-2', '2026-06-01T10:00:10Z'::timestamp, 'checkout', 504, 1800),
    ('req-3', '2026-06-01T10:00:20Z'::timestamp, 'catalog', 200, 40),
    ('req-4', '2026-06-01T10:00:30Z'::timestamp, 'checkout', 200, 180)
INSERT INTO reference_requests

Examples starting with VALUES include their own data and need no table setup.

FROM

FROM reads rows from a table or view. Use schema.table to qualify a table name and AS to give the source an alias.

FROM reference_requests AS requests
SELECT requests.event_id AS event_id, requests.service AS service
ORDER BY event_id

+----------+----------+
| event_id | service  |
+----------+----------+
| req-1    | checkout |
| req-2    | checkout |
| req-3    | catalog  |
| req-4    | checkout |
+----------+----------+

FROM reference_requests by itself returns all columns. Without ORDER BY, the row order is not guaranteed.

VALUES

VALUES supplies rows directly in a query. Columns are named $0, $1, and so on, counting from 0. Use SELECT to assign meaningful names:

VALUES ('checkout', 120), ('catalog', 40)
SELECT $0 AS service, $1 AS latency_ms

+----------+------------+
| service  | latency_ms |
+----------+------------+
| checkout | 120        |
| catalog  | 40         |
+----------+------------+

The first row determines each column’s type. If the first value is NULL, give it a type, such as NULL::string. Every row must have the same number of values.

SELECT

SELECT chooses, renames, or calculates columns. Later clauses can use the names assigned by AS.

Convert request latency from milliseconds to seconds:

FROM reference_requests
SELECT event_id, latency_ms / 1000.0 AS latency_seconds
ORDER BY event_id

+----------+-----------------+
| event_id | latency_seconds |
+----------+-----------------+
| req-1    | 0.12            |
| req-2    | 1.8             |
| req-3    | 0.04            |
| req-4    | 0.18            |
+----------+-----------------+

Use * to keep all current columns, including when adding a calculated column:

VALUES ('req-1', 120), ('req-2', 1800)
SELECT $0 AS event_id, $1 AS latency_ms
SELECT *, latency_ms >= 1000 AS slow

+----------+------------+-------+
| event_id | latency_ms | slow  |
+----------+------------+-------+
| req-1    | 120        | false |
| req-2    | 1800       | true  |
+----------+------------+-------+

You can select a column by its zero-based position, such as SELECT $0. A SELECT without an input source can evaluate expressions directly, for example SELECT now() AS query_time.

WHERE

WHERE <predicate> keeps rows for which the predicate is TRUE. FALSE and NULL both exclude a row. Combine conditions with logical operators.

Find checkout requests that returned a server error:

FROM reference_requests
WHERE service = 'checkout' AND status >= 500
SELECT event_id, status, latency_ms
ORDER BY event_id

+----------+--------+------------+
| event_id | status | latency_ms |
+----------+--------+------------+
| req-2    | 504    | 1800       |
+----------+--------+------------+

Place WHERE after SELECT, AGGREGATE, or WINDOW to filter their results. WHERE also limits the rows affected by UPDATE and DELETE.

JOIN

JOIN combines the current rows with a table, view, or subquery. The clauses before JOIN form its left side.

Syntax

[ INNER | LEFT | RIGHT | FULL ] JOIN <right_source> [ AS <alias> ] [ ON <predicate> ]

Join type	Rows returned
`INNER`	Matching rows from both sides. This is the default.
`LEFT`	All left rows, with NULL right columns when no match exists.
`RIGHT`	All right rows, with NULL left columns when no match exists.
`FULL`	Matches and unmatched rows from both sides; missing columns are NULL.

Use aliases to distinguish columns with the same name. If ON is omitted, every left row is paired with every right row. Multiple matching rows produce multiple output rows.

Example

Add a responsible team to each request. The service metadata here contains only checkout, so a left join keeps the catalog request with a NULL team:

FROM reference_requests AS requests
LEFT JOIN (
    VALUES ('checkout', 'payments')
    SELECT $0 AS service, $1 AS team
) AS services ON requests.service = services.service
SELECT requests.event_id AS event_id, requests.service AS service, services.team AS team
ORDER BY event_id

+----------+----------+----------+
| event_id | service  | team     |
+----------+----------+----------+
| req-1    | checkout | payments |
| req-2    | checkout | payments |
| req-3    | catalog  | NULL     |
| req-4    | checkout | payments |
+----------+----------+----------+

Change LEFT JOIN to JOIN to keep only requests with a matching team. You can add another JOIN after any clause that produces rows.

AGGREGATE

AGGREGATE calculates summary values. Without GROUP BY, it returns one row for the entire input. With GROUP BY, it returns one row per combination of group keys.

Syntax

[ GROUP BY <expr> [ AS <alias> ] [, ...] ]
[ WITHIN GROUP ORDER BY <order_item> [, ...] ]
AGGREGATE <function_call> [ AS <alias> ] [, ...]

Count requests and calculate average latency for each service:

FROM reference_requests
GROUP BY service
AGGREGATE count() AS requests, avg(latency_ms) AS avg_ms
ORDER BY service

+----------+----------+--------+
| service  | requests | avg_ms |
+----------+----------+--------+
| catalog  | 1        | 40.0   |
| checkout | 3        | 700.0  |
+----------+----------+--------+

Add group keys to split the result further, or omit GROUP BY for a total. For example, group requests by service and status:

FROM reference_requests
GROUP BY service, status
AGGREGATE count() AS requests
ORDER BY service, status

+----------+--------+----------+
| service  | status | requests |
+----------+--------+----------+
| catalog  | 200    | 1        |
| checkout | 200    | 2        |
| checkout | 504    | 1        |
+----------+--------+----------+

For order-sensitive aggregates such as first, last, and array_agg, use WITHIN GROUP ORDER BY before AGGREGATE. An ORDER BY after the aggregation sorts the result rows. See aggregate functions for empty-input and NULL behavior.

WINDOW

WINDOW calculates a value for each input row while keeping the original columns. GROUP BY creates independent windows; without it, all input rows belong to one window. WITHIN GROUP ORDER BY sets the row order inside each window.

Syntax

[ GROUP BY <expr> [, ...] ]
[ WITHIN GROUP ORDER BY <order_item> [, ...] ]
WINDOW <function_call> [ AS <alias> ] [, ...]

Measure the time between consecutive requests to each service:

FROM reference_requests
GROUP BY service
WITHIN GROUP ORDER BY event_time, event_id
WINDOW lag(event_time) AS previous_time
SELECT event_id, service, event_time - previous_time AS since_previous
ORDER BY event_id

+----------+----------+----------------+
| event_id | service  | since_previous |
+----------+----------+----------------+
| req-1    | checkout | NULL           |
| req-2    | checkout | 10s            |
| req-3    | catalog  | NULL           |
| req-4    | checkout | 20s            |
+----------+----------+----------------+

The first request in each service has no previous timestamp, so its interval is NULL. See window functions for lag, lead, and row_number.

DISTINCT ON

DISTINCT ON keeps one row for each combination of key values. Use BY to specify which row to keep. Without BY, or when its sort keys tie, the selected row is not guaranteed.

Syntax

DISTINCT ON <expr> [, ...]
[ BY <order_item> [, ...] ]

Each BY item uses the same syntax as an ORDER BY item. BY chooses a row within each group; a separate ORDER BY sorts the final result.

Keep the latest request for each service, using the event ID to break timestamp ties:

FROM reference_requests
DISTINCT ON service BY event_time DESC, event_id DESC
SELECT event_id, service, status
ORDER BY service

+----------+----------+--------+
| event_id | service  | status |
+----------+----------+--------+
| req-3    | catalog  | 200    |
| req-4    | checkout | 200    |
+----------+----------+--------+

To remove duplicate combinations without choosing between other fields, select only the keys:

FROM reference_requests
SELECT service, status
DISTINCT ON service, status
ORDER BY service, status

+----------+--------+
| service  | status |
+----------+--------+
| catalog  | 200    |
| checkout | 200    |
| checkout | 504    |
+----------+--------+

ORDER BY

ORDER BY sorts the current rows by one or more expressions. Use column names, aliases from an earlier clause, or positional references such as $0.

Syntax

ORDER BY <expr> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, ...]

ASC sorts from lowest to highest and is the default. NULL values come last.
DESC sorts from highest to lowest. NULL values come first.
NULLS FIRST or NULLS LAST overrides the default NULL placement.
Rows with equal sort keys have no guaranteed order. Add another key when the order matters.

List the slowest requests first, placing missing durations last:

VALUES ('req-1', 120), ('req-2', NULL), ('req-3', 1800)
SELECT $0 AS event_id, $1 AS latency_ms
ORDER BY latency_ms DESC NULLS LAST, event_id

+----------+------------+
| event_id | latency_ms |
+----------+------------+
| req-3    | 1800       |
| req-1    | 120        |
| req-2    | NULL       |
+----------+------------+

LIMIT

LIMIT returns at most the requested number of rows. OFFSET skips rows before taking that limit; it defaults to 0. Both values must be non-negative integer constants.

Syntax

LIMIT <count> [ OFFSET <start> ]

Find the two slowest requests:

FROM reference_requests
ORDER BY latency_ms DESC, event_id
LIMIT 2
SELECT event_id, latency_ms

+----------+------------+
| event_id | latency_ms |
+----------+------------+
| req-2    | 1800       |
| req-4    | 180        |
+----------+------------+

Use LIMIT 2 OFFSET 2 for the next two rows. Put ORDER BY before LIMIT to define which rows are selected.

SAMPLE

SAMPLE randomly includes rows from a table with a given probability. The percentage can range from 0 to 100, inclusive. The returned count can vary; it is not an exact percentage of the row count.

Syntax

FROM <table_name> SAMPLE <probability> PERCENT

Sample approximately 10% of request events:

FROM reference_requests SAMPLE 10 PERCENT

On this four-row table, the sample may be empty. A larger table makes sampling more useful for inspecting representative rows.