Summary of functions

Kater SQL has two kinds of functions. Its own calls, such as MEASURE, EVAL and TOTAL, declare fields and filters, and change the context that a measure is worked out in. A KATER__ function is a call that the compiler turns into your warehouse’s SQL. The menus write most of them for you, and you can write them yourself in any Kater SQL file. The same call compiles to Snowflake SQL on a Snowflake connection and to DuckDB SQL on a DuckDB connection. The editor describes each call when you hold the pointer over it.

Each function’s page gives its syntax, its arguments, what it returns, and an example. The example is a Sandbox query over the sample shop’s orders, with the rows that Kater gave. Where a KATER__ call stands for plain SQL, the page also shows that SQL on each warehouse. For the rest of Kater SQL, see the Kater SQL reference.

Family	What it does	Functions
Field declarations	A declaration makes a new field: a dimension, a measure or a custom calculation.	DIMENSION, MEASURE, CUSTOM_CALCULATION, NOT_ADDITIVE_ACROSS, EXCEL
EVAL and its modifiers	EVAL works out a measure on each row, in the SELECT’s context or in a context that a modifier changes.	EVAL, CLEAR, REMOVE, SET, SET_CUSTOM, CURRENT
Filters	A filter is a named condition in WHERE.	OPTIONAL_FILTER, REQUIRED_FILTER
Calculation context	These calls work a measure out again in another context, such as the total of all channels on each channel’s row.	TOTAL, SHARE_OF_TOTAL, VALUE_FOR, CALCULATE_ACROSS, CALCULATE_BY, CALCULATE_PER, RATIO, RUNNING_TOTAL, MOVING_AVERAGE, CUMULATIVE_SHARE, BY, ACROSS, IGNORE_FILTERS
Aggregations	An aggregation combines the values of many rows into one value for each group.	KATER__SUM, KATER__AVG, KATER__MIN, KATER__MAX, KATER__COUNT, KATER__COUNT_DISTINCT, KATER__COUNT_TRUE, KATER__COUNT_FALSE, KATER__LIST
Conditions	A condition is TRUE or FALSE for each row.	KATER__IS_EQUAL_TO, KATER__IS_NOT_EQUAL_TO, KATER__IS_GREATER_THAN, KATER__IS_AT_LEAST, KATER__IS_LESS_THAN, KATER__IS_AT_MOST, KATER__IS_BETWEEN, KATER__IS_ONE_OF, KATER__IS_NOT_ONE_OF, KATER__TEXT_CONTAINS, KATER__TEXT_DOES_NOT_CONTAIN, KATER__IS_NULL, KATER__IS_NOT_NULL
Date conditions	These conditions test a date or a timestamp.	KATER__IS_AFTER, KATER__IS_BEFORE, KATER__IS_ON_OR_AFTER, KATER__IS_ON_OR_BEFORE, KATER__IS_ON, KATER__IS_NOT_ON, KATER__IS_WITHIN_LAST
Top N	These conditions keep the rows of the result that rank highest or lowest.	KATER__IS_IN_TOP, KATER__IS_IN_BOTTOM
Date grains	A grain gives the start of the period that a date falls in, so rows group by day, week, month, quarter or year.	KATER__DATE, KATER__WEEK, KATER__MONTH, KATER__QUARTER, KATER__YEAR, KATER__HOUR, KATER__MINUTE, KATER__SECOND, KATER__MILLISECOND
Date parts	A date part gives one part of a date as a number or a name, such as the day of the week.	KATER__DAY_OF_WEEK, KATER__DAY_OF_WEEK_INDEX, KATER__DAY_OF_MONTH, KATER__DAY_OF_QUARTER, KATER__DAY_OF_YEAR, KATER__WEEK_OF_YEAR, KATER__MONTH_NUM, KATER__MONTH_NAME, KATER__QUARTER_OF_YEAR, KATER__HOUR_OF_DAY, KATER__MINUTE_OF_HOUR
Derive	These functions put values into groups, give them new labels, or count the periods between two dates.	KATER__MAP, KATER__BUCKET, KATER__PERIODS_SINCE
Windows	A window works out a value for each row of the result from other rows of the result, such as a running total or a rank.	KATER__WINDOW_AGGREGATION, KATER__LAG, KATER__LEAD, KATER__FIRST_VALUE, KATER__LAST_VALUE, KATER__DELTA, KATER__PERCENT_DELTA, KATER__RANK, KATER__ROW_NUMBER, KATER__SHARE_OF_TOTAL
Totals	A total adds up a column over every row of the query, and a row total adds up columns on each row, as a table’s Total row and Total column write them.	KATER__TOTAL, KATER__ROW_TOTAL
Calendar comparisons	A calendar comparison works a measure out again for an earlier period, such as the month before, so each row shows both.	KATER__PREVIOUS_PERIOD, KATER__SAME_PERIOD_LAST_YEAR, KATER__SAME_WEEKDAY_LAST_WEEK, KATER__PERIOD_SHIFT, KATER__PERCENT_CHANGE
Calculate at	These functions work a measure out at a grouping that is not the query’s own, such as each region’s total on each channel’s row.	KATER__ALWAYS_BY, KATER__CALCULATE_PER, KATER__IGNORE_GROUPING_BY