Why Not SQL: The Origin of ScopeQL

We chose to give ScopeDB its own query language. SQL has an extraordinary ecosystem, but its syntax makes ordinary relational operations surprisingly awkward to compose. Once an analysis involves derived values, repeated aggregations, or window results, much of the query exists to satisfy the grammar rather than describe the question.

ScopeQL is our answer: a query language implemented directly in ScopeDB, with explicit data flow and a small set of consistently composable operations. A pipeline is the starting point. The larger goal is to remove the special cases that make a straightforward change to an analysis require a structural rewrite of its query.

This design draws on PRQL, the SaneQL paper, and GoogleSQL's work on pipe syntax. The examples below explain how we apply these ideas in ScopeDB and the choices we made for ScopeQL.

TL;DR

Write in logical order. Start from the data and append operations as the analysis develops.
Compose ordinary operations freely. Aggregate, compute a window, filter its result, and aggregate again without introducing wrapper queries between the steps.
Share concepts across syntax. Grouping and ordering mean the same things for aggregates and windows; filtering remains WHERE wherever it occurs.
Name results where they are produced. Reuse derived columns and retain group keys without repeatedly spelling out the same expressions.
Keep the relational foundation. Queries remain declarative, with the optimizer choosing physical execution strategies.

Suppose we want to find services with at least 100 requests that took longer than one second. The requests table records a service name, a UTC calendar date in day, and a non-NULL integer duration_ms for each request.

The SQL examples use PostgreSQL syntax.

SELECT service, COUNT(*) AS slow_requests
FROM requests
WHERE duration_ms > 1000
GROUP BY service
HAVING COUNT(*) >= 100
ORDER BY slow_requests DESC, service

FROM requests
WHERE duration_ms > 1000
GROUP BY service AGGREGATE COUNT() AS slow_requests
WHERE slow_requests >= 100
ORDER BY slow_requests DESC, service

The ScopeQL version reads in the order of the analysis: find slow requests, count them by service, keep sufficiently large counts, and sort. slow_requests is available immediately after it is defined. Both filters use WHERE, although one examines requests and the other examines groups.

A Critique of SQL

Michael Stonebraker put the frustration plainly in his critique of System R:

My biggest complaint about System R is that the team never stopped to clean up SQL...

Our objection is to the accumulated exceptions in the language. A filter on input rows uses WHERE; a filter on aggregate results uses HAVING; filtering a window result in PostgreSQL requires another query level. The operation is still a filter. The programmer must also know which syntactic location is allowed to see the value being filtered.

Likewise, SELECT is responsible for several different jobs: choosing columns, computing scalar expressions, requesting aggregates, and invoking window functions. Their evaluation depends on other parts of the query. A name written near the beginning may only become usable near the end, and another calculation may need a subquery solely to refer to it.

Adding a second aggregation or filtering a window result often introduces another query level. We want such changes to look like adding another operation.

The relational model already gives us the foundation. A filter consumes a relation and produces a relation; so do projection and aggregation. Their results can become the inputs to further operations. ScopeQL makes this composition visible in the query itself.

ScopeQL To The Rescue

Query Syntax: A Relation at Every Step

A ScopeQL query usually starts with FROM or VALUES. The clauses below can then be combined and repeated as the analysis develops:

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

Each clause operates on the relation available at that point, and later clauses can use the columns it produces. A projection can introduce a value for a subsequent filter; a window can annotate aggregated rows; a join can enrich a result after aggregation.

SELECT is optional. If the current relation already contains the desired columns, that relation is the result. An aggregate returns its group keys and aggregate values; a window retains the input columns and adds its results. Add a final SELECT when the output needs reshaping.

ScopeQL uses logical order to express the analysis. Its planner chooses how to execute the query: it can push down filters, choose join strategies, and use indexes while preserving the query's meaning.

Compose Without Subqueries

A second aggregation should be a second step. Query 13 from the TPC-H benchmark is a useful example, also discussed in the GoogleSQL pipe-syntax paper linked above:

SELECT c_count, COUNT(*) AS custdist
FROM (
    SELECT c_custkey, COUNT(o_orderkey) c_count
    FROM customer
    LEFT OUTER JOIN orders ON c_custkey = o_custkey
        AND o_comment NOT LIKE '%unusual%packages%'
    GROUP BY c_custkey
) AS c_orders
GROUP BY c_count
ORDER BY custdist DESC, c_count DESC

