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.
Quick Answer: 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.