KQL Window Functions: serialize, prev, next, row_number, row_cumsum, row_rank_dense, row_rank_min, row_window_session, scan Operator, and Every Pattern for Fabric Real-Time Analytics
Our KQL Complete Guide covered KQL syntax, operators, functions, joins, time series, and anomaly detection. This post goes deeper into window functions — the KQL equivalents of SQL’s ROW_NUMBER(), RANK(), LAG(), LEAD(), and running aggregates. If you have ever used SQL window functions to rank rows, compute running totals, or access the previous row’s value, these are the KQL counterparts.
Think of window functions like a marathon scoring table. The timekeepers sit at the finish line and record each runner as they cross. They need the data in a specific order (serialize) to assign meaningful numbers: finish position (row_number), time gap from the previous runner (prev/next), cumulative runners finished (row_cumsum), and rankings that handle ties correctly (row_rank_dense, row_rank_min). Without the runners arriving in order, none of these calculations make sense. That is exactly why KQL requires serialization before any window function can run.
Table of Contents
– The serialize Operator – prev() — Access the Previous Row – next() — Access the Next Row – row_number() — Sequential Numbering – row_cumsum() — Running Totals – row_rank_dense() — Dense Ranking (No Gaps) – row_rank_min() — Standard Ranking (With Gaps) – row_window_session() — Automatic Session Detection – The scan Operator — Stateful Row Processing – Partitioned Windows (The restart Parameter) – KQL vs SQL Window Functions Comparison – Real-World Patterns – Pattern 1: Deduplicate Events (Keep Latest) – Pattern 2: Time Between Consecutive Events – Pattern 3: Running Revenue Total – Pattern 4: Top N per Group – Pattern 5: Detect Gaps in Time-Series Data – Pattern 6: User Session Analysis with row_window_session – Pattern 7: Compare Each Row to the Previous – Pattern 8: Multi-Step Sequence Detection with scan – Common Mistakes – Interview Questions – Wrapping Up
The serialize Operator
Before you can use any window function in KQL, the data must be serialized — KQL’s term for “the row order is guaranteed and preserved.” Without serialization, KQL processes rows in parallel across distributed nodes, meaning there is no guaranteed order. Window functions like prev(), next(), and row_number() depend on knowing which row comes before and after the current one, so the data must be ordered first.
Analogy — Shuffled playing cards. You cannot ask “what is the card before the 7 of hearts?” if the deck is randomly scattered on the table. You first need to arrange (serialize) the cards in a defined order — by suit and rank. Only then does “previous card” and “next card” have meaning.
Some operators automatically serialize their output — sort, top, and order by produce serialized row sets. If your query already sorts the data, you do not need an explicit serialize. But if your data is not sorted (or you need to guarantee serialization after a non-serializing operator), use serialize explicitly.
// Explicit serialization — marks the row set as ordered
sensor_readings
| where timestamp > ago(1h)
| serialize
| extend rn = row_number()
// Implicit serialization — sort already guarantees order
sensor_readings
| where timestamp > ago(1h)
| sort by device_id asc, timestamp asc
| extend rn = row_number()
// sort produces a serialized output, so row_number() works without explicit serializeImportant: The serialize operator stores the dataset in memory to preserve order. For very large datasets, this can impact performance. Prefer using sort (which both orders and serializes) over a standalone serialize when possible, and always filter with where before serializing to reduce the data volume.
Operators that preserve serialization (if the input is already serialized, the output remains serialized): extend, where, project, project-away, project-rename, take, mv-expand, and parse. Operators like summarize and join produce non-serialized output — you must re-sort or re-serialize after them.
prev() — Access the Previous Row
prev() returns the value from a previous row in the serialized set. By default, it looks back 1 row, but you can specify an offset to look further back. It also accepts a default value for when there is no previous row (the first row in the set).
Analogy — Checking the previous runner’s split time. At a relay race, each leg’s runner looks at the previous runner’s time to calculate the gap. The first runner has no predecessor — prev() returns null (or a default you specify) for that row.
// Access the previous row's value
sensor_readings
| where timestamp > ago(1h) and device_id == "sensor-042"
| sort by timestamp asc
| extend prev_temp = prev(temperature),
prev_time = prev(timestamp),
temp_change = temperature - prev(temperature),
time_gap_sec = datetime_diff("second", timestamp, prev(timestamp))
// Look back 2 rows (offset parameter)
| extend two_ago_temp = prev(temperature, 2)
// With a default value for the first row
| extend prev_temp_safe = prev(temperature, 1, 0.0)
// First row gets 0.0 instead of nullprev() syntax: prev(column, offset, default_value) — offset defaults to 1, default_value defaults to null.
next() — Access the Next Row
next() is the mirror of prev() — it looks forward in the serialized set. Where prev() answers “what was the last value?”, next() answers “what is the upcoming value?” This is useful for detecting transitions, finding the end of a sequence, or calculating how long until the next event.
// Access the next row's value
sensor_readings
| where timestamp > ago(1h) and device_id == "sensor-042"
| sort by timestamp asc
| extend next_temp = next(temperature),
next_time = next(timestamp),
time_until_next = datetime_diff("second", next(timestamp), timestamp)
// Detect state transitions: current status vs next status
device_events
| sort by device_id asc, timestamp asc
| extend current_status = status,
next_status = iif(device_id == next(device_id), next(status), "END")
| where current_status != next_status
// Returns only the rows where the status CHANGES — transition detectionrow_number() — Sequential Numbering
row_number() assigns a sequential integer to each row in the serialized set, starting from a value you specify (typically 1 or 0). Unlike SQL’s ROW_NUMBER() which uses OVER(ORDER BY …), KQL’s row_number() operates on the already-serialized row set — the ordering is done by sort before the function, not inside it.
// Basic row numbering (start at 1)
sensor_readings
| where timestamp > ago(1h)
| sort by temperature desc
| extend rn = row_number(1)
// Hottest reading = row 1, second hottest = row 2, etc.
// Start at 0
| extend rn = row_number(0)
// Row number per group (partitioned) — restart when device changes
sensor_readings
| where timestamp > ago(1h)
| sort by device_id asc, timestamp desc
| extend rn = row_number(1, prev(device_id) != device_id)
// rn resets to 1 at the start of each device_id group
// Row 1 = latest reading per device, row 2 = second latest, etc.The second parameter of row_number() is the restart condition — a boolean expression that, when true, resets the counter. This is how you partition row numbers by group without a SQL PARTITION BY clause.
row_cumsum() — Running Totals
row_cumsum() computes a cumulative sum as it walks through the serialized rows. Each row’s value is added to the running total from all previous rows. This is the KQL equivalent of SUM(column) OVER (ORDER BY … ROWS UNBOUNDED PRECEDING) in SQL.
Analogy — A tip jar at a coffee shop. Every customer drops in a different amount. The running total on the jar shows the cumulative donations. After customer 1 drops $2, the total is $2. Customer 2 drops $5, total becomes $7. Customer 3 drops $1, total becomes $8. That is row_cumsum.
// Running total of revenue
sales
| where order_date > ago(30d)
| sort by order_date asc
| extend running_revenue = row_cumsum(amount)
// Running total per customer (partitioned)
sales
| sort by customer_id asc, order_date asc
| extend customer_running_total = row_cumsum(amount, prev(customer_id) != customer_id)
// Resets the cumsum when customer_id changes
// Counting events cumulatively (useful for session numbering)
device_events
| sort by device_id asc, timestamp asc
| extend gap_min = datetime_diff("minute", timestamp, prev(timestamp))
| extend new_session = iif(gap_min > 5 or isnull(gap_min) or device_id != prev(device_id), 1, 0)
| extend session_id = row_cumsum(new_session)
// Each time new_session = 1, the cumsum increments → unique session IDsrow_rank_dense() — Dense Ranking (No Gaps)
row_rank_dense() assigns ranks to rows based on a column’s value. Rows with the same value get the same rank, and the next distinct value gets the next consecutive rank — no gaps. This is identical to SQL’s DENSE_RANK().
Analogy — A spelling bee. Three students score 95, two score 90, one scores 85. Dense ranking: 95 = rank 1, 90 = rank 2, 85 = rank 3. There is no gap — rank 2 follows rank 1 even though three students tied at rank 1.
// Dense rank by temperature (highest = rank 1)
sensor_readings
| where timestamp > ago(1h)
| sort by temperature desc
| extend rank = row_rank_dense(temperature)
// If three devices have temp 99, they all get rank 1.
// The next temperature (say 95) gets rank 2 — no gap.
// Dense rank per group (partitioned by device)
sensor_readings
| sort by device_id asc, temperature desc
| extend rank_in_device = row_rank_dense(temperature, prev(device_id) != device_id)
// Rank resets at each new device_idrow_rank_min() — Standard Ranking (With Gaps)
row_rank_min() assigns ranks with gaps after ties — identical to SQL’s RANK(). If three rows tie at rank 1, the next row gets rank 4 (not rank 2). The rank value equals the row’s position if there were no ties.
Analogy — An Olympic medal ceremony. Three athletes tie for gold (rank 1). There is no silver medal — the next athlete gets bronze (rank 4). The ranking reflects the actual number of athletes ahead of you, including ties.
// Standard rank with gaps
sensor_readings
| where timestamp > ago(1h)
| sort by temperature desc
| extend rank = row_rank_min(temperature)
// Three devices at temp 99 → all rank 1
// Next device at temp 95 → rank 4 (because 3 rows are ahead of it)
// Compare dense vs min rank side by side
sensor_readings
| sort by temperature desc
| extend dense_rank = row_rank_dense(temperature),
min_rank = row_rank_min(temperature)| Score | row_rank_dense | row_rank_min |
|---|---|---|
| 99 | 1 | 1 |
| 99 | 1 | 1 |
| 99 | 1 | 1 |
| 95 | 2 | 4 |
| 90 | 3 | 5 |
The choice between dense and min rank depends on your use case. Use row_rank_dense() when you want consecutive ranks for “top N distinct values” (top 3 scores means ranks 1, 2, 3). Use row_rank_min() when you need to know the actual position (how many items rank higher).
row_window_session() — Automatic Session Detection
row_window_session() is KQL’s built-in session detection function. Instead of manually computing gaps with prev() and flagging new sessions with row_cumsum() (which works but requires 4-5 lines), row_window_session() does it in a single function call. It returns the start timestamp of the session each row belongs to.
The function takes three required parameters and one optional restart condition. It creates a new session when either: the current row’s timestamp exceeds the session start plus MaxDistanceFromFirst (the session has lasted too long), or the gap between the current row and the previous row exceeds MaxDistanceBetweenNeighbors (the user went idle), or the restart condition is true (typically a different user/device).
// Automatic session detection
// MaxDistanceFromFirst = 1h (session cannot exceed 1 hour total)
// MaxDistanceBetweenNeighbors = 5m (gap > 5 min starts new session)
web_events
| sort by user_id asc, timestamp asc
| extend session_start = row_window_session(
timestamp, // Column to evaluate
1h, // MaxDistanceFromFirst — absolute session cap
5m, // MaxDistanceBetweenNeighbors — inactivity gap threshold
user_id != prev(user_id) // Restart condition — new user = new session
)
| summarize
event_count = count(),
session_duration_min = datetime_diff("minute", max(timestamp), min(timestamp)),
first_page = take_any(page_url)
by user_id, session_startHow the two distance parameters interact: MaxDistanceFromFirst caps the total session length — even if the user is continuously active, the session closes after this duration. MaxDistanceBetweenNeighbors detects idle periods — if the user pauses longer than this, a new session starts. A typical web analytics configuration is 30 minutes for the neighbor gap and 4 hours for the absolute cap.
// IoT device activity sessions
sensor_readings
| sort by device_id asc, timestamp asc
| extend activity_session = row_window_session(
timestamp,
4h, // Device session cannot exceed 4 hours
10m, // 10-minute silence = device went idle
device_id != prev(device_id)
)
| summarize
readings = count(),
avg_temp = avg(temperature),
session_length_min = datetime_diff("minute", max(timestamp), min(timestamp))
by device_id, activity_session
| where readings > 5
// Only sessions with meaningful activityThe scan Operator — Stateful Row Processing
The scan operator is KQL’s most powerful window tool — it lets you define state machines that process rows sequentially. While prev()/next() look at one adjacent row, scan maintains state across all rows and can match multi-step patterns. Think of it as a programmable cursor that walks through your data and remembers what it has seen.
Analogy — An airport boarding gate agent. The agent processes passengers one by one (sequential scan). They track state: how many passengers have boarded (counter), which zones have been called (state transitions), and whether to trigger an announcement when a threshold is reached. The agent makes decisions based on both the current passenger and the accumulated state — that is scan.
// scan with step definitions
// Detect "temperature rising then falling" patterns per device
sensor_readings
| where timestamp > ago(1h)
| sort by device_id asc, timestamp asc
| scan with_match_id = mid declare(prev_temp: real = 0.0) with
(
step rising: temperature > prev_temp => prev_temp = temperature;
step falling: temperature < prev_temp;
)
| where isnotempty(mid)
// Finds sequences where temperature went up then came back downThe scan operator is advanced and typically used for complex sequence detection in security analytics (multi-stage attack detection), IoT patterns (device state machines), and workflow analysis (multi-step user journeys).
Partitioned Windows (The restart Parameter)
In SQL, you partition window functions with OVER(PARTITION BY group_col ORDER BY sort_col). In KQL, partitioning is done via the restart parameter — a boolean expression that resets the window function when it evaluates to true. The most common restart condition is prev(group_col) != group_col, which fires when the group changes.
// SQL equivalent:
// ROW_NUMBER() OVER(PARTITION BY device_id ORDER BY timestamp DESC)
//
// KQL equivalent:
sensor_readings
| sort by device_id asc, timestamp desc
| extend rn = row_number(1, prev(device_id) != device_id)
// rn resets to 1 whenever device_id changes
// Running total per customer (PARTITION BY customer_id)
sales
| sort by customer_id asc, order_date asc
| extend running_total = row_cumsum(amount, prev(customer_id) != customer_id)
// Dense rank per category
products
| sort by category asc, price desc
| extend price_rank = row_rank_dense(price, prev(category) != category)Critical requirement: For the restart parameter to work correctly, the data MUST be sorted by the partition column first, then by the ordering column. If you sort by timestamp asc without sorting by device_id first, prev(device_id) != device_id will fire at random boundaries — the data is not grouped.
The partition by operator provides an alternative approach — it explicitly splits the data into groups before applying window functions:
// Using the partition operator (explicit partitioning)
sensor_readings
| where timestamp > ago(1h)
| partition by device_id
(
sort by timestamp desc
| extend rn = row_number(1)
| extend prev_temp = prev(temperature)
| extend temp_change = temperature - prev_temp
)
// Each device_id is processed independently — no restart parameter neededKQL vs SQL Window Functions Comparison
| SQL Window Function | KQL Equivalent | Notes |
|---|---|---|
| `ROW_NUMBER() OVER(ORDER BY col)` | `row_number(1)` | Sort before, not inside the function |
| `ROW_NUMBER() OVER(PARTITION BY g ORDER BY col)` | `row_number(1, prev(g) != g)` | Restart parameter replaces PARTITION BY |
| `RANK() OVER(ORDER BY col)` | `row_rank_min(col)` | Gaps after ties (1, 1, 3) |
| `DENSE_RANK() OVER(ORDER BY col)` | `row_rank_dense(col)` | No gaps (1, 1, 2) |
| `LAG(col, 1) OVER(ORDER BY ts)` | `prev(col)` | Look back 1 row |
| `LAG(col, n, default) OVER(…)` | `prev(col, n, default)` | Look back n rows with default |
| `LEAD(col, 1) OVER(ORDER BY ts)` | `next(col)` | Look forward 1 row |
| `SUM(col) OVER(ORDER BY ts ROWS UNBOUNDED PRECEDING)` | `row_cumsum(col)` | Running total |
| `SUM(col) OVER(PARTITION BY g ORDER BY ts ROWS UNBOUNDED PRECEDING)` | `row_cumsum(col, prev(g) != g)` | Partitioned running total |
| (no direct equivalent) | `row_window_session(ts, max_dist, gap)` | Built-in session detection |
| (no direct equivalent) | `scan` operator | Stateful sequence matching |
Key structural difference: In SQL, the window specification (OVER(PARTITION BY … ORDER BY …)) is embedded inside the function call. In KQL, the ordering is done as a separate sort step before the function, and partitioning is done via the restart parameter. This separation makes KQL window functions composable — you sort once and apply multiple window functions in the same extend.
Real-World Patterns
Pattern 1: Deduplicate Events (Keep Latest)
// Keep only the latest event per device (deduplicate by row_number)
sensor_readings
| sort by device_id asc, timestamp desc
| extend rn = row_number(1, prev(device_id) != device_id)
| where rn == 1
// Same result as: summarize arg_max(timestamp, *) by device_id
// But row_number gives you flexibility to keep top 3, filter, etc.Pattern 2: Time Between Consecutive Events
// Calculate the gap between consecutive events per device
sensor_readings
| sort by device_id asc, timestamp asc
| extend prev_ts = iif(device_id == prev(device_id), prev(timestamp), datetime(null))
| extend gap_seconds = datetime_diff("second", timestamp, prev_ts)
| where isnotnull(gap_seconds)
| summarize avg_gap = avg(gap_seconds), max_gap = max(gap_seconds) by device_id
| where max_gap > 300
// Devices with a gap exceeding 5 minutes — possible outagePattern 3: Running Revenue Total
// Cumulative revenue over time with daily granularity
sales
| summarize daily_revenue = sum(amount) by bin(order_date, 1d)
| sort by order_date asc
| extend cumulative_revenue = row_cumsum(daily_revenue)
| extend formatted = format_datetime(order_date, "yyyy-MM-dd")
| project formatted, daily_revenue, cumulative_revenuePattern 4: Top N per Group
// Top 3 highest temperatures per device
sensor_readings
| where timestamp > ago(24h)
| sort by device_id asc, temperature desc
| extend rn = row_number(1, prev(device_id) != device_id)
| where rn <= 3
| project device_id, temperature, timestamp, rnPattern 5: Detect Gaps in Time-Series Data
// Find gaps where a device stopped sending data for more than 10 minutes
sensor_readings
| where timestamp > ago(24h)
| sort by device_id asc, timestamp asc
| extend prev_ts = iif(device_id == prev(device_id), prev(timestamp), datetime(null))
| extend gap_minutes = datetime_diff("minute", timestamp, prev_ts)
| where gap_minutes > 10
| project device_id, gap_start = prev_ts, gap_end = timestamp, gap_minutes
| sort by gap_minutes desc
// Returns every gap longer than 10 minutes — investigate for sensor failuresPattern 6: User Session Analysis with row_window_session
// Complete session analysis: session count, avg duration, pages per session
web_events
| where timestamp > ago(7d)
| sort by user_id asc, timestamp asc
| extend session_start = row_window_session(timestamp, 4h, 30m, user_id != prev(user_id))
| summarize
pages = dcount(page_url),
duration_min = datetime_diff("minute", max(timestamp), min(timestamp)),
events = count()
by user_id, session_start
| summarize
total_sessions = count(),
avg_session_duration = avg(duration_min),
avg_pages_per_session = avg(pages),
avg_events_per_session = avg(events)
by user_id
| top 20 by total_sessions descPattern 7: Compare Each Row to the Previous
// Detect temperature spikes: current reading vs previous reading
sensor_readings
| where timestamp > ago(1h)
| sort by device_id asc, timestamp asc
| extend prev_temp = iif(device_id == prev(device_id), prev(temperature), real(null))
| extend temp_change = temperature - prev_temp
| extend pct_change = round(100.0 * temp_change / prev_temp, 1)
| where abs(pct_change) > 20
// Rows where temperature changed by more than 20% from the previous reading
| project device_id, timestamp, temperature, prev_temp, pct_changePattern 8: Multi-Step Sequence Detection with scan
// Detect login → privilege escalation → data access sequences
// within 15 minutes (potential security threat)
security_events
| where timestamp > ago(24h)
| sort by user_id asc, timestamp asc
| scan with_match_id = sequence_id declare(step_time: datetime = datetime(null)) with
(
step login: event_type == "login" => step_time = timestamp;
step escalation: event_type == "privilege_change"
and datetime_diff("minute", timestamp, step_time) < 15;
step access: event_type == "data_access"
and datetime_diff("minute", timestamp, step_time) < 15;
)
| where isnotempty(sequence_id)
| summarize steps = make_list(event_type),
first_event = min(timestamp),
last_event = max(timestamp)
by user_id, sequence_id
// Each matched sequence is a potential threat — all 3 steps within 15 minutesCommon Mistakes
1. Forgetting to serialize before using window functions. row_number(), prev(), next(), and all window functions require a serialized row set. If you skip sort or serialize, the query fails. Always sort by the relevant columns first.
2. Sorting by the wrong column order for partitioned windows. When using prev(device_id) != device_id as a restart condition, you MUST sort by device_id first, then by your ordering column. sort by timestamp asc, device_id asc groups by time, not by device — your restart condition fires randomly.
3. Using prev() across partitions without checking the boundary. prev(temperature) returns the last row’s temperature even if the last row belongs to a different device. Always guard cross-partition access: iif(device_id == prev(device_id), prev(temperature), real(null)).
4. Confusing row_rank_dense and row_rank_min. row_rank_dense gives consecutive ranks (1, 1, 2). row_rank_min gives ranks with gaps (1, 1, 3). Use dense for “top N distinct values” and min for “actual position among all rows.”
5. Not filtering before serializing. The serialize operator loads data into memory. Calling it on an unfiltered table with billions of rows crashes the query. Always where timestamp > ago(…) before any sort or serialize.
6. Using row_window_session without understanding both distance parameters. MaxDistanceFromFirst caps the total session length. MaxDistanceBetweenNeighbors detects idle gaps. Setting only one without considering the other leads to sessions that never close (no cap) or sessions that break during brief pauses (gap too small).
7. Using scan when prev/next would suffice. The scan operator is powerful but complex. If you only need to compare with the previous or next row, prev() and next() are simpler and faster. Reserve scan for multi-step sequence matching.
8. Expecting SQL-style PARTITION BY syntax. KQL does not have OVER(PARTITION BY …). Partitioning is done via the restart parameter (prev(group) != group) or the partition by operator. Trying SQL syntax in KQL gives a syntax error.
Interview Questions
Q: How does KQL implement window functions differently from SQL? A: SQL embeds the window specification inside the function call with OVER(PARTITION BY … ORDER BY …). KQL separates these concerns: ordering is done by a preceding sort operator, and partitioning is done via the restart parameter — a boolean expression that resets the function when true (typically prev(group_col) != group_col). This makes KQL window functions composable — you sort once and apply multiple functions in the same extend.
Q: What is the serialize operator and when do you need it? A: The serialize operator marks a row set as having guaranteed order, which is required for window functions. You need it when your data has not already been serialized by an ordering operator. Operators like sort, top, and order by automatically serialize their output, so if you sort before applying window functions, explicit serialize is unnecessary. Using serialize without sorting just preserves the current (arbitrary) order while marking it as safe for window functions.
Q: What is the difference between row_rank_dense and row_rank_min? A: Both rank rows based on a column’s value, but they handle ties differently. row_rank_dense() assigns consecutive ranks with no gaps — if three rows tie at rank 1, the next distinct value gets rank 2 (like DENSE_RANK in SQL). row_rank_min() assigns ranks with gaps — if three rows tie at rank 1, the next value gets rank 4 (like RANK in SQL). Use dense for “top N distinct values” and min for “how many rows rank above me.”
Q: How does row_window_session work and when would you use it? A: row_window_session(timestamp, MaxDistFromFirst, MaxDistBetweenNeighbors, restart) assigns each row to a session by returning the session’s start timestamp. A new session starts when: the gap between consecutive rows exceeds MaxDistBetweenNeighbors (idle detection), or the session length exceeds MaxDistFromFirst (absolute cap), or the restart condition is true (different user/device). It replaces the manual prev() + row_cumsum() pattern for session detection. Use it for user session analysis, IoT device activity, and any “group by activity gap” scenario.
Q: How do you implement SQL’s ROW_NUMBER() OVER(PARTITION BY group ORDER BY col) in KQL? A: Sort by the partition column first, then by the ordering column. Then use row_number() with a restart condition: sensor_readings | sort by device_id asc, timestamp desc | extend rn = row_number(1, prev(device_id) != device_id). This resets rn to 1 whenever device_id changes. Filtering where rn == 1 gives the latest reading per device, and where rn <= 3 gives the top 3.
Q: When would you use the scan operator instead of prev/next? A: Use scan when you need to detect multi-step sequences — patterns that span three or more events with conditions between them. For example, “login followed by privilege escalation followed by data access within 15 minutes.” The prev()/next() functions look at one adjacent row, but scan maintains state across the entire sequence and can match complex patterns with steps, conditions, and time constraints. For simple “compare with previous row” tasks, prev() is simpler and faster.
Q: How would you calculate a running total in KQL and reset it per group? A: Use row_cumsum() with a restart parameter. Sort by the group column, then by the ordering column, then apply row_cumsum(value, prev(group_col) != group_col). For example, running revenue per customer: sales | sort by customer_id asc, order_date asc | extend running_total = row_cumsum(amount, prev(customer_id) != customer_id). The cumulative sum resets to the current row’s amount whenever customer_id changes.
Wrapping Up
KQL window functions give you the same analytical power as SQL’s ROW_NUMBER, RANK, LAG, LEAD, and running aggregates — with a different syntax that separates ordering (sort) from the function call. The restart parameter replaces SQL’s PARTITION BY, and row_window_session() provides built-in session detection that SQL lacks entirely.
Master these eight functions (prev, next, row_number, row_cumsum, row_rank_dense, row_rank_min, row_window_session, scan) and you can handle any sequential analysis problem in Fabric Real-Time Analytics.
Related posts: – KQL Complete Guide – RTI Deep Dive (Windows, Materialized Views) – Real-Time Intelligence Overview – SQL Window Functions
← Previous: KQL Complete Guide
Fabric (27/37)
Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.