Skip to content

Exact Aggregates

Count

Counts the total number of events

Syntax:

Count([feature(s)]
      [by feature(s)]
      [where condition]
      [after time]
      [last duration]
      [exclusive])

Code Examples:

Count()
Count(by account_id)
Count(by email last week)
Count(by ip, user_agent where country!="us" last 10 minutes)
Count(by email after latest_login)
Count(by email after latest_login last 2.5 hours)
Count(by ip where verdict="Block" exclusive)
Count(session_id by email)

Data Example:

user ip Count() Count(by ip) Count(by ip, user)
anna 1.1.1.1 1 1 1
brad 2.2.2.2 2 1 1
cela 1.1.1.1 3 2 1
anna 1.1.1.1 4 3 2

CountUnique

Counts the number of unique values for a given feature

Syntax:

CountUnique(feature(s)
            [by feature(s)]
            [where condition]
            [after time]
            [last duration]
            [exclusive])

Code Examples:

CountUnique(device_id by user)
CountUnique(email by ip last week)
CountUnique(ip, user_agent by device_id where is_signed_in last 2 hours)
CountUnique(sku by user after latest_billing_change)
CountUnique(email by ip where verdict="Allow" exclusive)

Data Example:

user ip CountUnique(user) CountUnique(user by ip)
anna 1.1.1.1 1 1
brad 2.2.2.2 2 1
cela 1.1.1.1 3 2
anna 1.1.1.1 3 2

Sum

Computes the sum of values for a given feature

Syntax:

Sum(feature
    [by feature(s)]
    [where condition]
    [after time]
    [last duration]
    [exclusive])

Code Examples:

Sum(amount by user)
Sum(price by device_id last week)
Sum(bad_word_count by commenter where not is_admin last week)
Sum(price by email where price > 9.99)
Sum(price by email exclusive)

Data Example:

price user Sum(price) Sum(price by user) Sum(price by user exclusive)
1.50 anna 1.50 1.50 0.00
2.25 brad 3.75 2.25 0.00
0.50 cela 4.25 0.50 0.00
10.00 anna 14.25 11.50 1.50

Max

Computes the maximum of values for a given feature

Syntax:

Max(feature
    [by feature(s)]
    [where condition]
    [after time]
    [last duration]
    [exclusive])

Code Examples:

Max(amount by user)
Max(risk_score by device_id last week)
Max(price by email where risk_score > 75 last 4 hours)

Data Example:

risk_score user Max(risk_score) Max(risk_score by user)
68 anna 68 68
95 brad 95 95
84 brad 95 95
89 anna 95 89

Min

Computes the minimum of values for a given feature

Syntax:

Min(feature
    [by feature(s)]
    [where condition]
    [after time]
    [last duration]
    [exclusive])

Code Examples:

Min(amount by user)
Min(risk_score by device_id last week)
Min(price by email where risk_score > 75 last 4 hours)

Data Example:

risk_score user Min(risk_score) Min(risk_score by user)
68 anna 68 68
95 brad 68 95
84 brad 68 84
89 anna 68 68

Average

Computes the average of values for a given feature

Syntax:

Average(feature
       [by feature(s)]
       [where condition]
       [after time]
       [last duration]
       [exclusive])

Code Examples:

Average(amount by user)
Average(risk_score by device_id last week)
Average(price by email where risk_score > 75 last 4 hours)

Data Example:

amount user Average(amount) Average(amount by user)
15 anna 15 15
13.5 brad 14.25 13.5
16.5 brad 15 15
3 anna 12 9

StdDev

Computes the (population) standard deviation of values for a given feature

Syntax:

StdDev(feature
       [by feature(s)]
       [where condition]
       [after time]
       [last duration]
       [exclusive])

Code Examples:

StdDev(amount by user)
StdDev(risk_score by device_id last week)
StdDev(price by email where risk_score > 75 last 4 hours)

Data Example:

amount user StdDev(amount) StdDev(amount by user)
15 anna 0 0
13.5 brad 0.75 0
16.5 brad 1.2247 1.5
3 anna 5.3033 6

Median

Computes the median of values for a given feature

Syntax:

Median(feature
       [by feature(s)]
       [where condition]
       [after time]
       [last duration]
       [exclusive])

Code Examples:

Median(amount by user)
Median(risk_score by device_id last week)
Median(price by email where risk_score > 75 last 4 hours)

Data Example:

amount user Median(amount) Median(amount by user)
10 anna 10 10
3 brad 6.5 3
4 brad 4 3.5
2 anna 3.5 6
1 anna 3 2

MedianAbsoluteDeviation

Computes the median absolute deviation of values for a given feature

Syntax:

MedianAbsoluteDeviation(feature
                        [by feature(s)]
                        [where condition]
                        [after time]
                        [last duration]
                        [exclusive])

Code Examples:

MedianAbsoluteDeviation(amount by user)
MedianAbsoluteDeviation(risk_score by device_id last week)
MedianAbsoluteDeviation(price by email where risk_score > 75 last 4 hours)

Data Example:

amount user Median(amount by user) MedianAbsoluteDeviation(amount by user)
10 anna 10 0
3 anna 6.5 3.5
8 anna 8 2
2 anna 5.5 3
9 anna 8 2