Salesforce Docs Portal

SQL Functions for Insights

Use these aggregate, datetime, analytical, formatting, math and statistical, and boolean functions when you author calculated insights in Data 360. The Aggregatable column indicates whether a function's output can be rolled up across fewer dimensions when queried in segments or activations.

Required Editions

Note
Note Streaming and real-time insights support only the SUM and COUNT aggregate functions.
Available in: All Editions supported by Data 360. See Data 360 edition availability.

Aggregatable and Non-Aggregatable Metrics

Some functions produce metrics that can be rolled up or collapsed across dimensions. These are called aggregatable metrics. Functions such as SUM, COUNT, and AVG are examples of aggregatable metrics. You can query the result for any subset of the dimensions you defined when you created the insight.

Other functions produce non-aggregatable metrics. Functions such as RANK, NTILE, and CASE are examples of non-aggregatable metrics. Because the result depends on all the dimensions at once, you can't reduce or collapse the metric to a smaller set of dimensions. When you query a non-aggregatable metric, you must provide values for all the dimensions that were defined when you created the insight.

For example, if you create a metric called "Rank customers by total spend by product category and location" using the RANK function, you can't later query that rank for product category alone. When querying the metric, you must always specify both "Product Category" and "Location."

SELECT
  ssot__SalesOrder__dlm.ssot__SalesChannelId__c AS channel__c,
  RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ASC) AS spend_rank__c,
  UnifiedIndividual__dlm.ssot__Id__c AS unified_individual__c
FROM
  ssot__SalesOrder__dlm
  JOIN IndividualIdentityLink__dlm
    ON ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = IndividualIdentityLink__dlm.SourceRecordId__c
  JOIN UnifiedIndividual__dlm
    ON IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c
GROUP BY
  unified_individual__c,
  channel__c

You can see whether a function produces a non-aggregatable metric in the object home screen of the Calculated Insights.

A screenshot of the aggregated functions in Calculated Insights

Aggregate Functions

Important
Important To compare the results of aggregate functions, use a CASE WHEN statement. Boolean comparison doesn't support aggregate functions.
Function Aggregatable Description Example
SUM Y Calculates the total by adding all values in the selected field. Supports numerical values. 
SELECT ProductID, SUM(Quantity)
FROM OrderDetails
GROUP BY ProductID
COUNT Y Counts all values in the selected field. Supports date, text, and boolean measures. COUNT(*) isn't supported. 
SELECT
COUNT(EmailEngagement__dlm.Id__c) as email_engagement_count__c,
UnifiedIndividual__dlm.Id__c as customer_id__c
FROM EmailEngagement__dlm
AVG Y Calculates the average of all the values in the selected field. Supports numerical values. 
SELECT avg(SalesOrder__dlm.TotalAmount__c) as avg__c,
SubQuery1.Id as customer_id__c
FROM SalesOrder__dlm
MIN Y Calculates the minimum value of the selected field. Supports numerical values. 
SELECT MIN(Price) FROM SalesOrder__dlm;
MAX Y Calculates the maximum value of the selected field. Supports numerical values. 
SELECT MAX(Price) FROM SalesOrder__dlm;
MEAN Y Calculates the average of the given dataset by dividing the total sum by the number of values in the dataset. 
SELECT AVG(sale_amount) FROM sales;
FIRST Y Returns the first value of the expression for a group of rows. 
SELECT first(col)
FROM VALUES (10), (5), (20) AS tab(col); -- 10
LAST Y Returns the last value of the expression for a group of rows. 
SELECT last(col)
FROM VALUES (10), (5), (20) AS tab(col); -- 20
APPROX_COUNT_DISTINCT N Returns the estimated cardinality by using the HyperLogLog++ algorithm, similar to COUNT DISTINCT. Use this function when you want to count unique occurrences of dimensions in a calculated insight. 
SELECT approx_count_distinct(col1)
FROM VALUES (1), (1), (2), (2), (3) tab(col1); -- 3
PERCENTILE N Returns the exact percentile of the numeric column at the given percentage. The percentage must be from 0.0 through 1.0. 
SELECT percentile(col, array(0.25, 0.75))
FROM VALUES (0), (10) AS tab(col); -- [2.5, 7.5]
STDDEV N Returns the statistical standard deviation of all values in the specified expression. 
SELECT stddev(col)

