Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data analyst is querying a large Delta table named `product_sales` in Microsoft Fabric. The table contains `sale_id`, `product_id`, `sale_date`, and `revenue` columns. The analyst needs to retrieve the first `product_id` sold on each `sale_date` for each `product_id`, based on the `sale_id` (assuming `sale_id` indicates order of sale within a date). If multiple sales occur at the exact same `sale_id` (which is highly unlikely but possible in data), any of them is acceptable. Which Spark SQL function should the analyst use to achieve this?
- ARANK() OVER (PARTITION BY sale_date ORDER BY sale_id) = 1
- BFIRST_VALUE(product_id) OVER (PARTITION BY sale_date ORDER BY sale_id)
- CMIN(product_id) GROUP BY sale_date
- DROW_NUMBER() OVER (PARTITION BY sale_date ORDER BY sale_id) = 1
Show answer & explanationAnswer & explanation
Correct answer: B. FIRST_VALUE(product_id) OVER (PARTITION BY sale_date ORDER BY sale_id)
The `FIRST_VALUE()` window function is specifically designed to retrieve the value of an expression from the first row within its window frame, as defined by `PARTITION BY` and `ORDER BY`. Here, `PARTITION BY sale_date` ensures the 'first' is determined for each day, and `ORDER BY sale_id` defines which sale comes first. `FIRST_VALUE(product_id)` then picks that product ID.
Why the other options are wrong
- A. `RANK() = 1` could also identify the first sale, but `FIRST_VALUE()` is more direct for extracting the value itself.
- C. `MIN(product_id) GROUP BY sale_date` would return the alphabetically or numerically smallest `product_id` for each date, not necessarily the one from the first sale by `sale_id`.
- D. `ROW_NUMBER() = 1` would give the entire row of the first sale, but the question specifically asks for the 'first `product_id`', implying a direct value retrieval.
Spark SQL FIRST_VALUE()
A Spark SQL window function that returns the value of the expression from the first row in the window frame, as defined by the `PARTITION BY` and `ORDER BY` clauses.
- Useful for identifying the initial or earliest value within a group.
- Requires an `ORDER BY` clause within the `OVER()` to define 'first'.
- Can be used with an optional `IGNORE NULLS` clause.
Memory trick: First in line gets the prize.