A data analytics team is using Microsoft Fabric to analyze customer feedback. They have a Spark Delta table `feedback_scores` with `customer_id`, `feedback_date`, and `score`. They need to calculate the average `score` for each customer, but only considering their feedback from the last 90 days relative to the current analysis date. Which PySpark function combination is best suited for this rolling average calculation?
- Adf.groupBy('customer_id', F.window('feedback_date', '90 days')) .agg(F.avg('score').alias('rolling_avg_score'))
- Bwindow_spec = Window.partitionBy('customer_id').orderBy('feedback_date') df.withColumn('rolling_avg_score', avg('score').over(window_spec.rowsBetween(Window.unboundedPreceding, Window.currentRow)))
- Cwindow_spec = Window.partitionBy('customer_id').orderBy('feedback_date') df.withColumn('rolling_avg_score', avg('score').over(window_spec.rowsBetween(-90, Window.currentRow)))
- Dwindow_spec = Window.partitionBy('customer_id').orderBy('feedback_date') df.withColumn('rolling_avg_score', avg('score').over(window_spec.rangeBetween(F.expr('-INTERVAL 90 DAYS'), Window.currentRow)))
Show answer & explanationAnswer & explanation
Correct answer: D. window_spec = Window.partitionBy('customer_id').orderBy('feedback_date') df.withColumn('rolling_avg_score', avg('score').over(window_spec.rangeBetween(F.expr('-INTERVAL 90 DAYS'), Window.currentRow)))
To calculate a rolling average based on a *time interval* (like 'last 90 days'), `rangeBetween()` is the correct window frame type. `rangeBetween(F.expr('-INTERVAL 90 DAYS'), Window.currentRow)` specifies a frame from 90 days before the current row's date to the current row's date. `rowsBetween()` works on row offsets, not time intervals.
Why the other options are wrong
- A. This `groupBy` with `F.window()` creates tumbling windows (fixed, non-overlapping time bins), not a sliding (rolling) window that moves row by row, which is required for a rolling average.
- B. `rowsBetween(Window.unboundedPreceding, Window.currentRow)` would calculate a cumulative average from the beginning for each customer, not a rolling 90-day average.
- C. `rowsBetween(-90, Window.currentRow)` defines a window of the 90 preceding *rows*, not a 90-day time interval, which is incorrect for this time-based requirement.
PySpark rangeBetween() for Time Windows
A PySpark window frame clause (`WindowSpec.rangeBetween()`) used to define a window based on a time interval relative to the current row's value in the ordering column, rather than a fixed number of rows.
- Defines window using value/time offsets, not row offsets.
- Requires an `orderBy()` clause on a numeric or timestamp column.
- Commonly used with `F.expr('-INTERVAL X DAYS')` for time-based windows.
- Ideal for rolling averages/sums over specific periods (e.g., 'last 30 days').
Memory trick: Range Between Time, for each Partition's Average.