23. Trailing 3 Calendar Days (Range Frame)
Difficulty: Hard · Topics: Window Functions, Dates, Filtering & Selection
Some days have no sales, so they are missing from sales. For each row, compute rev_3d: total revenue over the last 3 calendar days (the day itself and the two days before it), not the last 3 rows.
Return day, revenue, rev_3d, ordered by day.
Row order matters for this problem. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
sales
| day | revenue |
|---|---|
| 2024-05-01 | 10 |
| 2024-05-02 | 20 |
| 2024-05-04 | 40 |
| 2024-05-05 | 5 |
| 2024-05-09 | 7 |
Expected output
| day | revenue | rev_3d |
|---|---|---|
| 2024-05-01 | 10 | 10 |
| 2024-05-02 | 20 | 30 |
| 2024-05-04 | 40 | 60 |
| 2024-05-05 | 5 | 45 |
| 2024-05-09 | 7 | 7 |
Hints
Hint 1
rowsBetween(-2, 0) would look back 3 rows; with gaps that spans more than 3 days.Hint 2
A range frame needs a numeric ordering column: turn the date into a day number withF.datediff("day", F.lit("2000-01-01")).Hint 3
Then useWindow.orderBy("day_num").rangeBetween(-2, 0).PySpark functions you'll practise
- Range Between
- Window spec
- sum
- datediff
- select
- orderBy
Related problems
- Sessionize a Clickstream · Hard · Window Functions
- Three-Day Login Streak · Hard · Window Functions
- Latest Order per Customer · Medium · Window Functions
- Top Two Salary Levels per Department · Medium · Window Functions
- Build an SCD Type 2 History · Hard · 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