31. Year-over-Year Growth
Difficulty: Medium · Topics: Joins, Filtering & Selection
monthly_revenue has one row per (year, month). For every row, add prev_year_revenue (same month, previous calendar year) and yoy_pct = growth in percent, rounded to 1 decimal.
If there is no row for the same month last year, both are null. Note: some years are missing entirely. Return year, month, revenue, prev_year_revenue, yoy_pct.
Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
monthly_revenue
| year | month | revenue |
|---|---|---|
| 2022 | 1 | 100 |
| 2022 | 2 | 80 |
| 2023 | 1 | 150 |
| 2023 | 2 | 60 |
Expected output
| year | month | revenue | prev_year_revenue | yoy_pct |
|---|---|---|---|---|
| 2022 | 1 | 100 | null | null |
| 2022 | 2 | 80 | null | null |
| 2023 | 1 | 150 | 100 | 50 |
| 2023 | 2 | 60 | 80 | -25 |
Hints
Hint 1
lag over partitionBy("month").orderBy("year") returns the previous available year, which is wrong when a year is missing.Hint 2
Instead shift the table by one year (year + 1) and join it back on (year, month).Hint 3
A left join keeps rows that have no previous year.PySpark functions you'll practise
- join
- select
Related problems
- Customers Without Orders · Medium · Joins
- Employees and Their Departments · Medium · Joins
- Employees Earning More than Their Manager · Medium · Joins
- Joining on Nullable Keys · Medium · Joins
- Price Valid at Order Time (Range Join) · Hard · Joins
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