How to calculate rolling average per user in Real-Time Analytics

This tutorial shows how to calculate a rolling average per user in Real-Time Analytics, useful for monitoring behaviors such as average response time or average event score without losing real-time updates. We will explain why and how to implement an efficient sliding window.

Prerequisites

• Event source with timestamp and user_id (for example: telemetry, logs, or application events).
• Real-Time Analytics platform that supports sliding windows (for example: Azure Stream Analytics, Kafka Streams, Flink, or Kusto/ADX with streaming ingestion).
• Tools to test events (curl, kafka-console-producer, or a simple Python script).
• Basic knowledge of SQL-like streaming queries or KQL.

Step 1: Understand the problem and choose the window

Why: the moving average smooths variations and allows per-user analysis based on recent behavior. The choice of window depends on the case: short windows (e.g. 1 minute) for fast detectors; long windows (e.g. 1 hour) for trends.

Also decide whether you want a tumbling (non-overlapping) or sliding window. For per-user averages in real time it is common to use a sliding window with continuous updates.

Step 2: Structure the event

Ensure each event has at least: timestamp, user_id, value. Minimal JSON example for ingestion:

{
  "timestamp":"2026-10-24T12:34:56Z",
  "user_id":"user-123",
  "value": 42.5
}

Step 3: Implement the streaming query (conceptual SQL-like example)

This example shows the generic logic: group by user_id and apply a 5-minute sliding window with updates every 30 seconds. Adapt to your platform's syntax (Azure Stream Analytics, Flink SQL, Kafka Streams, etc.).

SELECT
  user_id,
  System.Timestamp() AS window_end,
  AVG(value) AS rolling_avg
FROM input_stream
GROUP BY
  user_id,
  HOPPINGWINDOW(second, 30, 300, timestamp)

Explanation: HOPPINGWINDOW(second, 30, 300, timestamp) creates a sliding window of 300s that advances every 30s. System.Timestamp() indicates the end of the window; AVG computes the average per user_id within each window.

Step 4: Optimize and handle late events

Late events can affect the average. Use tolerance for late events (watermark) and incremental updates. Conceptual parameter example:

-- Pseudocode for watermark (varies by platform)
WATERMARK ON timestamp DELAY 60s

This allows accepting events up to 60s after the timestamp without rejecting them. For platforms with a state store (Flink, Kafka Streams), set state limits and compaction per user_id to avoid unbounded growth.

Step 5: Emit results and store state

Decide whether to emit each update as a new row (metrics stream) or write to a database/table of aggregates. Conceptual example for output to a metrics table:

OUTPUT TO metrics_table
FIELDS (user_id, window_end, rolling_avg)

If you use a store with upsert (e.g. Azure SQL, Cosmos DB, ClickHouse), use the key (user_id, window_end) to replace the most recent average per window.

Step 6: Test with sample data

Generate events for a user and verify how the average evolves. Python example to send three events spaced by 20s (pseudo-send):

import requests, json, time
url = 'https://your-ingest-endpoint'
user = 'user-123'
for v in [10, 20, 30]:
    evt = {"timestamp":time.strftime('%Y-%m-%dT%H:%M:%SZ', time.gmtime()),
           "user_id":user, "value":v}
    requests.post(url, json=evt)
    time.sleep(20)

Analyze the emitted averages: after three events the rolling average should reflect the average of the values within the active window.

Verify the result

Confirm that for each user_id the metrics stream shows rolling_avg updated at the cadence (e.g. every 30s) and that values match the averages of events within the last 5 minutes. Common errors: out-of-order timestamps (check timezone), watermark too short rejecting events, and per-user state growing without bounds.

Conclusion

You calculated a rolling average per user in Real-Time Analytics using sliding windows, handling late events and state considerations. Next steps: adjust window/delay for your case and integrate alerts when rolling_avg exceeds thresholds. Tip: start with larger windows to validate and then reduce for greater sensitivity — which window do you consider appropriate for your case?