The inner query counts qualifying orders per customer; the outer query counts customers by that order count. To follow the data, read the inside before the outside.

ScopeQL can express the same stages sequentially:

FROM customer
LEFT JOIN orders ON c_custkey = o_custkey
    AND NOT regexp_like(o_comment, '(?s)unusual.*packages')
GROUP BY c_custkey AGGREGATE COUNT(o_orderkey) AS c_count
GROUP BY c_count AGGREGATE COUNT() AS custdist
ORDER BY custdist DESC, c_count DESC

The left join keeps customers with no qualifying orders, and COUNT(o_orderkey) gives them a count of zero. The next aggregation consumes that result directly. There is no wrapper relation to name and no need to jump back to the top of the query to find out what happens next.

Grouping Belongs to the Operation

Consider a report that treats service names case-insensitively. In SQL, the grouping expression and its output are specified in different clauses:

SELECT LOWER(service) AS service_name, COUNT(*) AS request_count
FROM requests
GROUP BY LOWER(service)
ORDER BY service_name

ScopeQL defines and names the group key at the aggregation:

FROM requests
GROUP BY lower(service) AS service_name
AGGREGATE COUNT() AS request_count
ORDER BY service_name

PostgreSQL also allows GROUP BY service_name to refer to the SELECT alias, avoiding repetition of the expression. In ScopeQL, the aggregation defines its keys and values together, and its result contains both.

GROUP BY and WITHIN GROUP ORDER BY attach grouping and ordering to an operation. These modifiers have the same form for aggregates and windows:

[ GROUP BY ... ] [ WITHIN GROUP ORDER BY ... ] AGGREGATE ...
[ GROUP BY ... ] [ WITHIN GROUP ORDER BY ... ] WINDOW ...

The distinction is in what they produce. AGGREGATE reduces each group to one row. WINDOW computes values associated with each input row and keeps those rows available. Without GROUP BY, the operation considers the entire input as one group. An ordered aggregate such as first or array_agg uses the same ordering modifier as a window such as lag.

SQL expresses related concepts through several forms, including GROUP BY, window PARTITION BY, and ordering attached to individual function calls. ScopeQL makes grouping and ordering properties of the operation. We think the similarity between these operations deserves to be reflected in their syntax.

Aggregate, Window, Filter, Aggregate Again

Now suppose we want to find services with at least two recorded days when their mean request duration was at least twice that of their previous recorded day.

First compute daily means. Then compare adjacent observations within each service. Keep the increases that meet the threshold, and count them by service. In PostgreSQL, we can write:

WITH daily AS (
    SELECT service, day, AVG(duration_ms) AS mean_ms
    FROM requests
    GROUP BY service, day
), compared AS (
    SELECT service, day, mean_ms,
        LAG(mean_ms) OVER (
            PARTITION BY service ORDER BY day
        ) AS previous_ms
    FROM daily
)
SELECT service, COUNT(*) AS spike_days
FROM compared
WHERE previous_ms > 0 AND mean_ms >= previous_ms * 2
GROUP BY service
HAVING COUNT(*) >= 2
ORDER BY spike_days DESC, service

The ScopeQL version keeps all four stages in one pipeline:

FROM requests
GROUP BY service, day AGGREGATE AVG(duration_ms) AS mean_ms
GROUP BY service WITHIN GROUP ORDER BY day
WINDOW lag(mean_ms) AS previous_ms
WHERE previous_ms > 0 AND mean_ms >= previous_ms * 2
GROUP BY service AGGREGATE COUNT() AS spike_days
WHERE spike_days >= 2
ORDER BY spike_days DESC, service

The first aggregation produces one row per service and day. The window adds previous_ms to those rows without collapsing them. The next filter can use both means directly. Finally, another aggregation counts the qualifying days, followed by another ordinary filter.

lag sees all daily means. Filtering out low-latency days beforehand would change which observation it reads. The first row for each service gets NULL for previous_ms; previous_ms > 0 keeps only rows with a positive baseline. If the records jump from Monday to Wednesday, Wednesday is compared with Monday.

The SQL version uses two named intermediate relations and switches from WHERE to HAVING for the final condition. In ScopeQL, each result is available to the next operation without an extra wrapper.

WITHIN GROUP ORDER BY day determines which row lag reads. The final ORDER BY controls presentation.

Name an Expression Once

