Coding questions · Meta
Forecast Next Year's Revenue Using YoY% Analysis
This question evaluates data manipulation and time-series analysis skills, including rolling aggregations and year-over-year percentage calculations using SQL or Python.
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.
30-day Rolling Ad Revenue YoY and Forecast
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.
Table ad_revenue
| column | type | constraints |
|---|---|---|
| date | DATE | |
| revenue | INTEGER |
Sample data:
| date | revenue |
|---|---|
| 2023-01-01 | 1000 |
| 2023-01-02 | 1200 |
| 2024-01-01 | 1500 |
| 2024-01-02 | 1600 |
| 2024-01-03 | 1550 |
Expected output
For each calendar day between the minimum and maximum dates in the sample data, output the raw revenue for that date, the 30-day rolling sum, the prior-year rolling sum on the same calendar date, and YoY% rounded to two decimals.
| date | prior_year_rolling_30d_revenue | revenue | rolling_30d_revenue | yoy_pct |
|---|---|---|---|---|
| 2023-01-01 | NULL | 1000 | 1000 | NULL |
| 2023-01-02 | NULL | 1200 | 2200 | NULL |
| 2023-01-03 | NULL | 0 | 2200 | NULL |
| 2024-01-01 | 1000 | 1500 | 1500 | 50 |
| 2024-01-02 | 2200 | 1600 | 3100 | 40.91 |
| 2024-01-03 | 2200 | 1550 | 4650 | 111.36 |
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.
- Use SUM(revenue) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) for a 30-day rolling window.
- Join to the prior year's date using DATEADD(year, -1, date) to fetch the prior-year 30-day rolling sum.
- Guard against division by zero or NULL when computing YoY%.
- Project next year's total by applying the latest YoY% to the last fully completed year's total revenue.