Tables and functions
Every virtual table, column and allowed function in Atrix SQL, generated from the compiler.
This page is generated from the SQL compiler's own tables (crates/aql/src/tables.rs) and function allowlist (crates/aql/src/functions.rs). If a name is not here, the compiler rejects it.
Virtual tables
Every table is already scoped to your project and environment, and deduplicated or deleted rows are folded away. You never write project_id filters yourself.
events
project_id, environment, uuid, event, timestamp, inserted_at, distinct_id, person_id, person_mode, session_id, window_id, device_id, properties, elements_chain, group_0, group_1, group_2, group_3, group_4, m_url, m_pathname, m_referrer, m_utm_source, m_utm_medium, m_utm_campaign, m_screen, m_os, m_os_version, m_browser, m_device_type, m_app_version, m_lib, m_lib_version, m_country, m_region, m_city, bot_class
persons
project_id, environment, id, created_at, properties, is_identified
person_distinct_ids
project_id, environment, distinct_id, person_id
groups
project_id, environment, group_type_index, group_key, created_at, properties
sessions
project_id, environment, session_id, start_timestamp, end_timestamp, duration_s, entry_url, exit_url, entry_screen, exit_screen, pageview_count, screen_count, event_count, utm_source, person_id, distinct_id
cohort_people
project_id, environment, cohort_id, cohort_version, person_id
revenue_events
project_id, environment, provider, provider_event_id, event_type, timestamp, amount_minor, currency, amount_usd_minor, mrr_delta_usd_minor, customer_ref, distinct_id, person_id, subscription_id, product_id
Scalar and window functions
456 functions. Names match case-insensitively; the canonical spelling is what runs.
Conditionals / nulls
if, multiIf, coalesce, ifNull, nullIf, assumeNotNull, toNullable, isNull, isNotNull, isZeroOrNull, greatest, least
Arithmetic / math
plus, minus, multiply, divide, intDiv, intDivOrZero, modulo, moduloOrZero, negate, abs, gcd, lcm, round, roundBankers, roundDown, floor, ceil, ceiling, trunc, truncate, sqrt, cbrt, exp, exp2, exp10, log, ln, log2, log10, log1p, pow, power, sign, pi, e, sin, cos, tan, asin, acos, atan, atan2, hypot, isFinite, isInfinite, isNaN, rand, rand64, randCanonical, bitAnd, bitOr, bitXor, bitNot, bitShiftLeft, bitShiftRight, bitCount, bitTest
Comparison / logic
equals, notEquals, less, greater, lessOrEquals, greaterOrEquals, and, or, not, xor
Type conversion
toString, toFixedString, toInt8, toInt16, toInt32, toInt64, toInt128, toInt256, toUInt8, toUInt16, toUInt32, toUInt64, toUInt128, toUInt256, toFloat32, toFloat64, toDecimal32, toDecimal64, toDecimal128, toBool, toInt8OrNull, toInt16OrNull, toInt32OrNull, toInt64OrNull, toUInt8OrNull, toUInt16OrNull, toUInt32OrNull, toUInt64OrNull, toFloat32OrNull, toFloat64OrNull, toInt8OrZero, toInt16OrZero, toInt32OrZero, toInt64OrZero, toUInt8OrZero, toUInt16OrZero, toUInt32OrZero, toUInt64OrZero, toFloat32OrZero, toFloat64OrZero, toDecimal32OrNull, toDecimal64OrNull, toUUID, toUUIDOrNull, toUUIDOrZero, toDateOrNull, toDateOrZero, toDateTimeOrNull, toDateTimeOrZero, toTypeName, toLowCardinality, toIPv4, toIPv6, IPv4NumToString, IPv4StringToNum
Dates and times
now, now64, today, yesterday, toDate, toDate32, toDateTime, toDateTime64, toTimeZone, toYear, toQuarter, toMonth, toWeek, toISOWeek, toISOYear, toDayOfYear, toDayOfMonth, toDayOfWeek, toHour, toMinute, toSecond, toUnixTimestamp, toUnixTimestamp64Milli, fromUnixTimestamp, fromUnixTimestamp64Milli, toStartOfYear, toStartOfQuarter, toStartOfMonth, toStartOfWeek, toMonday, toStartOfDay, toStartOfHour, toStartOfMinute, toStartOfSecond, toStartOfFiveMinutes, toStartOfTenMinutes, toStartOfFifteenMinutes, toStartOfInterval, toLastDayOfMonth, toYYYYMM, toYYYYMMDD, toYYYYMMDDhhmmss, toRelativeYearNum, toRelativeMonthNum, toRelativeWeekNum, toRelativeDayNum, toRelativeHourNum, toRelativeMinuteNum, toRelativeSecondNum, dateDiff, date_diff, age, dateTrunc, date_trunc, dateAdd, date_add, dateSub, date_sub, timestampAdd, timestampSub, addYears, addQuarters, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, subtractYears, subtractQuarters, subtractMonths, subtractWeeks, subtractDays, subtractHours, subtractMinutes, subtractSeconds, toIntervalYear, toIntervalQuarter, toIntervalMonth, toIntervalWeek, toIntervalDay, toIntervalHour, toIntervalMinute, toIntervalSecond, formatDateTime, parseDateTimeBestEffort, parseDateTimeBestEffortOrNull, parseDateTimeBestEffortOrZero, parseDateTime64BestEffort, parseDateTime64BestEffortOrNull, timeSlot, timeSlots, UUIDv7ToDateTime
Strings
length, lengthUTF8, char_length, empty, notEmpty, lower, upper, lowerUTF8, upperUTF8, reverse, reverseUTF8, concat, concatWithSeparator, substring, substr, substringUTF8, left, right, leftUTF8, rightUTF8, leftPad, rightPad, trim, trimBoth, trimLeft, trimRight, ltrim, rtrim, repeat, format, position, positionCaseInsensitive, positionUTF8, locate, startsWith, endsWith, like, notLike, ilike, notILike, match, extract, extractAll, extractGroups, replaceOne, replaceAll, replace, replaceRegexpOne, replaceRegexpAll, splitByChar, splitByString, splitByRegexp, splitByWhitespace, arrayStringConcat, multiSearchAny, multiMatchAny, countSubstrings, normalizeUTF8NFC, toValidUTF8, hex, unhex, base64Encode, base64Decode, tryBase64Decode, lowerUTF8, stringToH3, leftPadUTF8, rightPadUTF8, initcap, soundex, ngramDistance
Hashing
cityHash64, sipHash64, sipHash128, xxHash32, xxHash64, murmurHash2_64, murmurHash3_64, farmHash64, MD5, SHA1, SHA256, halfMD5, javaHash
JSON (properties are JSON text)
JSONHas, JSONLength, JSONType, JSONExtract, JSONExtractUInt, JSONExtractInt, JSONExtractFloat, JSONExtractBool, JSONExtractString, JSONExtractRaw, JSONExtractArrayRaw, JSONExtractKeys, JSONExtractKeysAndValues, JSONExtractKeysAndValuesRaw, simpleJSONHas, simpleJSONExtractString, simpleJSONExtractRaw, simpleJSONExtractInt, simpleJSONExtractUInt, simpleJSONExtractFloat, simpleJSONExtractBool, isValidJSON, toJSONString
URLs
protocol, domain, domainWithoutWWW, topLevelDomain, firstSignificantSubdomain, cutToFirstSignificantSubdomain, port, path, pathFull, queryString, fragment, extractURLParameter, extractURLParameters, extractURLParameterNames, cutQueryString, cutFragment, cutWWW, cutURLParameter, decodeURLComponent, encodeURLComponent, URLHierarchy, URLPathHierarchy, netloc
Arrays (lambdas allowed as arguments)
array, arrayJoin, arrayMap, arrayFilter, arrayExists, arrayAll, arrayCount, arraySum, arrayAvg, arrayMin, arrayMax, arrayProduct, arraySort, arrayReverseSort, arrayReverse, arrayDistinct, arrayUniq, arrayElement, has, hasAll, hasAny, hasSubstr, indexOf, countEqual, arraySlice, arrayConcat, arrayEnumerate, arrayEnumerateUniq, arrayCompact, arrayZip, arrayFirst, arrayLast, arrayFirstIndex, arrayLastIndex, arrayFill, arrayReverseFill, arraySplit, arrayCumSum, arrayDifference, arrayPushBack, arrayPushFront, arrayPopBack, arrayPopFront, arrayResize, arrayIntersect, arrayFlatten, arrayShuffle, arrayPartialSort, range, emptyArrayString, emptyArrayUInt64, emptyArrayInt64, emptyArrayFloat64, notEmpty, arrayStringConcat
Tuples and maps
tuple, tupleElement, map, mapKeys, mapValues, mapContains, mapFromArrays
Misc pure
generateUUIDv4, generateUUIDv7, toUInt128OrNull, bar, formatReadableSize, formatReadableQuantity, formatReadableTimeDelta, transform, materialize, identity, ignore, isConstant
Bitmaps (retention)
bitmapCardinality, bitmapAnd, bitmapOr, bitmapXor, bitmapAndnot, bitmapToArray, bitmapBuild, bitmapContains, bitmapHasAny, bitmapHasAll, bitmapAndCardinality, bitmapOrCardinality
Window functions
row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lagInFrame, leadInFrame, first_value, last_value, nth_value, lag, lead
Aggregate functions
74 aggregates:
count, sum, avg, min, max, any, anyLast, anyHeavy, argMin, argMax, uniq, uniqExact, uniqCombined, uniqCombined64, uniqHLL12, uniqTheta, groupArray, groupUniqArray, groupArraySample, groupArrayMovingSum, groupArrayInsertAt, groupBitmap, groupBitAnd, groupBitOr, groupBitXor, median, medianExact, quantile, quantiles, quantileExact, quantilesExact, quantileTDigest, quantilesTDigest, quantileTiming, quantileDeterministic, stddevPop, stddevSamp, varPop, varSamp, corr, covarPop, covarSamp, skewPop, skewSamp, kurtPop, kurtSamp, topK, topKWeighted, histogram, sumMap, minMap, maxMap, sumWithOverflow, sumKahan, avgWeighted, entropy, simpleLinearRegression, windowFunnel, retention, sequenceMatch, sequenceCount, sequenceNextNode, boundingRatio, rankCorr, exponentialMovingAverage, first_value, last_value, groupConcat, sparkbar, deltaSum, any_value, countDistinct, stddev, variance
Combinators
Any aggregate may carry up to four of these suffixes, for example countIf, uniqExactIf or sumArrayIf: If, Array, Distinct, OrNull, OrDefault, Merge, State, ForEach, Map.