PostgreSQL Percent Change using Row Number

I’m trying to find the percent change using row number with PostgreSQL but I’m running into an error where my “percent_change” column shows 0.

Here is what I have as my code.

WITH CTE AS (
SELECT date, sales, ROW_NUMBER() OVER (ORDER by date) AS rn
FROM sales_2019)

SELECT c1.date, c1.sales,
CAST(COALESCE (((c1.sales - c2.sales) * 1.0 / c2.sales) * 100, 0) AS INT) AS percent_change
FROM CTE AS c1
LEFT JOIN CTE AS c2 
ON c1.date = c2.date AND c1.rn = c2.rn + 1

Here is my SQL table in case it’s needed. Thank you in advance, I greatly appreciate it.

Answer

You can use LAG() for your requirement:

select 
date, 
sales, 
round(coalesce((((sales-(lag(sales) over (order by date)))*1.0)/(lag(sales) over (order by date)))*100,0),2)
                                                            
from sales_2019

or you can try with WITH clause

with cte as ( select
  date,
  sales,
  coalesce(lag(sales) over (order by date),0) as previous_month
                            from sales_2019
  )
select 
date,
sales,
round( coalesce( (sales-previous_month)*1.0/nullif(previous_month,0),0 )*100,2)
from cte
           

DEMO

EDIT as per requirement in comment

with cte as ( select
  date_,
  sales,
  ROW_NUMBER() OVER (ORDER by date_) AS rn1,
  ROW_NUMBER() OVER (ORDER by date_)-1 AS rn2
                            from sales_2019
  )
  
  select t1.date_,
  t1.sales,
  round( coalesce( (t1.sales-t2.sales)*1.0/nullif(t2.sales,0),0 )*100,2)
  from cte t1 left join cte t2 on t1.rn2=t2.rn1

DEMO