Quick Start¶

Get started with Analytic SQL. This guide covers the essential language features you need to write powerful analytic queries.

Table of Contents¶

Basic Operations
Filtering
Aggregations
Ordering & Limiting
Date Functions
Joins
Window Functions
Common Patterns

Basic Operations¶

FROM - Start with a table¶

Every Analytic SQL query starts with from:

ASQL Playground

from users

This selects all columns from the users table.

SELECT - Choose columns¶

Use select to specify which columns you want:

ASQL Playground

from users
select name, email, created_at

WHERE - Filter rows¶

Filter rows with where:

ASQL Playground

from users
where status = "active"

Filtering¶

Comparison Operators¶

= - equals (preferred; == also works)
!= - not equals
<, >, <=, >= - comparisons
is null / is not null - null checks
in / not in - membership checks

ASQL Playground

from orders
where amount > 100
  and status != "cancelled"
  and customer_id is not null

Logical Operators¶

and - logical AND
or - logical OR
not - logical NOT

ASQL Playground

from users
where (status = "active" or status = "pending")
  and email is not null

String Matching¶

ASQL Playground

from products
where name contains "widget"
  and category in ("electronics", "computers")

Aggregations¶

GROUP BY¶

Group rows and compute aggregates:

ASQL Playground

from orders
group by customer_id (
    sum(amount) as total_spent,
    # as order_count
)

The # symbol is shorthand for COUNT(*).

Multiple Aggregations¶

You can compute multiple aggregates in a single group by:

ASQL Playground

from sales
group by product (
    sum(amount) as revenue,
    avg(amount) as avg_order_value,
    min(amount) as min_order,
    max(amount) as max_order,
    # as order_count
)

Aggregation Functions¶

# (preferred) or count(*) - count rows
#col - count non-null values in column
##col or uniq(col) - count distinct values
sum(column) - sum values
avg(column) - average values
min(column) - minimum value
max(column) - maximum value

Auto-Generated Column Names¶

When you use functions without explicit AS aliases, ASQL automatically generates meaningful names:

from orders
group by customer_id (
    sum(amount),     -- → column: sum_amount
    avg(amount),     -- → column: avg_amount
    count(*)         -- → column: num
)
order by -sum_amount  -- Reference the auto-generated name!

No more SQL's unusable defaults like count, f0_, or SUM(amount). See the Auto-Aliasing Reference for complete details.

Ordering & Limiting¶

ORDER BY¶

Order results with order by:

from users
order by name              # Ascending

from users
order by -created_at        # Descending (use - prefix)

Multiple Order Columns¶

ASQL Playground

from orders
order by -amount, created_at          -- Order by amount DESC, then created_at ASC

TAKE - Limit results¶

Use limit to limit the number of rows:

ASQL Playground

from users
order by -created_at
limit 10

Date Functions¶

Date Truncation by Unit¶

year, quarter, month, week, day, and hour truncate to the start of the unit, so January 2024 and January 2025 stay separate groups:

ASQL Playground

from events
group by month(date) (
    # as event_count
)

Available functions: - year(date) - start of the year - month(date) - start of the month - day(date) - start of the day - date(date) - extract date (remove time)

Date Extraction¶

To get a number instead of a date, use month of year date (1-12), day of week date, and the other forms in Dates, or extract(year from date).

Explicit `date_trunc`¶

ASQL Playground

from events
group by date_trunc("month", date) (
    # as events_per_month
)

Date Formatting¶

from events
select
    date_format(date, "%Y-%m") as month,
    #

Joins¶

ASQL uses symbolic operators for joins where & represents the join point and ? marks optional (nullable) sides.

Join Operators¶

Operator	Join Type	Meaning
`&`	INNER JOIN	Both sides must match
`&?`	LEFT JOIN	Right side is optional
`?&`	RIGHT JOIN	Left side is optional
`?&?`	FULL OUTER	Both sides are optional
`*`	CROSS JOIN	Cartesian product

INNER JOIN¶

ASQL Playground

from orders & users on orders.user_id = users.id

LEFT JOIN¶

ASQL Playground

from users &? orders on users.id = orders.user_id

Multiple Joins¶

from orders
  & users on orders.user_id = users.id
  & products on orders.product_id = products.id
select
    users.name,
    products.name as product_name,
    orders.amount

FK Dot Notation (Auto-Joins)¶

If your columns follow the {name}_id pattern, you can use dot notation for automatic joins:

