Calculate average delta change in KPIs over a period of time.

It is often that we have to calculate delta change in KPIs over a period of time. It might be on a daily, weekly, monthly or quarterly level. Monitoring changes in a product's KPIs is a very crucial task. Analysing trends will help us improve products.

In this blog, we will discuss a similar situation where we have to find the average monthly increase/decrease in total visits for an article in the last 6 months.

(Table contains data for the last 7 months for each article)

╔════════════════╦═══════════════╦═════════════════╗
║  article_id    ║ year_month    ║ total_visits    ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202201        ║ 700             ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202202        ║ 1100            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202203        ║ 1200            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202204        ║ 1440            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202205        ║ 4300            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202206        ║ 4500            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202207        ║ 4100            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 5              ║ 202203        ║ 9800            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 5              ║ 202204        ║ 12000           ║
╠════════════════╬═══════════════╬═════════════════╣
║ 5              ║ 202205        ║ 14000           ║
╚════════════════╩═══════════════╩═════════════════╝
--------------------------------------------------------------
The expected output for the article with ID 1
╔════════════════╦═════════════╗
║  article_id    ║ monthly_avg ║
╠════════════════╬═════════════╣
║ 1              ║ 760         ║
╚════════════════╩═════════════╝

The first step of solving this would be calculating the difference in visits between the current month and last month for every article. Here we can use a special function called LAG.

LAG function provides access to a row at a given physical offset that comes before the current row, which means the function gives us the nth last row value of a particular column. It takes at least 2 arguments in this case, first is the column and second is the offset.

We create a CTE to calculate the difference in visits by subtracting the current month's visits from last month's visits which we are getting by using the LAG function.

WITH cte_articles
AS (
    SELECT article_id
        ,year_month
        ,total_visits - (
            LAG(total_visits, 1) OVER (
                PARTITION BY article_id ORDER BY year_month ASC
                )
            ) AS diff_in_visits
    FROM articles
    )

The difficult task is now done. You must be wondering why didn't we add the filter to include just the last 6 months of data. That is because for calculating the monthly difference of the last 6 months we need 7 months of data and when the LAG function gives us the last offset value of row for the first month we get null.

This null is ignored when we calculate the average difference in visits using the AVERAGE function.

Calculating the average for every article.

SELECT article_id
    ,avg(diff_in_visits) AS monthly_avg
FROM cte_articles
GROUP BY article_id;

This is how easily we can calculate average delta changes in KPIs using just SQL.