Composability also matters within a row. Suppose a usage report adds read and write traffic, displays the total in MiB, and checks whether it exceeds a 1 GiB allowance. In PostgreSQL, reusing the computed name requires another query level:

SELECT account_id,
    total_bytes / 1048576.0 AS total_mib,
    total_bytes > 1073741824 AS over_quota
FROM (
    SELECT account_id, read_bytes + write_bytes AS total_bytes
    FROM transfer_usage
) AS measured

In ScopeQL, introduce the value with a projection and use it in the next one:

FROM transfer_usage
SELECT *, read_bytes + write_bytes AS total_bytes
SELECT account_id,
    total_bytes / 1048576.0 AS total_mib,
    total_bytes > 1073741824 AS over_quota

Repeating the arithmetic would also work in SQL. But as a calculation grows to include nested fields, fallback rules, or billing adjustments, repetition becomes a maintenance problem. Naming the value gives the business rule one place to change.

Projection can also adjust an existing set of columns without enumerating every column that passes through:

SELECT *, expr AS name adds a derived column.
SELECT * EXCLUDE (name) removes a column from the current relation.
SELECT * REPLACE (expr AS name) replaces a column's value.
SELECT * RENAME (a TO b) renames a column while carrying the others forward.

For example, a request report can convert its unit, remove the old field, and give the new field its report-facing name:

FROM requests
SELECT *, duration_ms / 1000.0 AS duration_s
SELECT * EXCLUDE (duration_ms) RENAME (duration_s TO latency_s)
WHERE latency_s > 1,
SELECT service, day, latency_s,

ScopeQL accepts trailing commas in expression lists, including these projections and predicates. You can add or comment out the final item without changing the punctuation on its neighbor.

Extending Queries and the Language

An analysis rarely ends at its first result. After finding slow services, we may want the team responsible for each one. A join can consume the aggregated relation directly:

FROM requests
WHERE duration_ms > 1000
GROUP BY service AGGREGATE COUNT() AS slow_requests
WHERE slow_requests >= 100
LEFT JOIN service_owners AS owners ON service = owners.name
SELECT service, owners.team, slow_requests
ORDER BY slow_requests DESC, service

Here, service_owners has one row per service name. The query counts requests first, then looks up the owning team for each service. The left join preserves an unassigned service, with a NULL team.

These composition rules also guide language extensions. A new relational operation defines its input columns and output relation. Functions use the existing expression syntax; order-sensitive aggregates use the existing grouping and ordering modifiers.

A query builder can likewise extend a pipeline at the point where its input columns are available.

Flexible Values Use the Same Query Operations

Expression design matters alongside clause design. ScopeDB's ANY type can carry objects, arrays, and scalar values, so an event can keep varying attributes inside an object while retaining stable columns such as service.

For example, suppose an events table stores HTTP attributes in its var object. Extracting a duration gives us a column that can participate in the same filters and aggregates:

FROM events
SELECT service, TRY(var['http']['duration_ms']::float) AS duration_ms
WHERE duration_ms IS NOT NULL
GROUP BY service AGGREGATE AVG(duration_ms) AS mean_ms
ORDER BY service

Here, producers record durations in milliseconds. The cast converts them to numbers; TRY returns NULL for failed conversions, and the filter removes those rows along with missing values. Schema On The Fly explores how these flexible fields evolve into a shared schema.

FAQ

What about interoperability with SQL?

The SQL ecosystem matters, especially tools that generate SQL. A translator from SQL to ScopeQL could support those tools because both languages share a relational model. Translating their queries would require mapping dialect-specific functions, NULL behavior, ordering, and type conversions.

Other languages have similar features. Why build ScopeQL?

The projects cited at the beginning informed our design. GoogleSQL emphasizes adding pipe syntax while retaining compatibility with existing SQL. PRQL provides a language that compiles to SQL. We already had ScopeDB's relational engine and chose to implement ScopeQL directly against it, making composable operations the primary way to express a query.

That gives us control over query syntax, expression semantics, flexible values, and database commands. Users must learn a new language, and SQL-generating tools need integration work.

What about the rest of the language?

ScopeQL keeps familiar SQL-style commands for table definitions and maintenance. A query can also finish with INSERT INTO, using its result as the rows to write. For example, with a matching daily_latency destination table:

FROM requests
GROUP BY service, day AGGREGATE AVG(duration_ms) AS mean_ms
INSERT INTO daily_latency

The ScopeQL reference documents the full query and command syntax.