Functions Reference¶

Complete reference of ASQL built-in functions.

Aggregate Functions¶

count¶

Count rows or non-null values.

count(*)                    -- Count all rows
count(column)               -- Count non-null values
count(distinct column)      -- Count distinct values
--                           -- Shorthand for count(*)
#(column)                   -- Shorthand for count(column)

sum¶

Sum numeric values.

sum(amount)                 -- Sum of amount
sum_amount                  -- Shorthand (same as above)
total(amount)               -- Alias for sum
total amount                -- Natural language style

avg / average¶

Calculate average.

avg(price)                  -- Average price
average(price)              -- Alias
avg_price                   -- Shorthand
average of price            -- Natural language

min / minimum¶

Find minimum value.

min(date)                   -- Earliest date
minimum(amount)             -- Smallest amount
min_created_at              -- Shorthand

max / maximum¶

Find maximum value.

max(amount)                 -- Largest amount
maximum(amount)             -- Alias
max_created_at              -- Shorthand

Date Truncation Functions¶

Truncate dates to a specific precision.

year¶

year(created_at)            -- Truncate to year start: 2025-01-01
year_created_at             -- Shorthand
year of created_at          -- Natural language

month¶

month(created_at)           -- Truncate to month start: 2025-03-01

week¶

week(created_at)            -- Truncate to week start (Monday)
week_monday(created_at)     -- Explicit Monday start
week_sunday(created_at)     -- Sunday start

day¶

day(created_at)             -- Truncate to day: 2025-03-15

hour¶

hour(created_at)            -- Truncate to hour: 2025-03-15 14:00:00

quarter¶

quarter(created_at)         -- Truncate to quarter start

Date Part Extraction¶

Extract specific parts from dates (returns integers).

day of week¶

day of week created_at      -- 1-7 (Monday = 1)
day_of_week(created_at)     -- Function style

day of month¶

day of month created_at     -- 1-31
day_of_month(created_at)

day of year¶

day of year created_at      -- 1-366
day_of_year(created_at)

week of year¶

week of year created_at     -- 1-52
week_of_year(created_at)

month of year¶

month of year created_at    -- 1-12
month_of_year(created_at)

quarter of year¶

quarter of year created_at  -- 1-4
quarter_of_year(created_at)

Date Difference Functions¶

Calculate time between dates.

days¶

days(end_date - start_date)     -- Integer days between
days_between(start, end)        -- Alternative syntax

weeks¶

weeks(end_date - start_date)    -- Integer weeks between
weeks_between(start, end)

months¶

months(end_date - start_date)   -- Integer months between
months_between(start, end)

years¶

years(end_date - start_date)    -- Integer years between
years_between(start, end)

hours¶

hours(end_date - start_date)    -- Integer hours between

Time Since/Until Functions¶

Calculate time elapsed since a date, or time remaining until a future date.

days_since / days_until¶

-- Three equivalent syntaxes:
days_since(created_at)          -- Function call (recommended)
days since created_at           -- Space notation
days_since_created_at           -- Underscore alias

days_until(due_date)            -- Time remaining
days until due_date
days_until_due_date

weeks_since / weeks_until¶

weeks_since(signup_date)
weeks since signup_date
weeks_since_signup_date

weeks_until(deadline)
weeks until deadline

months_since / months_until¶

months_since(last_login)
months since last_login

months_until(renewal_date)
months until renewal_date

years_since / years_until¶

years_since(birth_date)         -- Calculate age
years since birth_date

years_until(contract_end)

hours_since / hours_until¶

hours_since(event_time)
hours since event_time

minutes_since / minutes_until¶

minutes_since(last_update)
minutes since last_update

seconds_since / seconds_until¶

seconds_since(request_time)
seconds since request_time

Window Functions¶

prior¶

Access previous row value (LAG).

prior(revenue)              -- Previous row's revenue
prior(revenue, 3)           -- 3 rows back

next¶

Access next row value (LEAD).

next(revenue)               -- Next row's revenue
next(revenue, 2)            -- 2 rows ahead

running_sum¶

Cumulative sum from start.

running_sum(amount)         -- Cumulative total
running sum amount          -- Space style

running_avg¶

Cumulative average from start.

running_avg(amount)         -- Average to date

running_count¶

Cumulative count from start.

running_count(*)            -- Row number (effectively)

rolling_avg¶

Moving average with window size.

rolling_avg(price, 7)       -- 7-day moving average
rolling_avg(price, 30)      -- 30-day moving average

rolling_sum¶

Moving sum with window size.

rolling_sum(amount, 7)      -- 7-day rolling sum

rolling_min¶

Moving minimum with window size.

rolling_min(price, 7)       -- 7-day rolling minimum

rolling_max¶

Moving maximum with window size.

rolling_max(price, 7)       -- 7-day rolling maximum

rolling_count¶

Moving count with window size.

rolling_count(events, 7)    -- 7-day rolling count

first¶

Get first value with ordering.

first(order_id order by -order_date)    -- Latest order
first(order_id order by order_date)     -- Earliest order

last¶

Get last value with ordering.

last(order_id order by order_date)      -- Latest order

arg_max¶

Value where another column is maximum.

arg_max(order_id, order_date)           -- order_id at max order_date

arg_min¶

Value where another column is minimum.

arg_min(order_id, order_date)           -- order_id at min order_date

row_number¶

Standard SQL window function.

row_number() over (partition by customer_id order by -order_date)

rank¶

Standard SQL window function.

