Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data analyst is querying a large Spark Delta table `web_events` in Microsoft Fabric. The table contains `event_id` (string), `user_id` (string), `event_timestamp` (timestamp), and `page_url` (string). The analyst needs to find the immediately preceding `page_url` visited by each user before the current event. Which Spark SQL window function should be used?
- ALEAD(page_url, 1) OVER (PARTITION BY user_id ORDER BY event_timestamp)
- BLAG(page_url, 1) OVER (PARTITION BY user_id ORDER BY event_timestamp)
- CNTH_VALUE(page_url, -1) OVER (PARTITION BY user_id ORDER BY event_timestamp)
- DFIRST_VALUE(page_url) OVER (PARTITION BY user_id ORDER BY event_timestamp)
Show answer & explanationAnswer & explanation
Correct answer: B. LAG(page_url, 1) OVER (PARTITION BY user_id ORDER BY event_timestamp)
The `LAG()` window function is designed to access data from a preceding row within the same partition, based on the specified order. `LAG(page_url, 1)` correctly retrieves the `page_url` from the row immediately before the current one for each `user_id`, ordered by `event_timestamp`.
Why the other options are wrong
- A. `LEAD()` retrieves the value from a *succeeding* row, which is the opposite of what is needed.
- C. `NTH_VALUE()` retrieves the Nth value. While `NTH_VALUE(page_url, 1)` could get the first, there is no direct way to specify 'immediately preceding' with a negative index like `-1` for `NTH_VALUE` in this context; `LAG` is the direct and appropriate function.
- D. `FIRST_VALUE()` retrieves the first value in the entire window, not the immediately preceding one.
Spark SQL LAG() Function
The `LAG()` window function returns the value of an expression from a row that precedes the current row by a specified offset within its partition.
- Requires an `OVER` clause with `PARTITION BY` and `ORDER BY`.
- Syntax: `LAG(expression, offset, default_value)`.
- Offset specifies how many rows back to look (default is 1).
- Default value is returned if the offset goes beyond the partition boundary (default is NULL).
Memory trick: Lag looks back, Lead looks forward, First/Last grab the ends!