You have a Fabric eventhouse that contains an KQL database. The database contains a table named TaxiData. The following is a sample of the data in TaxiData. VendorID tpep_pickup_datetime…

This question tests knowledge of KQL (Kusto Query Language) window functions and partitioning - specifically row_cumsum() for running totals partitioned by a grouping column, and first_value() combined with bin() for extracting the first occurrence per time bucket per partition.

Design and implement data ingestion and transformation

Question

You have a Fabric eventhouse that contains an KQL database. The database contains a table named TaxiData. The following is a sample of the data in TaxiData.
VendorID tpep_pickup_datetime tpep_dropoff_datetime passenger_count trip_distance PULocationID DOLocationID payment_type total_amount
2 08T11:08:33Z 2022-06-08T11:22:17Z 1 0.17 231 50 2 7.12
1 08T11:12:05Z 2022-06-08T11:20:43Z 1 1.02 161 163 1 10.56
2 08T11:15:00Z 2022-06-08T11:25:32Z 1 1.07 142 230 2 17.12
1 08T11:29:54Z 2022-06-08T11:49:34Z 2 2.07 162 236 2 12.01
1 08T11:50:50Z 2022-06-08T12:07:24Z 2 2.65 140 142 1 7.89
You need to build two KQL queries. The solution must meet the following requirements:
One of the queries must partition RunningTotalAmount by VendorID.
The other query must create a column named FirstPickupDateTime that shows the first value of each hour from tpep_pickup_datetime partitioned by payment_type.
How should you complete each query? To answer, drag the appropriate values the correct position.

Explanation

This question tests knowledge of KQL (Kusto Query Language) window functions and partitioning - specifically row_cumsum() for running totals partitioned by a grouping column, and first_value() combined with bin() for extracting the first occurrence per time bucket per partition.

Approach. For the RunningTotalAmount query, the correct approach uses row_cumsum(total_amount, VendorID != prev(VendorID)) after sorting by VendorID and tpep_pickup_datetime - the second argument acts as a restart condition so the cumulative sum resets at each new VendorID boundary. For the FirstPickupDateTime query, the correct approach uses bin(tpep_pickup_datetime, 1h) to bucket timestamps into hourly slots, then either summarize first_value(tpep_pickup_datetime) by payment_type, bin(tpep_pickup_datetime, 1h) or arg_min(tpep_pickup_datetime, *) grouped by payment_type and the hourly bin - this extracts the earliest timestamp within each hour for each payment type. Both queries rely on KQL's ability to partition analytic computations either through the partition by operator or through sort-dependent scalar functions like row_cumsum() and first_value().

Concept tested. KQL window/analytic functions in Microsoft Fabric Eventhouse: row_cumsum() with a restart predicate for partitioned running totals, and first_value() or arg_min() combined with bin() for time-bucketed first-occurrence aggregation, both partitioned by a categorical column.

Reference. https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/row-cumsum-function

Topics

#KQL#Window Functions#Data Aggregation#Time Series Analysis

Community Discussion

No community discussion yet for this question.

Full DP-700 Practice