Year-over-Year (YoY) Growth Calculation & SQL Self Joins

Many of the powerful calculation in SQL can be done by joining a table on itself. Let’s say you have a table containing companies that received investment in a certain timeframe and you wanted to identify companies that received an investment from Great Britain following an investment from Japan. Then the following query will do the job:

SELECT DISTINCT japan_investments.company_name,
    japan_investments.company_permalink
  FROM tutorial.crunchbase_investments_part1 japan_investments
  JOIN tutorial.crunchbase_investments_part1 gb_investments
    ON japan_investments.company_name = gb_investments.company_name
   AND gb_investments.investor_country_code = 'GBR'
   AND gb_investments.funded_at > japan_investments.funded_at
 WHERE japan_investments.investor_country_code = 'JPN'
 ORDER BY 1

The above query can be run directly on Mode with their existing dataset.

Another common request in SQL is to compute YoY growth, it could be growth in revenue or number of clients.

Assume you have the following ‘sales’ table :

Then, this YoY calculation can be obtained via :

WITH cte AS (
    SELECT
        sales_year AS year,
        SUM(amount) AS total_sales
    FROM sales
    GROUP BY 1
)
SELECT
    t_cur.year AS year,
    t_cur.total_sales AS total_sales_cur_year,
    t_pre.total_sales AS total_sales_prev_year,
    ROUND((t_cur.total_sales/t_pre.total_sales) - 1, 2) AS growth_rate
FROM
  cte t_cur left join cte t_pre on t_cur.year = t_pre.year + 1

Which gives you the following result :

Here is the full code to try it yourself :

CREATE TABLE sales (
    id INT,
    sales_year INT,
    amount DECIMAL(10, 2)
);
INSERT INTO sales (id, sales_year, amount)
VALUES
    (1, 2022, 1000.00),
    (2, 2022, 1200.50),
    (3, 2022, 1500.50),
    (4, 2023, 1800.00),
    (5, 2023, 2000.00),
    (6, 2023, 2200.00); 
WITH cte AS (
    SELECT
        sales_year AS year,
        SUM(amount) AS total_sales
    FROM sales
    GROUP BY 1
)
SELECT
    t_cur.year AS year,
    t_cur.total_sales AS total_sales_cur_year,
    t_pre.total_sales AS total_sales_prev_year,
    ROUND((t_cur.total_sales / t_pre.total_sales) - 1, 2) AS growth_rate
FROM
  cte t_cur join cte t_pre on t_cur.year = t_pre.year + 1

References :

1. https://mode.com/sql-tutorial/sql-self-joins/
2. https://medium.com/@jiaop24/calculating-year-over-year-yoy-sales-growth-using-sql-ef877063007a