Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 2 question 5 discussion

Actual exam question from Microsoft's DP-203

Question #: 5
Topic #: 2

HOTSPOT -
You are processing streaming data from vehicles that pass through a toll booth.
You need to use Azure Stream Analytics to return the license plate, vehicle make, and hour the last vehicle passed during each 10-minute window.
How should you complete the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

Box 1: MAX -
The first step on the query finds the maximum time stamp in 10-minute windows, that is the time stamp of the last event for that window. The second step joins the results of the first query with the original stream to find the event that match the last time stamps in each window.
Query:

WITH LastInWindow AS -
(

SELECT -

MAX(Time) AS LastEventTime -

FROM -

Input TIMESTAMP BY Time -

GROUP BY -
TumblingWindow(minute, 10)
)

SELECT -
Input.License_plate,
Input.Make,

Input.Time -

FROM -

Input TIMESTAMP BY Time -

INNER JOIN LastInWindow -
ON DATEDIFF(minute, Input, LastInWindow) BETWEEN 0 AND 10
AND Input.Time = LastInWindow.LastEventTime

Box 2: TumblingWindow -
Tumbling windows are a series of fixed-sized, non-overlapping and contiguous time intervals.

Box 3: DATEDIFF -
DATEDIFF is a date-specific function that compares and returns the time difference between two DateTime fields, for more information, refer to date functions.
Reference:
https://docs.microsoft.com/en-us/stream-analytics-query/tumbling-window-azure-stream-analytics

by elcholo at Sept. 4, 2021, 3:39 p.m.

Comments

Submit Cancel

rikku33

Highly Voted 3 years, 2 months ago

correct

upvoted 31 times

Jerrylolu

2 years, 11 months ago

Why not Hopping Window??

upvoted 1 times

auwia

1 year, 5 months ago

it needs 3 parameters in input.

upvoted 5 times

...

Wijn4nd

2 years, 11 months ago

Because a hopping window can overlap, and we need the data from 10 minute time frames that DON'T overlap

upvoted 12 times

...

GodfreyMbizo

Highly Voted 1 year, 10 months ago

answer is here https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-stream-analytics-query-patterns#return-the-last-event-in-a-window

upvoted 20 times

ExamDestroyer69

11 months, 3 weeks ago

The referenced link shows the provided answer is correct

upvoted 2 times

...

goldy29

1 year, 5 months ago

You are great!

upvoted 1 times

...

sdg2844

Most Recent 11 months, 1 week ago

I agree with the person who said this says to define the last event in that HOUR for the 10 minute window increments. So it should only return one value. This will return more than one value, as it returns the last values in EACH 10 minute window, not the maximum timestamp for a defined HOUR.

upvoted 1 times

...

blazy001

11 months, 4 weeks ago

error in code: wrong: DATEDIFF( minut, Input, LastInWindow) correct: DATEDIFF( minut, Input, LastEventTime)

upvoted 3 times

lola_mary5

9 months ago

Not according to Microsoft: https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-stream-analytics-query-patterns#return-the-last-event-in-a-window Special thanks to @GodfreyMbizo

upvoted 2 times

...

gggqqqqq

1 year, 2 months ago

DATEDIFF used in the SELECT statement uses the general syntax where a datetime column or expression is passed in as the second and third parameter. However, when the DATEDIFF function is used inside the JOIN condition, the input_source name or its alias is used. Internally the timestamp associated for each event in that source is picked.

upvoted 1 times

...

kkk5566

1 year, 3 months ago

correct

upvoted 1 times

...

mamahani

1 year, 6 months ago

max / tumblingwindow/datediff

upvoted 3 times

...

AZLearn111

1 year, 10 months ago

May be a dumb question but why the datediff condition when the other condition is exactly matching on timestamp. Is it not unnecessary?

upvoted 6 times

mamahani

1 year, 7 months ago

exactly my thought too; we already have the unique timestamp per 10 min windows, so why simply not match the car's (event) timestamp with the max? what is the value added of the datediff

upvoted 2 times

...

alphilla

11 months, 3 weeks ago

The reason for using DATEDIFF in this context is to create a condition for joining the two streams based on a time window. If you directly join on Input.Time = LastInWindow.LastEventTime, you are essentially checking for an exact match of timestamps. This might not capture events that are close to each other in time but are not exactly equal. By using DATEDIFF, you allow for a time range (0 to 10 minutes) within which events will be considered as part of the same window. This ensures that events occurring slightly before or after the last event in the window are included in the result.

upvoted 1 times

meatpoof

11 months, 1 week ago

it's AND Input.Time = LastInWindow.LastEventTime...i'm thinking this condition would invalidate anything that's only close in time, but not an exact match

upvoted 1 times

...

Bro111

2 years ago

Are "Input" and "LastInWindow" DateTime fields, to be comapred with datediff???

upvoted 2 times

Bro111

2 years ago

I have the answer here: https://learn.microsoft.com/en-us/stream-analytics-query/join-azure-stream-analytics

upvoted 4 times

...

anks84

2 years, 3 months ago

CORRECT

upvoted 1 times

...

Deeksha1234

2 years, 3 months ago

correct

upvoted 2 times

...

y203

2 years, 4 months ago

The full example with the answer is here: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-stream-analytics-query-patterns#return-the-last-event-in-a-window

upvoted 3 times

...

Sriramiyer92

2 years, 4 months ago

Correct! The keyword "each" in "last vehicle passed during each 10-minute window" pretty much makes it Clear!

upvoted 1 times

...

Revave2

2 years, 5 months ago

I understand the first two, but why datediff? They ask for the hour that the last vehicle went through, shouldn't that be datepart?

upvoted 3 times

sensaint

2 years ago

In order to match the correct inputs with the last event in window, the difference between both times should not exceed 10 minutes.

upvoted 2 times

...

PallaviPatel

2 years, 10 months ago

correct.

upvoted 1 times

...

BusinessApps

2 years, 10 months ago

HoppingWindow has a minimum of three arguments whereas TumblingWindow only takes two so considering the solution only has two arguments it has to be Tumbling https://docs.microsoft.com/en-us/stream-analytics-query/hopping-window-azure-stream-analytics https://docs.microsoft.com/en-us/stream-analytics-query/tumbling-window-azure-stream-analytics

upvoted 4 times

...

DrTaz

2 years, 11 months ago

Answer is 100% correct.

upvoted 2 times

...

Load full discussion...