Creating a column for 'current month - previous month' in PostgreSQL

Question

I am trying to create a column to calculate the difference between the current months value and the value the previous month, i.e. current_month-previous_month.

To create the values for the current month and the previous month I have

WITH cte AS (
    SELECT group1, group2, my_date,
    (COUNT(CASE WHEN some_value > 0 
                    THEN my_id 
                ELSE null
           END)/CAST(COUNT(my_id) AS double precision))AS current_month
    FROM my_table
    GROUP BY group2, group1, my_date
    ORDER BY group1 ASC, group2 ASC
)

SELECT group1, group2, 
current_month, LAG(current_month,1) OVER (ORDER BY date_part('year', my_date), 
date_part('month', my_date)) AS previous_month
FROM cte
GROUP BY group2, group1, current_month, my_date
ORDER BY group1 ASC, group2 ASC;

Which calulates two columns, current_month and previous_month.

When I try to add in the third column current_month-previous_month I seem to get the wrong answer using the code below.

WITH cte AS (
    SELECT group1, group2, my_date,
    (COUNT(CASE WHEN some_value > 0 
                    THEN my_id 
                ELSE null
           END)/CAST(COUNT(my_id) AS double precision))AS current_month
    FROM my_table
    GROUP BY group2, group1, my_date
    ORDER BY group1 ASC, group2 ASC
), 

cte2 AS (
    SELECT group1, group2, current_month, 
    LAG(current_month,1) OVER (ORDER BY date_part('year', my_date), 
    date_part('month', my_date)) AS previous_month
    FROM cte
    GROUP BY group2, group1, current_month, my_date
    ORDER BY group1 ASC, group2 ASC
)
    
SELECT group1, group2, 
SUM(CASE WHEN previous_month > 0
            THEN (current_month-previous_month)
         ELSE null 
    END) AS change
FROM cte2
GROUP BY group2, group1
ORDER BY group1 ASC, group2 ASC;

• How should I calculate current_month-previous_month correctly?
• Are the multiple GROUP BY and ORDER BY clauses causing the issue?
• Is there a way to remove the repetition and neaten this code?

Answer 1

UPDATE: Changing the ORDER BY clause in the LAG function to remove my_date fixed the issue and I was able to reduce the code to this.

SELECT group1, group2, 
((COUNT(CASE
      WHEN some_value > 0 
        THEN my_id 
      ELSE null
      END)/CAST(COUNT(my_id) AS double precision))*100) -
(LAG((COUNT(CASE WHEN some_value > 0 
                    THEN my_id 
                ELSE null
           END)/CAST(COUNT(my_id) AS double precision))*100,1) OVER (PARTITION BY group1 ORDER BY group2)) AS my_diff
FROM my_table
GROUP BY group2, group1
ORDER BY group1 ASC, group2 ASC;