Code error in "Return the last event in a window" example

Question

In the example given at https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-stream-analytics-query-patterns#return-the-last-event-in-a-window it seems to me that the JOIN condition: ON DATEDIFF(minute, Input.Time, LastInWindow.LastEventTime) BETWEEN 0 AND 10 is unnecessary and removing it would give the same result.

The condition ON DATEDIFF(minute, Input.Time, LastInWindow.LastEventTime) BETWEEN 0 AND 10 is used to ensure that the events from Input are within the 10-minute window defined by LastInWindow. However, since we are already grouping the events into 10-minute windows and selecting the last event in each window, this condition might seem redundant.

Let’s see how the code behaves without this condition:

-- CTE (Common Table Expression) to get the last event in each 10-minute window

Analysis

Without the DATEDIFF condition:

• The code selects the events from Input whose time exactly matches LastInWindow.LastEventTime.
  • This should work correctly because LastInWindow already contains only the last events of each 10-minute window.
  With the DATEDIFF condition:

  - The additional condition ensures we are within the correct time window, although this is already implied by the grouping and selection of the last event.
  

Conclusion

In this specific case, it seems that the DATEDIFF condition is redundant because LastInWindow already guarantees that we are working with the last events in each 10-minute window. Removing this condition should not affect the final result.

Answer 1

@David M. Rosenblum
Thank you for the feedback and using Microsoft Q&A platform.
You are correct that the JOIN condition in the example you provided may seem redundant. Since we are already grouping the events into 10-minute windows and selecting the last event in each window, the condition ON DATEDIFF(minute, Input.Time, LastInWindow.LastEventTime) BETWEEN 0 AND 10 might not be necessary.

However, it is possible that this condition was included as an extra precaution to ensure that only events within the correct time window are selected. In any case, removing this condition should not affect the final result.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.