Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Hard
A financial analyst is reviewing stock data in a Spark Delta table. They need to identify periods where the `closing_price` of a stock continuously increased for at least 3 consecutive days. Which advanced Spark SQL technique would be most effective for this pattern detection?
- ASelf-join with multiple conditions
- BWindow functions with LAG and conditional logic
- CCommon Table Expressions (CTEs) for simple aggregation
- DSubqueries with correlated predicates
Show answer & explanationAnswer & explanation
Correct answer: B. Window functions with LAG and conditional logic
Window functions, specifically `LAG`, are highly effective for comparing a row's value with previous rows within a partition (e.g., for each stock). By comparing the current `closing_price` with the `LAG` of the price for the previous two days, and applying conditional logic (`WHERE current > prev1 AND prev1 > prev2`), patterns like consecutive increases can be efficiently detected.
Why the other options are wrong
- A. While possible, multiple self-joins for N consecutive days can become very complex and inefficient for larger N.
- C. CTEs improve readability but don't inherently provide the sequential comparison logic needed for this problem; they would still rely on other techniques like window functions or joins.
- D. Subqueries with correlated predicates are generally less performant and more complex for this type of sequential pattern detection compared to window functions.
Spark SQL LAG Function
The `LAG(column, offset, default)` window function in Spark SQL retrieves the value of a column from a row that is a specified `offset` number of rows before the current row within its partition.
- Used for comparing current row with previous rows.
- Requires `OVER (PARTITION BY ... ORDER BY ...)` clause.
- Essential for time-series analysis and pattern detection.
- Can specify a default value if no prior row exists.
Memory trick: Lag to look back, Lead to look forward, Windows for rows.