The SQL part was fine, LAG() over a partition by month got me there.
Start by writing a clear SQL query that aggregates monthly ad revenue and computes YoY growth using a self-join or window function. Then discuss the pitfalls of rolling sums, such as seasonality and cumulative bias, and propose improvements like using YoY growth rates, seasonally adjusted metrics, or complementary metrics like MoM growth.
Pro tip: When discussing pitfalls, tie them to business impact—e.g., rolling sums can mask recent trends, leading to delayed decision-making. Also, mention that YoY growth alone can be misleading if the base month was anomalous, so suggest using a moving average of YoY growth for stability.
Confirm the table schema, revenue definition, and time granularity. State assumptions like handling missing months or incomplete data.
Use a CTE to aggregate monthly revenue, then self-join on year-1 to compute YoY growth. Alternatively, use LAG window function partitioned by month.
Discuss how rolling sums accumulate past data, obscuring recent changes, and can be skewed by seasonality or outliers. They also don't directly measure growth.
Suggest using YoY growth rate, seasonally adjusted data, or moving averages. Complement with metrics like MoM growth, contribution to growth, or cohort analysis.
Tie the metrics to decision-making, emphasizing the need for stable, interpretable trends that inform strategy.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.