postgresqlgroup-bycommon-table-expressiondatepart

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


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;

Solution

  • 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;