Forecast Next Year's Revenue Using YoY% Analysis
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
ad_revenue
+------------+---------+
| date | revenue |
+------------+---------+
| 2023-01-01 | 1000 |
| 2023-01-02 | 1200 |
| 2024-01-01 | 1500 |
| 2024-01-02 | 1600 |
| 2024-01-03 | 1550 |
+------------+---------+
##### Scenario
Advertising platform wants to understand and forecast revenue trends.
##### Question
Using table ad_revenue(date, revenue) compute the 30-day rolling sum of revenue for every calendar day. For each day, calculate the year-over-year percentage change (YoY%). Based on the latest YoY% figure, project total revenue for the next calendar year.
##### Hints
Apply WINDOW functions (SUM OVER, LAG, DATEADD) and extrapolate with YoY growth.
Overview: This question evaluates data manipulation and time-series analysis skills, including rolling aggregations and year-over-year percentage calculations using SQL or Python.
Using table ad_revenue(date, revenue) compute the 30-day rolling sum of revenue for every calendar day between the minimum and maximum dates in the data, including days with no rows as 0 revenue. For each day, calculate the year-over-year percentage change (YoY%) based on the 30-day rolling sums versus the same calendar date in the prior year. Then, based on the latest available YoY% figure, project total revenue for the next calendar year using the last fully completed calendar year as the base. For validation, return the daily rolling-revenue and YoY metric rows; the next-year projection can be discussed from the latest YoY value.
Tables
ad_revenue(date DATE, revenue INTEGER)
Hints
- Build a date spine from MIN(date) to MAX(date) to ensure every calendar day is present.
- Fill missing days with 0 revenue before computing rolling sums.