Objective

Write an SQL query that calculates the yearly revenue, the previous year's revenue, and the percentage growth in revenue year-over-year for a given set of financial transactions. The results should be rounded to two decimal places.

Additional information

• The financials table contains the columns transaction_date (DATE) and amount (NUMERIC).
• The growth_percentage is calculated as ((current year's revenue - previous year's revenue) / previous year's revenue) * 100.
• If there is no previous year's revenue for a given year, the previous_revenue and growth_percentage should be NULL.
• Order the results by year in ascending order.

WITH YearlyRevenue AS (
    SELECT
        EXTRACT(YEAR FROM transaction_date) AS year,
        SUM(amount) AS revenue
    FROM financials
    GROUP BY EXTRACT(YEAR FROM transaction_date)
)
SELECT
    current.year,
    current.revenue AS current_revenue,
    prev.revenue AS previous_revenue,
    ROUND(((current.revenue - prev.revenue) / prev.revenue * 100)::numeric, 2) AS growth_percentage
FROM YearlyRevenue current
LEFT JOIN YearlyRevenue prev ON current.year = prev.year + 1
ORDER BY current.year;

Explanation

Step 1: Aggregate revenue by year (CTE)

EXTRACT(YEAR FROM transaction_date) pulls the year from each transaction date. SUM(amount) totals revenue per year. The CTE makes this available for self-joining.

WITH YearlyRevenue AS (
    SELECT EXTRACT(YEAR FROM transaction_date) AS year, SUM(amount) AS revenue
    FROM financials
    GROUP BY EXTRACT(YEAR FROM transaction_date)
)

Step 2: Self-join to get previous year's revenue

LEFT JOIN YearlyRevenue prev ON current.year = prev.year + 1 links each year to the immediately preceding year. A LEFT JOIN preserves years that have no prior-year data (e.g., the earliest year), producing NULL for previous_revenue.

FROM YearlyRevenue current
LEFT JOIN YearlyRevenue prev ON current.year = prev.year + 1

Step 3: Calculate growth percentage

(current.revenue - prev.revenue) / prev.revenue * 100 computes the year-over-year growth rate. ROUND(...::numeric, 2) formats it to two decimal places. When prev.revenue is NULL, the expression also returns NULL.

ROUND(((current.revenue - prev.revenue) / prev.revenue * 100)::numeric, 2) AS growth_percentage