Year-Over-Year (YOY) Growth in SQL: The Ultimate Guide with Examples
Introduction
Year-over-Year (YOY) growth is a key metric used to measure business performance by comparing values from one period to the same period one year earlier. It eliminates seasonal fluctuations and highlights long-term trends.
Businesses rely on YOY growth to track revenue, customer base, profit margins, and market expansion. Understanding YOY growth helps companies make data-driven decisions and adjust their strategies based on long-term performance.
YOY Growth Formula
The standard formula for calculating YOY growth is:
YOY Growth (%) = ((Current Year Value - Previous Year Value) / Previous Year Value) × 100
Example Calculation:
A company's revenue in 2023 was $1,200,000, compared to $1,000,000 in 2022. The YOY growth is:
SELECT 2023 AS Year, 1200000 AS Revenue,
1000000 AS Prev_Year_Revenue,
((1200000 - 1000000) / 1000000) * 100 AS YOY_Growth;
Output:
| Year | Revenue ($) | Prev Year Revenue ($) | YOY Growth (%) |
|---|---|---|---|
| 2023 | 1,200,000 | 1,000,000 | 20.0% |
Data Model and Sample Data
To analyze YOY growth effectively, we use the following relational schema:
Sample Input Data
| Year | Revenue ($) | Customers | Net Profit ($) | Market Share (%) |
|---|---|---|---|---|
| 2022 | 1,000,000 | 5,000 | 200,000 | 10.0 |
| 2023 | 1,200,000 | 6,000 | 250,000 | 12.0 |
| 2024 | 1,440,000 | 7,200 | 312,500 | 14.4 |
Practical Use Cases
1. Revenue Growth
Tracking revenue growth is essential for understanding business performance over time.
SQL Query:
SELECT year, revenue,
LAG(revenue, 1) OVER (ORDER BY year) AS prev_year_revenue,
((revenue - LAG(revenue, 1) OVER (ORDER BY year)) / LAG(revenue, 1) OVER (ORDER BY year)) * 100 AS YOY_Growth
FROM financials;
Output:
| Year | Revenue ($) | Prev Year Revenue ($) | YOY Growth (%) |
|---|---|---|---|
| 2022 | 1,000,000 | NULL | NULL |
| 2023 | 1,200,000 | 1,000,000 | 20.0% |
| 2024 | 1,440,000 | 1,200,000 | 20.0% |
2. Customer Growth
Analyzing YOY customer growth helps businesses measure expansion.
SQL Query:
SELECT year, customers,
LAG(customers, 1) OVER (ORDER BY year) AS prev_year_customers,
((customers - LAG(customers, 1) OVER (ORDER BY year)) / LAG(customers, 1) OVER (ORDER BY year)) * 100 AS YOY_Growth
FROM financials;
Output:
| Year | Customers | Prev Year Customers | YOY Growth (%) |
|---|---|---|---|
| 2022 | 5,000 | NULL | NULL |
| 2023 | 6,000 | 5,000 | 20.0% |
| 2024 | 7,200 | 6,000 | 20.0% |
3. Profitability and Cost Efficiency
Understanding profitability trends helps assess financial health.
SQL Query:
SELECT year, net_profit,
LAG(net_profit, 1) OVER (ORDER BY year) AS prev_year_profit,
((net_profit - LAG(net_profit, 1) OVER (ORDER BY year)) / LAG(net_profit, 1) OVER (ORDER BY year)) * 100 AS YOY_Growth
FROM financials;
Output:
| Year | Net Profit ($) | Prev Year Profit ($) | YOY Growth (%) |
|---|---|---|---|
| 2022 | 200,000 | NULL | NULL |
| 2023 | 250,000 | 200,000 | 25.0% |
| 2024 | 312,500 | 250,000 | 25.0% |
4. Market Share and Performance
Evaluating market share growth helps measure competitiveness.
SQL Query:
SELECT year, market_share,
LAG(market_share, 1) OVER (ORDER BY year) AS prev_year_share,
((market_share - LAG(market_share, 1) OVER (ORDER BY year)) / LAG(market_share, 1) OVER (ORDER BY year)) * 100 AS YOY_Growth
FROM financials;
Output:
| Year | Market Share (%) | Prev Year Share (%) | YOY Growth (%) |
|---|---|---|---|
| 2022 | 10.0 | NULL | NULL |
| 2023 | 12.0 | 10.0 | 20.0% |
| 2024 | 14.4 | 12.0 | 20.0% |
YOY vs. MoM vs. CAGR
While YOY growth is valuable for annual comparisons, other metrics serve different purposes:
- Month-over-Month (MoM): Measures short-term growth between consecutive months
- Compound Annual Growth Rate (CAGR): Calculates the average annual growth rate over multiple years
How to Calculate YOY Growth in SQL
SQL provides powerful tools for YOY calculations:
- Window Functions: Use LAG() to access previous year's values
- Self Joins: Join a table with itself to compare years
- CTEs: Create temporary result sets for complex calculations
Common Mistakes and Best Practices
- Handle NULL Values: Use COALESCE or ISNULL for first-year calculations. Learn more about handling NULL values in our comprehensive COALESCE guide.
- Data Quality: Ensure consistent date formats and complete datasets
- Seasonal Adjustments: Consider seasonal patterns in your analysis
- Percentage Formatting: Format results appropriately for reporting
Visualizing YOY Growth with a Database Schema
Try these examples yourself using RunSQL's SQL playground. Our platform makes it easy to:
- Create tables using DBML
- Import sample data
- Run and share SQL queries
- Visualize results
Conclusion
YOY growth analysis is crucial for business intelligence and decision-making. By mastering SQL calculations and understanding best practices, you can effectively track and analyze performance metrics over time.
Ready to practice YOY calculations? Try RunSQL for free and experiment with these examples in a live environment.