Learn Comparing Financial Periods | Period-over-Period Comparisons

Period-over-period analysis is a fundamental technique in finance that allows you to compare financial metrics from one time period to another. This approach helps you identify trends, spot anomalies, and make informed decisions by examining how key figures such as revenue, expenses, or profits change over time. For example, you might want to compare this month's revenue to last month's, or analyze how quarterly expenses evolve year over year. These comparisons form the basis for evaluating financial performance and understanding the direction of a business.


              123456
            
SELECT
    DATE_TRUNC('month', revenue_date) AS month,
    SUM(amount) AS total_revenue
FROM revenue
GROUP BY DATE_TRUNC('month', revenue_date)
ORDER BY month;

In this query, you group the revenue data by month using the DATE_TRUNC('month', revenue_date) function, which extracts the month and year from each revenue_date. By summing the amount for each group, you calculate the total revenue for every month. This grouping and aggregation are essential for comparing financial results across periods, as they transform daily or transactional data into meaningful monthly summaries.


              1234567891011
            
SELECT month, total_revenue,
    LAG(total_revenue) OVER (ORDER BY month) AS previous_month_revenue,
    total_revenue - LAG(total_revenue) OVER (ORDER BY month) AS revenue_change
FROM (
    SELECT
        DATE_TRUNC('month', revenue_date) AS month,
        SUM(amount) AS total_revenue
    FROM revenue
    GROUP BY DATE_TRUNC('month', revenue_date)
) monthly_revenue
ORDER BY month;

The LAG() window function is used here to fetch the total revenue from the previous month for each row. By ordering the results by month, LAG(total_revenue) OVER (ORDER BY month) shifts the total revenue values down by one row, making it possible to compare the current month's revenue to the previous month's. Subtracting the lagged value from the current value gives you the month-over-month revenue change, a key metric in period-over-period analysis. This approach allows you to track trends, detect sudden increases or decreases, and better understand the financial health of an organization over time.

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Swipe to show menu


              123456
            
SELECT
    DATE_TRUNC('month', revenue_date) AS month,
    SUM(amount) AS total_revenue
FROM revenue
GROUP BY DATE_TRUNC('month', revenue_date)
ORDER BY month;


              1234567891011
            
SELECT month, total_revenue,
    LAG(total_revenue) OVER (ORDER BY month) AS previous_month_revenue,
    total_revenue - LAG(total_revenue) OVER (ORDER BY month) AS revenue_change
FROM (
    SELECT
        DATE_TRUNC('month', revenue_date) AS month,
        SUM(amount) AS total_revenue
    FROM revenue
    GROUP BY DATE_TRUNC('month', revenue_date)
) monthly_revenue
ORDER BY month;

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 1

Comparing Financial Periods

1. What is the purpose of using the LAG() function in period comparisons?

2. Which SQL function is used to calculate the difference between current and previous periods?

3. Why are period-over-period comparisons important in financial analysis?

Comparing Financial Periods

1. What is the purpose of using the LAG() function in period comparisons?

2. Which SQL function is used to calculate the difference between current and previous periods?

3. Why are period-over-period comparisons important in financial analysis?