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.
Quick Answer: This question evaluates data manipulation and time-series analysis skills, including rolling aggregations and year-over-year percentage calculations using SQL or Python.