Calculating year-over-year (YoY) growth for sales data often requires aggregating sales figures from two different years and comparing them. Below is a general SQL query structure you can follow, using CTEs (Common Table Expressions) for clearer breakdown.
Assuming you have a table called sales with the following columns:
- sale_date: the date of the sale (of type DATE)
- amount: the amount of the sale (numeric type)
SQL Query to Calculate YoY Growth:
WITH yearly_sales AS (
SELECT
EXTRACT(YEAR FROM sale_date) AS sale_year,
SUM(amount) AS total_sales
FROM sales
GROUP BY sale_year
),
sales_with_previous_year AS (
SELECT
curr.sale_year,
curr.total_sales,
COALESCE(prev.total_sales, 0) AS previous_year_sales
FROM yearly_sales curr
LEFT JOIN yearly_sales prev ON curr.sale_year = prev.sale_year + 1
)
SELECT
sale_year,
total_sales,
previous_year_sales,
CASE
WHEN previous_year_sales = 0 THEN NULL -- Avoid division by zero
ELSE (total_sales - previous_year_sales) / previous_year_sales * 100
END AS yoy_growth_percentage
FROM sales_with_previous_year
ORDER BY sale_year;
Explanation of the Query:
1. CTE yearly_sales: This CTE aggregates the sales data by year, summing the total sales for each year.
2. CTE sales_with_previous_year: This CTE joins the yearly_sales CTE on itself to bring in the total sales from the previous year for each current year.
3. Main SELECT Statement: The final SELECT statement retrieves the current year's sales and the previous year's sales. It calculates the YoY growth percentage by applying the formula:
\[
\text{YoY Growth \%} = \left(\frac{\text{Current Year Sales} - \text{Previous Year Sales}}{\text{Previous Year Sales}}\right) \times 100
\]
It includes a check to avoid division by zero by returning NULL if the previous year's sales are zero.
Important Note:
- This query assumes sales data is available for multiple years. If your database contains only one year's sales data, you'll need to adjust the data accordingly.
- The EXTRACT(YEAR FROM sale_date) function syntax might vary depending on the SQL dialect (e.g., in MySQL, you would use YEAR(sale_date)).
- Adjust the table and column names as per your database schema.