Better than SQL

Introduction

"SQL is the universal language of data. Why did Holistics have to create a new query language instead of just using SQL?"

SQL is powerful, universal, and battle-tested. It's the right tool for ad-hoc data exploration, data transformation, and building data pipelines. AQL compiles to SQL under the hood — it's designed to complement SQL, not replace it.

But SQL was designed in the 1970s for querying tables, not for defining and composing business metrics. When analytics teams try to build a reusable metrics layer using SQL, they run into fundamental limitations that no amount of CTEs or window functions can fix.

This document explains the limitations of SQL for metrics-based analytics, and how AQL addresses those limitations.

SQL buries business intent in mechanical complexity

SQL requires you to solve a series of mechanical puzzles — which tables to join, what keys to use, what to GROUP BY — before you can express the actual business question. The intent gets buried under plumbing.

Joins, GROUP BY, and ORDER BY for every query

Consider a straightforward business question: "What is revenue by product category?"

SELECT
  c.name AS category,
  SUM(oi.quantity * p.price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY c.name
ORDER BY revenue DESC

Out of 7 lines, only one (SUM(oi.quantity * p.price)) is actual business logic. The rest is mechanical plumbing: 2 JOINs, a GROUP BY, and an ORDER BY. And if someone later wants revenue by merchant instead of category, they need to rewrite the query — different joins, different GROUP BY — even though the business logic hasn't changed at all.

CTEs for anything non-trivial

The moment your analysis goes beyond a single aggregation, SQL pushes you into CTEs and subqueries. Here's what it takes to answer "How does each month's sales compare to the average?":

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) as sales
  FROM orders
  GROUP BY 1
),
avg_monthly AS (
  SELECT AVG(sales) as avg_sales FROM monthly_sales
)
SELECT
  month,
  sales,
  sales - (SELECT avg_sales FROM avg_monthly) as variance
FROM monthly_sales

The business question is one sentence. The SQL is 14 lines across two CTEs.

AQL expresses analytics intent directly

AQL separates the what from the how. Relationships between tables are defined once in your dataset. Queries focus purely on what you want to know.

Here's the same revenue-by-category question:

// Relationships defined once in the dataset — no need to repeat in queries
// relationship(order_items.product_id > products.id, true)
// relationship(products.category_id > categories.id, true)

metric revenue {
  definition: @aql sum(order_items, order_items.quantity * products.price) ;;
}

No joins. The same revenue metric works whether you slice by category, merchant, country, or time period — just change the dimension in your exploration. The metric definition stays the same.

And the monthly variance? Three named, reusable components instead of a monolithic CTE query:

metric sales {
  definition: @aql sum(orders.amount) ;;
}

metric avg_monthly_sales {
  definition: @aql
    orders
    | group(orders.created_at | month())
    | select(sales)
    | avg(sales)
  ;;
}

metric sales_variance {
  definition: @aql sales - avg_monthly_sales ;;
}

Each metric has a name, a clear definition, and can be reused independently. The pipe operator (|) chains operations so the logic reads like a sentence: "take orders, group by month, calculate sales, then average them."

SQL metrics are trapped inside queries

In SQL, a metric like "revenue" is just an expression — SUM(amount) — embedded inside a query. It has no name you can reference later, no way to compose it with other metrics, no single source of truth. When another team member needs a variant, they copy-paste and modify.

The copy-paste problem