DateTime Functions

Function Aggregatable Description Example
HOUR(TIMESTAMP) N Returns the hour component of the string or timestamp. Return type: integer 0–23. 
SELECT hour('2009-07-30 12:58:59'); -- 12
DAY(DATE) N Returns the day of the month of the date or timestamp. Return type: integer 1–31. 
SELECT day('2009-07-30'); -- 30
MONTH(DATE) N Returns the month component of the date or timestamp. Return type: integer 1–12. 
SELECT month('2016-07-30'); -- 7
QUARTER(DATE) N Returns the quarter of the year for the date. Return type: integer 1–4. 
SELECT quarter('2016-08-31'); -- 3
YEAR(DATE) N Returns the year component of the date or timestamp. Return type: integer. 
SELECT year('2016-07-30'); -- 2016
CDPHOUR(TIMESTAMP) N Returns a timestamp representing the hour of the timestamp. Return type: timestamp. 
SELECT CdpHour('2009-07-30 01:10:05');
-- '2009-07-30 01:00:00'
CDPDAY(DATE) N Returns a timestamp representing the 0th hour of the day. Return type: timestamp. 
SELECT CdpDay('2009-07-30 01:10:05');
-- '2009-07-30 00:00:00'
CDPMONTH(DATE) N Returns a timestamp representing the month. Return type: timestamp. 
SELECT CdpMonth('2009-07-30 01:10:05');
-- '2009-07-01 00:00:00'
CDPQUARTER(DATE) N Returns a timestamp representing the first day of the quarter. Return type: timestamp. 
SELECT CdpQuarter('2009-07-30 01:10:05');
-- '2009-07-01 00:00:00'
CDPYEAR(DATE) N Returns a timestamp representing the first day of the year. Return type: timestamp. 
SELECT CdpYear('2009-07-30 01:10:05');
-- '2009-01-01 00:00:00'
CURRENT_DATE N Returns the current date at the start of query evaluation. All CURRENT_DATE calls within the same query return the same value. 
SELECT current_date(); -- 2020-04-25
DATE_SUB N Subtracts a number of days from the date value and returns the earlier date. 
SELECT date_sub('2016-07-30', 1); -- 2016-07-29
DATE_ADD N Adds a number of days to the date value and returns the later date. 
SELECT date_add('2016-07-30', 1); -- 2016-07-31
DATEDIFF N Returns the number of days from the start date to the end date. 
SELECT datediff('2009-07-31', '2009-07-30'); -- 1
MONTHS_BETWEEN N Returns the number of months between two dates. 
SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
-- 3.94959677
DAYOFWEEK N Returns the day of the week for a date or timestamp (1 = Sunday, 2 = Monday, ..., 7 = Saturday). 
SELECT dayofweek('2009-07-30'); -- 5
DAYOFMONTH N Returns the day of the month of the date or timestamp. 
SELECT dayofmonth('2009-07-30'); -- 30
DAYOFYEAR N Returns the day of the year of the date or timestamp. 
SELECT dayofyear('2009-07-30'); -- 211
TO_DATE N Parses the date string with the format expression to a date. Returns null if the input isn't valid. 
SELECT to_date('2016-12-31', 'yyyy-MM-dd'); -- 2016-12-31
TO_TIMESTAMP N Parses the timestamp string with the format expression to a timestamp. Returns null if the input isn't valid. 
SELECT to_timestamp('2016-12-31 00:12:00');
-- 2016-12-31 00:12:00
DATE_TRUNC N Returns the timestamp truncated to the unit specified by the format model. 
SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');
-- 2015-01-01 00:00:00
HOUR_ADD N Adds a number of hours to the datetime value and returns the later datetime. 
SELECT COUNT(SalesOrder__dlm.orderid__c) as orderid__c,
HOUR_ADD(SalesOrder__dlm.checkout_date__c, 2) as newDateHour__c
FROM SalesOrder__dlm GROUP BY newDateHour__c
HOUR_SUB N Subtracts a number of hours from the datetime value and returns the earlier datetime. 
SELECT COUNT(SalesOrder__dlm.orderid__c) as orderid__c,
HOUR_SUB(SalesOrder__dlm.checkout_date__c, 2) as newDateHour__c
FROM SalesOrder__dlm GROUP BY newDateHour__c
NOW N Returns the current date and time aligned to when the insight is processed. All NOW calls within the same query return the same value. Time is calculated at millisecond precision in UTC for your instance's region. 
SELECT NOW(); -- 2020-04-25T13:00:00.000Z
SECOND_ADD N Adds a number of seconds to a datetime value and returns the later datetime. 
SELECT SECOND_ADD('2020-04-25T13:00:00.000Z', 305);
-- 2020-04-25T13:05:05.000Z
SECOND_SUB N Subtracts a number of seconds from a datetime value and returns the earlier datetime. For example, use this function to include records from the last 5 minutes at the time of batch processing. 
SELECT SECOND_SUB('2020-04-25T13:00:00.000Z', 305);
-- 2020-04-25T12:54:55.000Z

