22. 7-Day Moving Average
Difficulty: Medium · Topics: Window Functions
The daily_sales table has one row per day. Add ma_7: the average revenue of the current day and the six rows before it (fewer at the start), rounded to 2 decimals.
Return day, revenue, ma_7, ordered by day. A day with missing revenue (null) is not counted in the average.
Row order matters for this problem. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
daily_sales
| day | revenue |
|---|---|
| 2024-01-01 | 100 |
| 2024-01-02 | 120 |
| 2024-01-03 | 90 |
| 2024-01-04 | 110 |
| 2024-01-05 | 130 |
| 2024-01-06 | 80 |
| 2024-01-07 | 150 |
| 2024-01-08 | 170 |
| 2024-01-09 | 60 |
Expected output
| day | revenue | ma_7 |
|---|---|---|
| 2024-01-01 | 100 | 100 |
| 2024-01-02 | 120 | 110 |
| 2024-01-03 | 90 | 103.33 |
| 2024-01-04 | 110 | 105 |
| 2024-01-05 | 130 | 110 |
| 2024-01-06 | 80 | 105 |
| 2024-01-07 | 150 | 111.43 |
| 2024-01-08 | 170 | 121.43 |
| 2024-01-09 | 60 | 112.86 |
Hints
Hint 1
A window ordered byday with a frame .rowsBetween(-6, Window.currentRow).Hint 2
F.avg over that frame ignores nulls automatically.Hint 3
Round the average, then sort by day.PySpark functions you'll practise
- Window spec
- Rows Between
- avg
- orderBy
Related problems
- Forward-Fill Missing Readings · Hard · Window Functions
- Latest Order per Customer · Medium · Window Functions
- Running Total by Region · Medium · Window Functions
- Top Two Salary Levels per Department · Medium · Window Functions
- Month-over-Month Change · Medium · Window Functions
Browse
Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings
Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems