40. Fill Missing Dates (Calendar Spine)
Difficulty: Hard · Topics: Joins, Arrays, Aggregations, Dates, Null Handling, Filtering & Selection
daily_signups only has rows for days with at least one signup. For charts you need every day from the first to the last date in the table, with 0 on days without signups.
Return day and signups, ordered by day.
Row order matters for this problem. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
daily_signups
| day | signups |
|---|---|
| 2024-01-01 | 5 |
| 2024-01-03 | 2 |
| 2024-01-06 | 4 |
Expected output
| day | signups |
|---|---|
| 2024-01-01 | 5 |
| 2024-01-02 | 0 |
| 2024-01-03 | 2 |
| 2024-01-04 | 0 |
| 2024-01-05 | 0 |
| 2024-01-06 | 4 |
Hints
Hint 1
Find the first and last day withF.min/F.max.Hint 2
F.sequence(start_date, end_date) builds an array of every date in between; F.explode turns it into rows.Hint 3
Left-join the data onto this calendar and replace missing counts with 0.PySpark functions you'll practise
- agg
- min
- max
- join
- explode
- sequence
- fillna
- to_date
- select
- orderBy
Related problems
- Monthly Retention by Cohort · Hard · Joins
- Word Count · Medium · Arrays
- Three-Day Login Streak · Hard · Window Functions
- Customers Who Bought Every Product · Hard · Joins
- Sessionize a Clickstream · 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