-- orders has user_id column → auto-joins to users
from orders
select
    orders.amount,
    orders.user.name,      -- Auto LEFT JOIN via user_id
    orders.user.email

This compiles to:

SELECT orders.amount, user_1.name, user_1.email
FROM orders
LEFT JOIN users AS user_1 ON orders.user_id = user_1.id

Window Functions¶

ASQL provides simplified syntax for common window function patterns.

prior() and next()¶

Get values from previous or next rows:

from monthly_sales
order by month
select
    month,
    revenue,
    prior(revenue) as prev_month,           # Previous row's revenue
    prior(revenue, 3) as three_months_ago,  # 3 rows back
    next(revenue) as next_month             # Next row's revenue

Running Aggregates¶

Calculate cumulative totals:

from transactions
order by date
select
    date,
    amount,
    running_sum(amount) as cumulative_total,
    running_avg(amount) as avg_to_date,
    running_count(*) as transaction_number

Rolling Aggregates¶

Calculate moving averages with a window size:

from daily_sales
order by date
select
    date,
    revenue,
    rolling_avg(revenue, 7) as seven_day_avg

Deduplication with `per`¶

Get the first/last row per group with the per command:

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

-- Add row numbers per customer
from orders
per customer_id number by -order_date

-- Rank employees by salary within department
from employees
per department rank by -salary

QUALIFY Clause¶

For more complex window function filtering:

from orders
select *, row_number() over (partition by customer_id order by -order_date) as rn
qualify rn = 1

DISTINCT ON¶

PostgreSQL-style deduplication:

ASQL Playground

from orders
distinct on (customer_id)
order by customer_id, -order_date

See the Window Functions guide for more details and patterns.

Common Patterns¶

Top N by Group¶

Find the top 5 products by revenue in each category:

ASQL Playground

from sales
group by category, product (
    sum(amount) as revenue
)
order by category, -revenue

Time Series Analysis¶

Daily revenue for the last 30 days:

ASQL Playground

from orders
where date >= 30 days ago
group by date_trunc("day", date) (
    sum(amount) as daily_revenue,
    # as order_count
)
order by date

Cohort Analysis¶

User signups by month:

ASQL Playground

from users
group by year(created_at), month(created_at) (
    # as signups
)
order by year, month

Filtered Aggregations¶

Count active users per country:

ASQL Playground

from users
where status = "active"
group by country (
    # as active_users
)
order by -active_users

Complete Example¶

Here's a complete query that demonstrates multiple features:

ASQL Playground

from orders
  where date >= @2024-01-01
    and status = "completed"
  & products on orders.product_id = products.id
  group by products.category (
      sum(orders.amount) as revenue,
      avg(orders.amount) as avg_order_value,
      # as order_count
  )
  order by -revenue
  limit 10

This query: 1. Starts with orders 2. Filters to completed orders from 2024 3. Inner joins with products using & 4. Groups by product category 5. Computes revenue, average order value, and order count 6. Sorts by revenue descending 7. Takes the top 10 categories

Next Steps¶

Window Functions - Simplified window function utilities
Examples - See more real-world query patterns
Language Specification - Complete syntax reference
Playground - Try writing queries interactively

Ready to write queries? Open the playground →

Quick Start¶

Table of Contents¶

Basic Operations¶

FROM - Start with a table¶

SELECT - Choose columns¶

WHERE - Filter rows¶

Filtering¶

Comparison Operators¶

Logical Operators¶

String Matching¶

Aggregations¶

GROUP BY¶

Multiple Aggregations¶

Aggregation Functions¶

Auto-Generated Column Names¶

Ordering & Limiting¶

ORDER BY¶

Multiple Order Columns¶

TAKE - Limit results¶

Date Functions¶

Date Truncation by Unit¶

Date Extraction¶

Explicit date_trunc¶

Date Formatting¶

Joins¶

Join Operators¶

INNER JOIN¶

LEFT JOIN¶

Multiple Joins¶

FK Dot Notation (Auto-Joins)¶

Window Functions¶

prior() and next()¶

Running Aggregates¶

Rolling Aggregates¶

Deduplication with per¶

QUALIFY Clause¶

DISTINCT ON¶

Common Patterns¶

Top N by Group¶

Time Series Analysis¶

Cohort Analysis¶

Filtered Aggregations¶

Complete Example¶

Next Steps¶

Explicit `date_trunc`¶

Deduplication with `per`¶