Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data analyst is examining a Spark Delta table named `customer_interactions` in Microsoft Fabric. The table includes `customer_id` (int), `interaction_type` (string), and `interaction_timestamp` (timestamp). The analyst wants to find the first `interaction_type` for each customer. Which Spark SQL function can efficiently retrieve this information?
- AFIRST(interaction_type) OVER (PARTITION BY customer_id ORDER BY interaction_timestamp)
- BFIRST_VALUE(interaction_type) OVER (PARTITION BY customer_id ORDER BY interaction_timestamp)
- CNTH_VALUE(interaction_type, 1) OVER (PARTITION BY customer_id ORDER BY interaction_timestamp)
- DMIN(interaction_type) OVER (PARTITION BY customer_id ORDER BY interaction_timestamp)
Show answer & explanationAnswer & explanation
Correct answer: B. FIRST_VALUE(interaction_type) OVER (PARTITION BY customer_id ORDER BY interaction_timestamp)
`FIRST_VALUE()` is specifically designed to retrieve the value of an expression from the first row of the window frame. By partitioning by `customer_id` and ordering by `interaction_timestamp`, it correctly identifies the earliest `interaction_type` for each customer.
Why the other options are wrong
- A. `FIRST()` is a deprecated aggregate function in Spark SQL and is not a window function in this context. It would typically be used in a `GROUP BY` clause without `OVER`.
- C. `NTH_VALUE(interaction_type, 1)` would also work, as it retrieves the 1st value. However, `FIRST_VALUE()` is semantically more direct and often preferred for simply getting the first value.
- D. `MIN()` would return the alphabetically smallest `interaction_type` within the window, not necessarily the one corresponding to the earliest timestamp, unless `interaction_type` itself is ordered in a specific way that aligns with time, which is unlikely.
Spark SQL FIRST_VALUE()
The `FIRST_VALUE()` window function returns the value of the specified expression from the first row of the window frame.
- Requires an `OVER` clause with `PARTITION BY` and `ORDER BY`.
- The `ORDER BY` clause determines what constitutes the 'first' row.
- Useful for retrieving initial states or values within groups.
- Can be used with an optional `IGNORE NULLS` clause.
Memory trick: First value is easy, just `FIRST_VALUE` in the `OVER` and `ORDER BY`!