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;
current_month-previous_month
correctly?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;