Scenario

You are an economist computing annual GDP growth rates from two separate data sources that share the same schema.

Task

Write a Snowflake SQL query that:

1. Combines {{ ref("gdp_domestic") }} and {{ ref("gdp_international") }} using UNION ALL
2. Uses LAG to look up the previous year's GDP for each country (partitioned by country, ordered by year)
3. Computes GDP_growth_rate as ROUND(((GDP - prev_GDP) / prev_GDP) * 100, 2), returning NULL when no previous year exists
4. Returns Country, Year, and GDP_growth_rate, sorted by Country ASC then Year ASC

Schema

gdp_domestic and gdp_international (same schema)

Example

gdp_domestic:

gdp_international:

Expected Output:

Note: The first year for each country has a NULL growth rate because there is no previous year to compare against. Japan shows negative growth in 2021 and 2022 because its GDP decreased year over year.

WITH combined AS (
    SELECT Country, Year, GDP FROM {{ ref("gdp_domestic") }}
    UNION ALL
    SELECT Country, Year, GDP FROM {{ ref("gdp_international") }}
),
with_lag AS (
    SELECT
        Country,
        Year,
        GDP,
        LAG(GDP) OVER (PARTITION BY Country ORDER BY Year) AS prev_GDP
    FROM combined
)
SELECT
    Country,
    Year,
    ROUND(((GDP - prev_GDP) / prev_GDP) * 100, 2) AS GDP_growth_rate
FROM with_lag
ORDER BY Country ASC, Year ASC

Explanation

Step 1: Combining the Two Tables with UNION ALL

WITH combined AS (
    SELECT Country, Year, GDP FROM {{ ref("gdp_domestic") }}
    UNION ALL
    SELECT Country, Year, GDP FROM {{ ref("gdp_international") }}
)

UNION ALL appends all rows from both tables into a single result set. Unlike UNION, it does not remove duplicates. Since the two tables contain different rows covering different countries or time periods, UNION ALL is the correct choice here.

Step 2: Using LAG to Access the Previous Year's GDP

LAG(GDP) OVER (PARTITION BY Country ORDER BY Year) AS prev_GDP

The LAG window function retrieves a value from the previous row within each partition. PARTITION BY Country ensures the window resets for each country, while ORDER BY Year sorts rows chronologically. For the earliest year of each country, LAG returns NULL because there is no preceding row.

Step 3: Computing the Growth Rate

ROUND(((GDP - prev_GDP) / prev_GDP) * 100, 2) AS GDP_growth_rate

This applies the standard percentage change formula. When prev_GDP is NULL (the first year for a country), the arithmetic expression automatically evaluates to NULL without needing explicit CASE WHEN handling. ROUND(..., 2) ensures the result has exactly two decimal places.

Step 4: Alternative with Explicit NULL Handling

If you prefer explicit control, you can use a CASE expression:

CASE
    WHEN prev_GDP IS NULL THEN NULL
    ELSE ROUND(((GDP - prev_GDP) / prev_GDP) * 100, 2)
END AS GDP_growth_rate

Both approaches produce identical results. The implicit version is more concise, while the explicit version makes the NULL behavior self-documenting.