Calculating growth in sqlite for beginner-CodePudding

I have a table which includes military spending as share of GDP for countries, year and a country_id which is connected to my country table.

I'm trying to calcualate the growth in military spending for each country with this code:

WITH start as (
SELECT
    country_id,
    year,
    military_spending
FROM military
WHERE year = 2013
),

end as (
SELECT
    country_id,
    year,
    military_spending
FROM military
WHERE year = 2020
)


SELECT
    s.country_id,
    c.name Country,
    (s.military_spending - e.military_spending) / s.military_spending *100 as growth
FROM start s
JOIN end e ON s.country_id = e.country_id
JOIN country c ON c.id = s.country_id
GROUP BY s.country_id
ORDER BY differ DESC

The problem is that for some countries I don't have yearly values, and if so, I want to use the prior value. My idea is to use an if-statement in my second CTE, looking something like this:

end as (
SELECT
    country_id,
    year,
    military_spending
FROM military
IF (year = 2020 AND military_spending > 0)
ELSE year = 2019
)

But this is not working and I'm pretty sure it is my IF/ELSE-statement not being correct. But unsure of how to do this. Any help is much appreciated.

CodePudding user response：

SQLite doesn't have an IF ELSE THEN expression/construct rather you can use the

and the result of the suggested query being :-

skipped years highlighted.