rank() over (partition by department order by -salary)

dense_rank¶

Standard SQL window function (no gaps).

dense_rank() over (partition by category order by -revenue)

String Functions¶

concat¶

Concatenate strings.

concat(first_name, " ", last_name)

string_agg¶

Aggregate strings with separator.

string_agg(product_name, ", ")

upper / lower¶

Change case.

upper(name)
lower(email)

trim / ltrim / rtrim¶

Remove whitespace.

trim(input)
ltrim(input)
rtrim(input)

length¶

String length.

length(name)

substring¶

Extract substring.

substring(email, 1, 5)
email[1:5]                  -- Slice syntax

replace¶

Replace text.

replace(text, "old", "new")

slugify¶

Convert text to URL-safe slugs.

slugify(name)               -- "Hello World!" → "hello-world"
slugify(title) as slug      -- URL-friendly version

Converts to lowercase, replaces non-alphanumeric characters with hyphens, and trims leading/trailing hyphens.

Null Handling Functions¶

coalesce¶

Return first non-null value.

value ?? default            -- Operator form (preferred)
primary_email ?? secondary_email ?? "unknown"  -- Chained fallbacks

-- Function form (also accepted)
coalesce(primary_email, secondary_email, "unknown")

Type Functions¶

cast¶

Explicit type conversion.

value::INTEGER              -- Cast to integer
value::DATE                 -- Cast to date
value::TIMESTAMP            -- Cast to timestamp
value::INTEGER?             -- Safe cast (NULL on failure)

Binning Functions¶

bucket¶

Discretize continuous values into labeled bins. Compiles to portable CASE WHEN expressions.

Form 1: Explicit boundaries with labels

bucket(score, [0, 60, 70, 80, 90, 100], ['F', 'D', 'C', 'B', 'A']) as grade

Creates bins: - [0, 60) → 'F' - [60, 70) → 'D' - [70, 80) → 'C' - [80, 90) → 'B' - [90, 100] → 'A' (last bin includes upper bound)

Form 2: Explicit boundaries with auto-labels

bucket(amount, [0, 100, 500, 1000]) as tier

Auto-generates range labels: '0-100', '100-500', '500-1000'

Form 3: Width-based bins

bucket(value, start=0, end=100, width=10) as decile

Generates evenly-spaced bins: '0-10', '10-20', ..., '90-100'

Examples¶

-- Grade students by score
from students
  select name, score,
    bucket(score, [0, 60, 70, 80, 90, 100], ['F', 'D', 'C', 'B', 'A']) as grade

-- Segment customers by lifetime value
from customers
  select *,
    bucket(lifetime_value, [0, 100, 1000, 10000, 100000], 
           ['bronze', 'silver', 'gold', 'platinum']) as tier

-- Age demographics with auto-labels
from users
  group by bucket(age, [0, 18, 30, 50, 65, 100]) (
    # as count
  )

-- Temperature ranges (supports negative values)
from readings
  select *,
    bucket(temp_celsius, [-40, -20, 0, 20, 40]) as temp_category

-- Percentile-style bins
from sales
  select *,
    bucket(amount, start=0, end=1000, width=100) as amount_bucket

Boundary Behavior¶

Left-inclusive: Each bin includes its lower bound
Right-exclusive: Each bin excludes its upper bound (except the last)
Last bin includes upper bound: [90, 100] not [90, 100)
Out of range: Values outside boundaries return NULL

Gap-Filling Functions¶

fill_forward¶

Propagate the last non-null value forward to fill gaps.

fill_forward(value)             -- Fill NULLs with previous value

Useful for time series where missing data should inherit the previous value.

from daily_prices
  order by date
  select date, fill_forward(price) as price

Compiles to: LAST_VALUE(col) IGNORE NULLS OVER (ROWS UNBOUNDED PRECEDING)

fill_backward¶

Propagate the next non-null value backward to fill gaps.

fill_backward(value)            -- Fill NULLs with next value

from forecasts
  order by date
  select date, fill_backward(target) as target

Compiles to: FIRST_VALUE(col) IGNORE NULLS OVER (ROWS CURRENT ROW TO UNBOUNDED FOLLOWING)

Special Functions¶

key¶

Generate a deterministic surrogate key from one or more columns. Inspired by dbt_utils' generate_surrogate_key.

key(user_id, order_id) as order_key
key(source, record_id) as unique_id

The function: - Concatenates column values with a separator - Handles NULLs consistently (converts to empty string) - Returns an MD5 hash of the concatenated values

-- Create stable keys for fact tables
from raw_events
  select *,
    key(user_id, event_type, timestamp) as event_key

-- Deduplicate by composite key
from staging_data
  group by key(source_system, record_id) (
    first(*)
  )

guarantee (Planned)¶

Note: guarantee() is a planned feature not yet implemented.

Specify explicit values for group by spine.

guarantee(status, ['pending', 'active', 'completed'])

Function Aliases¶

Natural Language	SQL Function
`total`	`SUM`
`average`	`AVG`
`maximum`	`MAX`
`minimum`	`MIN`

Auto-Generated Column Names¶

When functions are used without explicit AS aliases, ASQL automatically generates meaningful column names:

Function	Auto-Alias
`sum(amount)`	`sum_amount`
`avg(price)`	`avg_price`
`count(*)` / `#`	`num`
`month(created_at)`	`month_created_at`
`upper(name)`	`upper_name`

See the Auto-Aliasing Reference for the complete mapping table and configuration.