Find Minimum And Maximum Values of any Numerical Field in Session

Find minimum and maximum values of any numerical field in a session using the session() function

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0{{Aggregate}} result{{Result Set}} repo --> 0 0 --> result
logscale
groupBy(cookie_id, function=session([max(bet),min(bet)]))

Introduction

The session() function can be used to group related events into sessions.

A session contains events that occur within a specified time interval. By default, this interval is 15 minutes. You can modify this interval by setting the maxpause parameter.

The session() function then calculates aggregate values across all events in each session.

In this example, the session() function is used to find minimum and maximum values of the field bet in a session. The session() function groups events by a given timespan.

Example incoming data might look like this:

timestampcookie_idbetaction_typecategory
2025-05-15 05:30:00user12325.99purchaseelectronics
2025-05-15 05:32:00user12349.99purchaseelectronics
2025-05-15 05:34:00user12315.99purchaseaccessories
2025-05-15 05:48:00user12399.99purchaseappliances
2025-05-15 05:49:00user123150.00purchasefurniture
2025-05-15 05:35:00user45675.50purchaseclothing
2025-05-15 05:37:00user456199.99purchaseappliances
2025-05-15 05:40:00user45689.99purchaseelectronics
2025-05-15 05:30:00user78910.99purchasebooks
2025-05-15 05:55:00user78920.99purchasebooks

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0{{Aggregate}} result{{Result Set}} repo --> 0 0 --> result style 0 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    groupBy(cookie_id, function=session([max(bet),min(bet)]))

    Groups events by the field cookie_id (unique user identifier) and creates sessions of 15-minutes timeout (the default value of the maxpause parameter), then calculates the maximun and minimum values of the field bet for each session, returning the results in new fields named _max and _min.

  3. Event Result set.

Summary and Results

The query is used to analyze the likelihood (the bet) of the behavior within user sessions. This query is, for example, useful for identifying if the event was an attempt to hack the system.

Sample output from the incoming example data:

cookie_id_max_min
user12349.9915.99 // First session
user123150.0099.99 // Second session
user456199.9975.50 // Single session
user78910.9910.99 // First session
user78920.9920.99 // Second session

Note that each session shows its own min/max values.