Coding questions · Meta
Improve YoY Revenue Analysis with Complementary Metrics
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.
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.
Year-over-Year Ads Revenue by Month
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.
Table ads_revenue
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| revenue | INTEGER | NOT NULL |
Sample data:
| date | revenue |
|---|---|
| 2023-01-01 | 120000 |
| 2023-02-01 | 125500 |
| 2024-01-01 | 140000 |
| 2024-02-01 | 145000 |
| 2024-03-01 | 150000 |
Expected output
One row per calendar month with current revenue, prior-year same-month revenue, absolute YoY change, and YoY percentage change. Months without a prior-year comparator (or with zero prior-year revenue) should return NULL for the YoY metrics. In discussion, candidates should note pitfalls of using rolling sums for this purpose (e.g., partial windows at dataset edges, masking of turning points, distortion from missing months and outliers, and mixing different month lengths) and suggest complementary metrics such as absolute change, daily-normalized revenue, trailing averages/sums with completeness checks, and multi-year or seasonally adjusted comparisons.
| date | prev_year_revenue | revenue | yoy_abs_change | yoy_pct_change |
|---|---|---|---|---|
| 2023-01-01 | NULL | 120000 | NULL | NULL |
| 2023-02-01 | NULL | 125500 | NULL | NULL |
| 2024-01-01 | 120000 | 140000 | 20000 | 0.1667 |
| 2024-02-01 | 125500 | 145000 | 19500 | 0.1554 |
| 2024-03-01 | NULL | 150000 | NULL | NULL |
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.
- Rolling sums smooth seasonality but can hide inflection points, suffer from partial windows at dataset edges, and be distorted by missing months or outliers.
- Consider complementing YoY percent with absolute change, daily-normalized revenue (revenue per day), trailing averages/sums with completeness checks, and multi-year or seasonally adjusted comparisons.