how to find the difference of median price of a municipality from the current month and the next one?

How to get the absolute difference between the median house price for tat month in that municipality and the median house price for the next month in that municipality? I have a table with house data. it contains the price and municipality for each house and date it got sold. I want to get the difference of the median price for a municipality of a month and median price of the house for municipality of the next month. Already created the median function. This is my query at the moment:

select gemeentenaam as municipality, 
       EXTRACT(MONTH FROM datum_ondertekening) as month, median(koopprijs) as median_price_previous_month, 
       LEAD(median(koopprijs)) OVER(PARTITION BY gemeentenaam) AS median_price_next_month, 
       ABS((LEAD(median(koopprijs)) OVER(PARTITION BY gemeentenaam))-(median(koopprijs))) AS difference 
from funda 
  inner join postcode on funda.postcode = postcode.postcode 
  inner join gemeente on postcode.gemeente = gemeente.gemcode 
WHERE EXTRACT(MONTH FROM datum_ondertekening) <> 12 
group by (gemeentenaam, EXTRACT(MONTH FROM datum_ondertekening), koopprijs);

​x
 
select gemeentenaam as municipality,        EXTRACT(MONTH FROM datum_ondertekening) as month, median(koopprijs) as median_price_previous_month,        LEAD(median(koopprijs)) OVER(PARTITION BY gemeentenaam) AS median_price_next_month,        ABS((LEAD(median(koopprijs)) OVER(PARTITION BY gemeentenaam))-(median(koopprijs))) AS difference from funda   inner join postcode on funda.postcode = postcode.postcode   inner join gemeente on postcode.gemeente = gemeente.gemcode WHERE EXTRACT(MONTH FROM datum_ondertekening) <> 12 group by (gemeentenaam, EXTRACT(MONTH FROM datum_ondertekening), koopprijs);​

EDIT:

sample data:

globalid | postcode | koopprijs | datum_ondertekening
----------+----------+-----------+---------------------
 4606495  | 4701CV   |    595000 | 2019-03-01
 4607492  | 5252AV   |    329000 | 2019-01-11
 4562176  | 3454VC   |    292500 | 2019-08-26
 4562213  | 3454VC   |    337500 | 2019-01-25
 4557050  | 3774BM   |    439000 | 2019-03-08


 postcode | gemeente
----------+----------
 1231SW   |     1696
 1723MX   |      416
 2111XN   |      377
 3648HN   |      736
 4053JP   |     1740


 gemcode | gemeentenaam
---------+--------------
       3 | Appingedam
      10 | Delfzijl
      14 | Groningen
      24 | Loppersum
      34 | Almere

 
globalid | postcode | koopprijs | datum_ondertekening----------+----------+-----------+--------------------- 4606495  | 4701CV   |    595000 | 2019-03-01 4607492  | 5252AV   |    329000 | 2019-01-11 4562176  | 3454VC   |    292500 | 2019-08-26 4562213  | 3454VC   |    337500 | 2019-01-25 4557050  | 3774BM   |    439000 | 2019-03-08​​ postcode | gemeente----------+---------- 1231SW   |     1696 1723MX   |      416 2111XN   |      377 3648HN   |      736 4053JP   |     1740​​ gemcode | gemeentenaam---------+--------------       3 | Appingedam      10 | Delfzijl      14 | Groningen      24 | Loppersum      34 | Almere​

expected result:

 municipality  | month | median_price_previous_month | median_price_next_month |    difference
---------------+-------+-----------------------------+-------------------------+-------------------
 's-Gravenhage |     1 |         100000.000000000000 |     105000.000000000000 | 5000.000000000000
 's-Gravenhage |     2 |         105000.000000000000 |     110000.000000000000 | 5000.000000000000
 's-Gravenhage |     3 |         110000.000000000000 |     115000.000000000000 | 5000.000000000000
 's-Gravenhage |     4 |         115000.000000000000 |     119000.000000000000 | 4000.000000000000
 's-Gravenhage |     5 |         119000.000000000000 |     119500.000000000000 |  500.000000000000

 
 municipality  | month | median_price_previous_month | median_price_next_month |    difference---------------+-------+-----------------------------+-------------------------+------------------- 's-Gravenhage |     1 |         100000.000000000000 |     105000.000000000000 | 5000.000000000000 's-Gravenhage |     2 |         105000.000000000000 |     110000.000000000000 | 5000.000000000000 's-Gravenhage |     3 |         110000.000000000000 |     115000.000000000000 | 5000.000000000000 's-Gravenhage |     4 |         115000.000000000000 |     119000.000000000000 | 4000.000000000000 's-Gravenhage |     5 |         119000.000000000000 |     119500.000000000000 |  500.000000000000​

Answer

I think I found it myself.

select gemeentenaam as municipality, EXTRACT(MONTH FROM datum_ondertekening) as month, median(koopprijs) as 
                media_price_next_month, LAG(median(koopprijs)) OVER(PARTITION BY gemeentenaam) AS median_price_previous_month, 
                ABS(median(koopprijs)-(LAG(median(koopprijs)) OVER(PARTITION BY gemeentenaam))) AS absolute_difference 
        from funda 
            inner join postcode on funda.postcode = postcode.postcode 
            inner join gemeente on postcode.gemeente = gemeente.gemcode WHERE EXTRACT(MONTH FROM datum_ondertekening) <> 1
            group by (gemeentenaam, EXTRACT(MONTH FROM datum_ondertekening));```

 
select gemeentenaam as municipality, EXTRACT(MONTH FROM datum_ondertekening) as month, median(koopprijs) as                 media_price_next_month, LAG(median(koopprijs)) OVER(PARTITION BY gemeentenaam) AS median_price_previous_month,                 ABS(median(koopprijs)-(LAG(median(koopprijs)) OVER(PARTITION BY gemeentenaam))) AS absolute_difference         from funda             inner join postcode on funda.postcode = postcode.postcode             inner join gemeente on postcode.gemeente = gemeente.gemcode WHERE EXTRACT(MONTH FROM datum_ondertekening) <> 1            group by (gemeentenaam, EXTRACT(MONTH FROM datum_ondertekening));```​

Advertisement

Answer