Apprendre Calculating Growth Rates | Period-over-Period Comparisons

Growth rates are a core concept in finance, helping you measure how a value—such as revenue—changes from one period to the next. By calculating growth rates, you can quickly assess trends, spot acceleration or decline, and compare performance across different time frames. In SQL, growth rates are typically calculated by comparing values from consecutive periods, such as monthly or yearly totals.


              12345678
            
SELECT revenue_date, amount,
    LAG(amount) OVER (ORDER BY revenue_date) AS previous_amount,
    ROUND(
        (amount - LAG(amount) OVER (ORDER BY revenue_date)) 
        / NULLIF(LAG(amount) OVER (ORDER BY revenue_date), 0) * 100, 2
    ) AS percent_growth
FROM revenue
ORDER BY revenue_date;

The formula for calculating percentage growth between two periods is:
((current value - previous value) / previous value) * 100.
In SQL, you can use the LAG() window function to access the previous period's value within each row, making it possible to compute the growth rate directly in your query. The NULLIF() function prevents division by zero by returning NULL if the previous value is zero.


              1234567891011
            
SELECT revenue_date, amount,
    LAG(amount) OVER (ORDER BY revenue_date) AS previous_amount,
    CASE
        WHEN LAG(amount) OVER (ORDER BY revenue_date) IS NULL THEN NULL
        ELSE ROUND(
            (amount - LAG(amount) OVER (ORDER BY revenue_date)) 
            / NULLIF(LAG(amount) OVER (ORDER BY revenue_date), 0) * 100, 2
        )
    END AS percent_growth
FROM revenue
ORDER BY revenue_date;

When calculating growth rates, you may encounter missing data or the absence of a previous period—for instance, the first row in a time series. In these cases, SQL returns NULL for the growth calculation, signaling that a comparison cannot be made. Properly handling these NULL values is crucial for accurate reporting and interpretation, as it prevents misleading results and maintains data integrity.

Definition

NULL handling in SQL refers to the way SQL treats missing, undefined, or inapplicable values. When a calculation involves a NULL, the result is also NULL. Special functions like COALESCE(), IS NULL, and NULLIF() help you manage and interpret these values in your queries.

1. How do you calculate percentage growth between two periods in SQL?

2. Why is it important to handle NULLs in period comparisons?

3. What happens if there is no previous period for a row in a growth calculation?

Tout était clair ?

Merci pour vos commentaires !

Section 2. Chapitre 5

Demandez à l'IA

Posez n'importe quelle question ou essayez l'une des questions suggérées pour commencer notre discussion

Glissez pour afficher le menu


              12345678
            
SELECT revenue_date, amount,
    LAG(amount) OVER (ORDER BY revenue_date) AS previous_amount,
    ROUND(
        (amount - LAG(amount) OVER (ORDER BY revenue_date)) 
        / NULLIF(LAG(amount) OVER (ORDER BY revenue_date), 0) * 100, 2
    ) AS percent_growth
FROM revenue
ORDER BY revenue_date;