Analytical Functions

Function Aggregatable Description Example
LAG N Returns the input value at the offset row before the current row in the window. The default offset is 1. Returns null if the input value at the offset row is null. 
SELECT a, b, lag(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 NULL  A1 1 1  A1 2 1  A2 3 NULL
LEAD N Returns the value at the offset row after the current row in the window. The default offset is 1. Returns null if there's no offset row. 
SELECT a, b, lead(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 2  A1 2 NULL  A2 3 NULL
NTILE N Divides the rows for each window partition into n buckets ranging from 1 to at most n. 
SELECT a, b, ntile(2)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 1  A1 2 2  A2 3 1
RANK N Computes the rank of a value in a group of values. The result is 1 plus the number of rows preceding the current row in the ordering of the partition. Produces gaps in the sequence. 
SELECT a, b, rank(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 1  A1 2 3  A2 3 1
PERCENT_RANK N Computes the percentage ranking of a value in a group of values. 
SELECT a, b, percent_rank(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 0.0  A1 1 0.0  A1 2 1.0  A2 3 0.0
DENSE_RANK N Computes the rank of a value in a group of values. Unlike RANK, doesn't produce gaps in the ranking sequence. 
SELECT a, b, dense_rank(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 1  A1 2 2  A2 3 1
ROW_NUMBER N Assigns a unique, sequential number to each row starting with 1, based on the ordering of rows within the window partition. 
SELECT a, b, row_number()
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 2  A1 2 3  A2 3 1
FIRST_VALUE N Returns the first value of the expression for each row in the window. 
SELECT first_value(col)
FROM VALUES (10), (5), (20) AS tab(col); -- 10
LAST_VALUE N Returns the last value of the expression for each row in the window. 
SELECT last_value(col)
FROM VALUES (10), (5), (20) AS tab(col); -- 20
REGEXP N Searches strings for patterns in text. 
SELECT * FROM Customers
WHERE name REGEXP '^[A-Z][a-z]+'
REGEXP_EXTRACT N Creates dimension values by extracting them from a source dimension using Google RE2 regular expressions. 
SELECT regexp_extract('100-200', '(\\d+)-(\\d+)', 1); -- 100
REGEXP_REPLACE N Extends REPLACE by letting you search a string for a regular expression pattern. 
SELECT regexp_replace('100-200', '(\\d+)', 'num'); -- num-num
ISNULL N Returns true if the expression is null; otherwise, returns false. 
SELECT isnull(1); -- false
ISNOTNULL N Returns true if the expression isn't null; otherwise, returns false. 
SELECT isnotnull(1); -- true
IFNULL N Returns the second argument if the first argument is null; otherwise, returns the first argument. 
SELECT ifnull(null, 'default'); -- default
NULLIF N Returns null if the two expressions are equal; otherwise, returns the first expression. 
SELECT nullif(2, 2); -- NULL
CONTAINS N Searches for a word or phrase in one or more text columns using precise or fuzzy matching. 
SELECT * FROM COMPANY
WHERE CONTAINS(products, "product1")
LIKE N Searches for a specific pattern in a string value. 
SELECT * FROM Customers
WHERE last_name LIKE 'R%';
RLIKE N Searches a string for a regular expression pattern. 
SELECT id, text_field, text_field RLIKE '..number'
FROM SalesOrder__dlm ORDER BY id;
NOTLIKE N Ignores values that match the specified pattern in the column. 
SELECT * FROM Customers
WHERE last_name NOTLIKE 'R%'
NOTRLIKE N Ignores values that match a regular expression pattern in the column. 
SELECT * FROM SalesOrder__dlm
WHERE description__c NOTRLIKE '[0-9]+'
CASE N Evaluates conditions and returns a value when the first condition is met. If no conditions are true, the ELSE value is returned (or null if there's no ELSE clause). 
SELECT
  CASE
    WHEN SUM(ssot__Order__dlm.ssot__TotalAmount__c) > 500
    THEN 'high spender'
    ELSE 'low spender'
  END AS spend_label__c,
  Individual__dlm.ssot__Id__c AS customer_id__c
FROM ssot__Order__dlm
  JOIN IdLink__dlm ON ssot__Order__dlm.ssot__SoldId__c = IdLink__dlm.RecId__c
  JOIN UnifiedIndividual__dlm ON IdLink__dlm.UnifiedRecId__c = Individual__dlm.ssot__Id__c
GROUP BY customer_id__c

Formatting Functions

Function Aggregatable Description Example
SUBSTRING N Extracts a substring from a string. Accepts a start index and a length. 
SELECT COUNT(SUBSTRING(ssot__Acct__dlm.ssot__AcctTypeId__c, 1, 9))
AS count_of_acct_type__c,
ssot__Acct__dlm.ssot__Id__c AS account_id__c
FROM ssot__Acct__dlm
GROUP BY account_id__c
SUBSTR N Extracts a substring from a string. Accepts a start position and a character count. 
substr(Account_Id, 13, 3)
RTRIM N Removes trailing space characters from a string. 
rtrim('2 Spaces After  ')
UPPER N Converts lowercase characters to uppercase. 
upper("Account.Industry")
LOWER N Converts uppercase characters to lowercase. 
lower("Account.Industry")
CONCAT N Returns a string by merging the values of specified fields and input strings. 
concat("OwnerId.FirstName", ' ', "OwnerId.LastName")

Math and Statistical Functions

Function Aggregatable Description Example
MOD N Returns the remainder after expr1 / expr2. 
SELECT MOD(3, 1.8);
ABS N Calculates the absolute value of a number (the value without its sign). 
abs(number)
RAND N Returns a random decimal number in [0, 1). Non-deterministic in the general case. Specify a seed to return a repeatable sequence. 
SELECT rand();   -- random
SELECT rand(0);  -- repeatable sequence
ROUND N Rounds a number to the specified number of decimal places. 
SELECT round(2.5, 0)
GREATEST N Returns the greatest value in the argument list, skipping null values. 
SELECT greatest(10, 9, 2, 4, 3);
LEAST N Returns the smallest value in the argument list, skipping null values. 
SELECT least(10, 9, 2, 4, 3);
LOG N Returns the logarithm of a number in a specified base. Arguments: base (the base of the logarithm) and number (the value to take the logarithm of). 
log(base, number)
EXP N Returns e raised to the power of the specified number. 
SELECT EXP(2);
BETWEEN N Selects values within a given range (inclusive). Values can be numbers, text, or dates. 
SELECT * FROM table
WHERE price BETWEEN 50 AND 100;

Boolean Functions

Function Aggregatable Description Example
IS_TRUE N Returns true if the value is true. 
SELECT count(Id) FROM Table
WHERE Is_True(Boolean_Column);
IS_FALSE N Returns true if the value is false. 
SELECT count(Id) FROM Table
WHERE Is_False(Boolean_Column);
HAS_BOOLEAN_VALUE N Returns true if the field contains a value; returns false if it's null or blank. 
SELECT count(Id) FROM Table
WHERE Has_Boolean_Value(Boolean_Column);
HAS_NO_BOOLEAN_VALUE N Returns true if the field's value is null or blank; returns false if it contains a value. 
SELECT count(Id) FROM Table
WHERE Has_No_Boolean_Value(Boolean_Column);