postgres=> EXPLAIN SELECT iso_code, round(avg(((median_2022 - median_2002)/median_2002) * 100)) AS percent_change FROM (
SELECT postcode1 || ' ' || postcode2 AS postcode, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_2002 FROM uk_price_paid WHERE extract(year from date) = '2002' GROUP BY postcode
) med_2002 INNER JOIN (
SELECT postcode1 || ' ' || postcode2 AS postcode, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_2022 FROM uk_price_paid WHERE extract(year from date) = '2022' GROUP BY postcode
) med_2022 ON med_2002.postcode=med_2022.postcode INNER JOIN (
SELECT iso_code, postcode FROM uk_postcode_to_iso
) postcode_to_iso ON med_2022.postcode=postcode_to_iso.postcode GROUP BY iso_code ORDER BY percent_change DESC LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=38739865.98..38739866.01 rows=10 width=15)
-> Sort (cost=38739865.98..38739866.42 rows=175 width=15)
Sort Key: (round(avg((((med_2022.median_2022 - (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((uk_price_paid.price)::double precision)))) / (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((uk_price_paid.price)::double precision)))) * '100'::double precision)))) DESC
-> HashAggregate (cost=38739859.58..38739862.20 rows=175 width=15)
Group Key: uk_postcode_to_iso.iso_code
-> Hash Join (cost=1480445.76..1766956.13 rows=2957832276 width=23)
Hash Cond: (((((uk_price_paid.postcode1)::text || ' '::text) || (uk_price_paid.postcode2)::text)) = med_2022.postcode)
-> Merge Join (cost=859600.00..1116435.60 rows=1155988 width=55)
Merge Cond: (((((uk_price_paid.postcode1)::text || ' '::text) || (uk_price_paid.postcode2)::text)) = (uk_postcode_to_iso.postcode)::text)
-> GroupAggregate (cost=859599.58..1054124.96 rows=1155988 width=40)
Group Key: ((((uk_price_paid.postcode1)::text || ' '::text) || (uk_price_paid.postcode2)::text))
-> Gather Merge (cost=859599.58..1023133.58 rows=1434879 width=36)
Workers Planned: 1
-> Sort (cost=858599.57..860709.68 rows=844046 width=36)
Sort Key: ((((uk_price_paid.postcode1)::text || ' '::text) || (uk_price_paid.postcode2)::text))
-> Parallel Seq Scan on uk_price_paid (cost=0.00..748476.56 rows=844046 width=36)
Filter: (EXTRACT(year FROM date) = '2002'::numeric)
-> Index Scan using uk_postcode_to_iso_pkey on uk_postcode_to_iso (cost=0.43..33118.82 rows=1272836 width=15)
-> Hash (cost=610450.99..610450.99 rows=511741 width=40)
-> Subquery Scan on med_2022 (cost=590806.15..610450.99 rows=511741 width=40)
-> GroupAggregate (cost=590806.15..605333.58 rows=511741 width=40)
Group Key: ((((uk_price_paid_1.postcode1)::text || ' '::text) || (uk_price_paid_1.postcode2)::text))
-> Sort (cost=590806.15..592199.14 rows=557197 width=36)
Sort Key: ((((uk_price_paid_1.postcode1)::text || ' '::text) || (uk_price_paid_1.postcode2)::text))
-> Index Scan using uk_price_paid_extract_idx on uk_price_paid uk_price_paid_1 (cost=0.56..528702.04 rows=557197 width=36)
Index Cond: (EXTRACT(year FROM date) = '2022'::numeric)
(26 rows)
Regions for the UK (based on ISO 3166-2) that have experienced the largest price change in the last 20 years.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment