Skip to content

ASQL: SQL with Analytics Built In

Analytic SQL (ASQL) is a query language designed for analytics work. It uses a pipe syntax that flows top-to-bottom, includes built-in helpers for common analytics patterns (like dbt_utils macros), and handles cross-dialect differences automatically.

Think of it as SQL with analytics superpowers. Write ASQL, get standard SQL for any dialect.

Status: Experimental. GitHub

Pipe Syntax: Queries That Read Like Steps

ASQL queries flow top-to-bottom in execution order:

ASQL Playground 
from sales
  where year(date) = 2024
  group by region (sum(amount) as revenue)
  where revenue > 1000
  order by -revenue
  limit 10

Compare to SQL's inside-out structure:

SELECT region, SUM(amount) as revenue  -- 5th
FROM sales                              -- 1st
WHERE YEAR(date) = 2024                 -- 2nd
GROUP BY region                         -- 3rd
HAVING SUM(amount) > 1000               -- 4th
ORDER BY revenue DESC                   -- 6th
LIMIT 10;                               -- 7th

This matters most for analytics work—dbt models, ELT pipelines, dashboards—where queries are written once and read many times. Linear flow makes code review, debugging, and refactoring significantly easier.

CTEs When You Need Them

When you need to reuse intermediate results, use stash as:

ASQL Playground 
from users
  where is_active
  stash as active_users

  group by country (# as total)

Built-in Analytics Helpers

ASQL includes common analytics patterns that you'd normally get from dbt_utils or custom macros—built right into the language.

One-Line Window Functions

Common analytics operations become single lines:

ASQL Playground 
-- Most recent order per customer
from orders
  per customer_id first by -order_date

-- Previous period comparison
from monthly_sales
  select month, revenue, prior(revenue) as prev_revenue

-- Running totals
from daily_sales
  select date, revenue, running_sum(revenue) as cumulative
ASQL SQL Equivalent dbt_utils Macro
per X first by -date ROW_NUMBER() OVER (PARTITION BY X ORDER BY date DESC) = 1 deduplicate
prior(col) LAG(col, 1) OVER (...) —
next(col) LEAD(col, 1) OVER (...) —
running_sum(col) SUM(col) OVER (ROWS UNBOUNDED PRECEDING) —
rolling_avg(col, 7) AVG(col) OVER (ROWS 6 PRECEDING) —

Cross-Dialect Date Handling

Write once, run on any database:

where created_at > 7 days ago
where due_date < 30 days from now
select order_date + 14 days as delivery_date
Operation PostgreSQL BigQuery ASQL
Truncate to month DATE_TRUNC('month', d) DATE_TRUNC(d, MONTH) month(d)
Add days d + INTERVAL '7 days' DATE_ADD(d, INTERVAL 7 DAY) d + 7 days
Date literal DATE '2024-01-01' DATE '2024-01-01' @2024-01-01

ASQL compiles to the correct syntax for your target database.

Convention-Based Joins

ASQL infers relationships from naming patterns:

ASQL Playground 
-- Auto-joins on user_id → users.id
from orders
  & users
  select orders.*, users.name

-- FK shorthand: department_id matches departments.id
from employees
  join departments on department_id

When your tables follow standard naming conventions (user_id → users.id), ASQL figures out the join conditions. No more typing the obvious.

Syntax Shortcuts

ASQL adds shorthand for patterns that are verbose in SQL.

Count

#                    -- COUNT(*)
#col                 -- COUNT(col)
##col                -- COUNT(DISTINCT col) - double # = distinct
uniq(col)            -- COUNT(DISTINCT col) - function form

Descending Sort

order by -revenue    -- ORDER BY revenue DESC

Natural Aggregates

These are equivalent:

sum(amount)
sum amount
sum_amount

All produce a column named sum_amount.

Column Operations

Exclude Columns

ASQL Playground 
from users
  except password_hash, internal_notes

Rename

ASQL Playground 
from users
  rename id as user_id

Replace Values

ASQL Playground 
from users
  replace name with upper(name), salary with round(salary, 2)

Sampling

-- Fixed sample size
from orders sample 100

-- Percentage sample
from orders sample 10%

-- Stratified sampling (N per group)
from orders sample 100 per category

Gap-Filling for Time Series

When grouping by time, SQL only returns rows that exist—missing months simply disappear from your results. ASQL can automatically fill gaps:

ASQL Playground 
from orders
  group by spine(month(order_date)) (
    sum(amount) ?? 0 as revenue
  )
month revenue
Jan 1000
Feb 1500
Mar 800
Apr 0
May 0
Jun 1200

The spine() function ensures all time periods appear—even April and May with no orders. The ?? 0 provides a default value for missing periods.

See Guaranteed Groups for details.

Pivot & Unpivot

-- Rows to columns
from sales
  pivot sum(amount) by category values ('Electronics', 'Clothing')

-- Columns to rows
from metrics
  unpivot jan, feb, mar into month, value

-- Expand arrays
from posts
  explode tags as tag

Multi-Dialect Output

ASQL uses SQLGlot for transpilation:

PostgreSQL, MySQL, SQLite, BigQuery, Snowflake, Redshift, DuckDB, Trino, Spark SQL, and more.

What ASQL Is

  • Pipe syntax — Queries flow top-to-bottom in execution order, like dbt and pandas
  • Built-in helpers — Common analytics patterns (dedup, window functions, date math) built into the language
  • Dialect-portable — Write once, run on PostgreSQL, BigQuery, Snowflake, DuckDB, and more
  • SQL-compatible — Familiar vocabulary, compiles to standard SQL you can inspect

What ASQL Is Not

  • Not a database
  • Not required—use the generated SQL directly if you prefer
  • Not production-ready (yet)

Getting Started

Documentation

Concepts

Understanding ASQL's design:

Syntax Guide

Reference

Development

Inspiration

  • SQLGlot — SQL parsing and transpilation
  • pandas — Method chaining, column operations
  • PRQL — Pipeline approach to SQL
  • dbt — Many ASQL features are built-in versions of dbt macro patterns
  • Kusto/KQL — Pipe syntax at scale
⚠️ Work in Progress
ASQL is experimental. Syntax may change. Feedback welcome on GitHub.