Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data analyst is working with a Spark Delta table `web_traffic` containing `user_id`, `page_id`, and `timestamp`. They need to identify the first `page_id` visited by each `user_id` on a given day. Which Spark SQL window function is most appropriate for this task?
- AFIRST_VALUE(page_id) OVER (PARTITION BY user_id, date(timestamp) ORDER BY timestamp)
- BLAG(page_id, 1) OVER (PARTITION BY user_id, date(timestamp) ORDER BY timestamp)
- CROW_NUMBER() OVER (PARTITION BY user_id, date(timestamp) ORDER BY timestamp)
- DNTH_VALUE(page_id, 1) OVER (PARTITION BY user_id, date(timestamp) ORDER BY timestamp)
Show answer & explanationAnswer & explanation
Correct answer: A. FIRST_VALUE(page_id) OVER (PARTITION BY user_id, date(timestamp) ORDER BY timestamp)
The `FIRST_VALUE()` window function is specifically designed to retrieve the value of an expression from the first row within its window frame. By partitioning by `user_id` and `date(timestamp)` and ordering by `timestamp`, it will correctly identify the `page_id` of the very first visit for each user on each day.
Why the other options are wrong
- B. LAG() retrieves a value from a *preceding* row, not necessarily the first within the partition.
- C. ROW_NUMBER() assigns sequential numbers, which can be used to filter for the first row (e.g., `WHERE rn = 1`), but `FIRST_VALUE()` directly returns the value without an extra filtering step.
- D. NTH_VALUE(page_id, 1) is equivalent to FIRST_VALUE(page_id), but FIRST_VALUE is more semantically direct for specifically getting the first value.
Spark SQL FIRST_VALUE()
A Spark SQL window function that returns the value of the specified expression from the first row within its window frame, as defined by the PARTITION BY and ORDER BY clauses.
- Retrieves the value from the first row of a window.
- Requires `PARTITION BY` and `ORDER BY`.
- Useful for finding initial states, first events, or starting values.
- More direct than `ROW_NUMBER()=1` for just getting the value.
Memory trick: First Value, First Sight, in Each Partition's Ordered Line.