Writing analytical queries

Analytics accepts exactly one PostgreSQL-flavored SELECT query. Common table expressions, joins, filters, grouping, aggregate functions, ordering, and SQL limits are available through DataFusion.

Use only table and column names returned by the Analytics catalog. The query engine is DataFusion rather than a direct PostgreSQL session, so specialized PostgreSQL functions or extensions may not be supported even when the underlying data lives in PostgreSQL.

Start with a bounded detail query

This is useful for checking names and data types before writing an aggregate:

SELECT
  id,
  number,
  customer_id,
  issue_date,
  total
FROM invoice
ORDER BY issue_date DESC
LIMIT 100

The SQL LIMIT is part of the query. The server’s separate result cap still applies afterward; use both when the report itself should have a deliberate maximum.

Filtering

Use ordinary SQL predicates:

SELECT
  number,
  issue_date,
  total
FROM invoice
WHERE issue_date >= DATE '2026-01-01'
  AND issue_date < DATE '2027-01-01'
  AND status = 'issued'
ORDER BY issue_date

For time intervals, a closed lower boundary and open upper boundary usually avoids overlap between adjacent reports.

Null requires IS NULL or IS NOT NULL:

SELECT COUNT(*) AS invoice_without_customer
FROM invoice
WHERE customer_id IS NULL

Deleted rows need no predicate because the analytical table has already removed them.

Grouping and totals

Group by every selected value that is not aggregated:

SELECT
  status,
  COUNT(*) AS invoice_count,
  SUM(total) AS total_amount,
  AVG(total) AS average_amount
FROM invoice
GROUP BY status
ORDER BY total_amount DESC

Most aggregates ignore null inputs. COUNT(*) counts rows, while COUNT(total) counts only rows where total is not null.

Use COALESCE when the report needs a replacement for a missing aggregate or dimension:

SELECT
  COALESCE(region, 'Unassigned') AS region,
  SUM(total) AS total_amount
FROM invoice
GROUP BY COALESCE(region, 'Unassigned')

Joining tables

Join a link column to the target table’s id:

SELECT
  customer.name AS customer_name,
  COUNT(*) AS invoice_count,
  SUM(invoice.total) AS invoiced_amount
FROM invoice
JOIN customer
  ON customer.id = invoice.customer_id
GROUP BY customer.name
ORDER BY invoiced_amount DESC
LIMIT 100

When joining a header to repeated lines, aggregate the line values rather than repeating a header total once per line:

SELECT
  invoice.number,
  SUM(invoice_line.line_total) AS calculated_total
FROM invoice
JOIN invoice_line
  ON invoice_line.invoice_id = invoice.id
GROUP BY invoice.id, invoice.number
ORDER BY invoice.number

Joins can multiply rows

Joining two one-to-many relationships in one query can multiply combinations and overstate sums. Aggregate each child table in a common table expression first, or verify the expected row cardinality before trusting the total.

Common table expressions

A common table expression can make a multi-stage report easier to verify:

WITH customer_totals AS (
  SELECT
    customer_id,
    SUM(total) AS invoiced_amount
  FROM invoice
  GROUP BY customer_id
)
SELECT
  customer.name,
  customer_totals.invoiced_amount
FROM customer_totals
JOIN customer
  ON customer.id = customer_totals.customer_id
ORDER BY customer_totals.invoiced_amount DESC

This remains one query and is accepted by the read-only query guard.

Dates, money, and exact arithmetic

Use the catalog’s field type, currency, and rounding metadata to format results correctly. A money column’s currency belongs to that column; Analytics does not silently convert different currencies before summing them.

Do not combine money values of different currencies into one meaningful total unless the query explicitly applies an appropriate conversion model. For accounting conversions and authoritative balances, use the accounting workflows rather than reconstructing them casually in a report.

Decimal and money calculations retain their analytical numeric type inside the query. At the external result boundary, exact decimals are represented as strings so clients do not lose precision by converting them to floating point.

Naming result columns

Give calculated columns clear aliases:

SELECT
  issue_date,
  SUM(net_total) AS net_sales,
  SUM(tax_total) AS tax,
  SUM(gross_total) AS gross_sales
FROM invoice
GROUP BY issue_date
ORDER BY issue_date

These aliases become the result column names. They are independent of table-definition aliases and exist only for that query.

Quote an identifier with double quotes when it conflicts with an SQL keyword or when exact case must be preserved. String values use single quotes.

Unsupported query shapes

Analytics is intentionally read-only:

• only one statement is accepted;
• inserts, updates, deletes, DDL, COPY, and other write operations are rejected;
• SELECT INTO is unsupported and must not be used;
• multiple semicolon-separated queries are rejected;
• an empty query is rejected.

The SQL text itself may not exceed 64 KiB.

Continue with Results and limits to understand streaming, truncation, timeouts, types, and resource bounds.