Improve YoY Revenue Analysis with Complementary Metrics
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
ads_revenue
+------------+-----------+
| date | revenue |
+------------+-----------+
| 2023-01-01 | 120000 |
| 2023-02-01 | 125500 |
| 2024-01-01 | 140000 |
| 2024-02-01 | 145000 |
| 2024-03-01 | 150000 |
+------------+-----------+
##### Scenario
Analyzing advertising revenue performance
##### Question
Write SQL to calculate Year-over-Year (YoY) ads revenue by month. What potential pitfalls exist when using a rolling sum in this context? Suggest ways to improve or complement the YoY growth-rate metric.
##### Hints
Think window functions, seasonality, data sparsity, normalization.
Overview: This question evaluates a data scientist's competency in time-series data manipulation and metric validation, focusing on calculating Year-over-Year advertising revenue with SQL/Python and window functions while recognizing pitfalls associated with rolling sums.
Given a monthly advertising revenue table, write SQL to calculate Year-over-Year (YoY) ads revenue by month. For each month, return the current month's revenue, the same month’s revenue from the prior year, the absolute YoY change, and the YoY percentage change. Months without a prior-year comparator (or with zero prior-year revenue) should return NULL for the YoY metrics. Then, discuss what potential pitfalls exist when using a rolling sum in this context and suggest ways to improve or complement the YoY growth-rate metric.
Tables
ads_revenue(date DATE, revenue INTEGER)
Hints
- Self-join each month to the same calendar month in the prior year rather than relying blindly on LAG(12).
- Guard against divide-by-zero and missing prior-year months when computing YoY percent; return NULL in those cases.