Year on Year Growth Rate - Video Solutions

Problem Overview: The task is to compute the year‑on‑year (YoY) growth rate of a metric stored in a database table. For each year (and typically for each entity such as product or company), you aggregate the metric, find the value from the previous year, and calculate the percentage change using (current - previous) / previous.

Approach 1: Grouping Statistics + Left Join (O(n log n) time, O(n) space)

Start by aggregating the metric by year using GROUP BY. This produces one row per year with the total value you want to analyze. Next, self‑join this aggregated dataset with itself using a LEFT JOIN where the current year matches the previous year plus one. The join exposes both the current year's total and the previous year's total in the same row, allowing you to compute the YoY growth rate using a simple arithmetic expression.

This approach relies on standard SQL operations: grouping, joining, and arithmetic expressions. The key insight is that YoY growth is simply a comparison between adjacent years, so joining the dataset with a shifted version of itself solves the alignment problem. A LEFT JOIN ensures that the earliest year still appears even when no previous year exists. Aggregation and sorting during grouping typically lead to O(n log n) processing time in most SQL engines, with O(n) intermediate storage for the grouped results.

This method is widely supported across relational systems and works cleanly in MySQL where window functions may not always be the preferred option. It fits naturally with concepts from SQL, database querying, and relational joins.

Approach 2: Window Function with LAG (O(n log n) time, O(n) space)

An alternative solution uses a window function such as LAG(). After computing yearly aggregates, apply LAG(total_value) ordered by year to access the previous year's value in the same result set. The growth rate can then be calculated directly without a self‑join.

The advantage of this method is readability. Window functions make sequential comparisons explicit and eliminate the need for manual join conditions. Internally, the database still sorts rows by year, so the complexity remains roughly O(n log n) with O(n) memory for the window frame.

Recommended for interviews: The grouping + left join approach is the safest answer. It shows you understand relational joins and how to align records across time periods. Mentioning the window function alternative demonstrates deeper SQL knowledge and awareness of modern query features.