-- Dashboard query 1: Total revenue
SELECT SUM(oi.quantity * p.price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id;

-- Dashboard query 2: Delivered revenue (copy-paste + WHERE)
SELECT SUM(oi.quantity * p.price) AS delivered_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'delivered';

-- Dashboard query 3: Revenue per order (copy-paste + different aggregation)
SELECT
  SUM(oi.quantity * p.price) / COUNT(DISTINCT oi.order_id) AS revenue_per_order
FROM order_items oi
JOIN products p ON oi.product_id = p.id;

The definition of "revenue" is now scattered across three queries. If the formula changes — say, you need to exclude refunds — you must hunt down and update every copy. Miss one, and your dashboards silently disagree with each other.

AQL metrics are first-class, composable objects

In AQL, metrics are named objects that can be referenced, filtered, and composed — just like functions in a programming language.

// Single source of truth
metric revenue {
  definition: @aql sum(order_items, order_items.quantity * products.price) ;;
}

// Filtered variants — references the original, not a copy
metric delivered_revenue {
  definition: @aql revenue | where(orders.status == 'delivered') ;;
}

metric cancelled_revenue {
  definition: @aql revenue | where(orders.status == 'cancelled') ;;
}

// Derived metrics — composed from existing building blocks
metric revenue_per_order {
  definition: @aql safe_divide(revenue, count(orders.id)) ;;
}

When the definition of revenue changes, all derived metrics update automatically. This isn't string substitution — AQL understands the semantic structure. You build a library of metrics that grows more powerful over time, rather than a collection of isolated queries that drift apart.

SQL lacks built-in analytics patterns

Percent of total, year-over-year comparisons, and nested aggregations are bread-and-butter analytics operations. Yet in SQL, each one requires a verbose, error-prone pattern — window functions, self-joins, or deeply nested subqueries. These patterns are so common that they should be built-in primitives, not puzzles to solve from scratch every time.

Percent of total

"What percentage of total revenue does each product contribute?"

SELECT
  p.name,
  SUM(oi.quantity * p.price) AS revenue,
  SUM(oi.quantity * p.price) * 100.0 /
    SUM(SUM(oi.quantity * p.price)) OVER () AS pct_of_total
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.name

SUM(SUM(...)) OVER () — a nested window function over an aggregate. Many analysts struggle to write this correctly, and it's nearly impossible to read at a glance.

Year-over-year comparison

"What is the YoY revenue change by month?"

WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT
  curr.month,
  curr.revenue,
  prev.revenue AS prev_year_revenue,
  (curr.revenue - prev.revenue) * 100.0 / prev.revenue AS yoy_change
FROM monthly curr
LEFT JOIN monthly prev
  ON curr.month = prev.month + INTERVAL '1 year'

A self-join on a CTE with date arithmetic — fragile and easy to get wrong. Want month-over-month too? Write it all again with a different interval.

Nested aggregation

"What is the average number of new customers per month?"

SELECT AVG(monthly_count) FROM (
  SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS monthly_count
  FROM users GROUP BY 1
) sub

Even this relatively simple case requires a subquery. More complex nested aggregations quickly become deeply nested and unreadable.

AQL has built-in analytical functions

The same three questions in AQL:

Percent of total — one line with of_all():

metric pct_of_total {
  definition: @aql
    safe_divide(revenue * 100.0, revenue | of_all(products))
  ;;
}

of_all(products) is a declarative statement: "calculate revenue ignoring the product dimension." No window functions, no nested aggregates.

Year-over-year — one function with relative_period():

metric revenue_last_year {
  definition: @aql
    revenue | relative_period(orders.created_at, interval(-1 year))
  ;;
}

metric yoy_change {
  definition: @aql
    safe_divide((revenue - revenue_last_year) * 100.0, revenue_last_year)
  ;;
}

The same metric works at any time granularity — daily, monthly, quarterly. Want month-over-month? Just change interval(-1 year) to interval(-1 month).

Nested aggregation — a pipe chain that reads like a sentence:

metric avg_monthly_new_customers {
  definition: @aql
    users
    | group(users.created_at | month())
    | select(monthly_count: count(users.id))
    | avg(monthly_count)
  ;;
}

"Take users, group by month, count them, then average." Two levels of aggregation in four lines, no subqueries.

Conclusion

SQL remains the right tool for ad-hoc data exploration and data transformation. But when it comes to building a scalable analytics layer — where metrics need to be defined once, reused everywhere, and composed into increasingly sophisticated analyses — SQL's limitations become structural barriers, not just inconveniences.

We designed AQL to systematically address every limitation of SQL for metrics-based analytics. Where SQL falls short because it was designed for querying tables, AQL succeeds because it was designed for defining and composing metrics, built on three foundational principles:

• Intent over mechanics — express analytics questions directly without specifying joins, GROUP BY, or query structure. Relationships are defined once; queries focus on what you want to know.
• Metrics as first-class objects — metrics are named, reusable, composable building blocks with a single source of truth, not disposable expressions scattered across queries.
• Built-in analytics primitives — common patterns like percent of total, time comparisons, and nested aggregations are single functions, not verbose SQL workarounds.

Your analysts deserve something better than copy-pasting SQL.

tldr: AQL vs SQL

Category	SQL	AQL
Query Construction	Must specify joins, GROUP BY, ORDER BY for every query. Business intent buried in mechanical plumbing.	Relationships defined once. Queries express only what you want to know.
Metric Reusability	Metrics are anonymous expressions embedded in queries. Must copy-paste and modify for each use.	Metrics are named, first-class objects. Define once, reference and compose freely.
Metric Composition	No native way to build metrics from other metrics. Each variant is a standalone query.	Pipe operator enables chaining: revenue | where(...), revenue | of_all(...). New metrics compose from existing ones.
Percent of Total	Requires nested window functions: SUM(SUM(...)) OVER ()	Single function: revenue | of_all(dimension)
Time Comparisons	Self-joins on CTEs with date arithmetic. Must rewrite for each comparison period.	Single function: revenue | relative_period(date, interval(-1 year))
Nested Aggregations	Subqueries or CTEs for each aggregation level. Deep nesting becomes unreadable.	Pipe chain: model | group(...) | select(...) | aggregate()
Single Source of Truth	Metric definitions scattered across N queries. Changes require finding and updating all copies.	One definition; all derived metrics update automatically.

Bottom Line: SQL is a table query language trying to serve as a metrics layer. AQL is a metrics-first language designed specifically for analytics composition.