Regions for the UK (based on ISO 3166-2) that have experienced the largest price change in the last 20 years.

query4.md

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)