WITH overlapping AS (SELECT
  a.sk_region AS sk_region_1,
  b.sk_region AS sk_region_2,
  round(ST_Area(a.the_geom)) AS region_1_area,
  round(ST_Area(b.the_geom)) AS region_2_area,
  round(ST_Perimeter(a.the_geom)) AS region_1_perim,
  round(ST_Perimeter(b.the_geom)) AS region_2_perim,
  ST_CollectionExtract(
    ST_Intersection(a.the_geom, b.the_geom), 3
  ) AS the_geom,
  ST_CollectionExtract(
    ST_Intersection(a.the_geom, b.the_geom), 3
  ) AS the_geom,
  round(ST_Area(
    ST_CollectionExtract(
      ST_Intersection(a.the_geom, b.the_geom),
  3))) AS overlap_area,
  round(ST_Perimeter(
    ST_CollectionExtract(
      ST_Intersection(a.the_geom, b.the_geom),
  3))) AS overlap_perimeter
FROM qa_subregions a
JOIN qa_subregions b ON ST_Intersects(a.the_geom, b.the_geom)
WHERE a.sk_region != b.sk_region
AND ST_Dimension(
  ST_CollectionExtract(
    ST_Intersection(a.the_geom, b.the_geom),
  3)) = 2
ORDER BY 4 DESC),
-- to get only the first instance of A-overlap-B, not B-overlap-A
distinct_overlapping AS (
SELECT DISTINCT
  CASE WHEN sk_region_1 > sk_region_2 THEN sk_region_2 ELSE sk_region_1 END AS sk_region_1,
  CASE WHEN sk_region_1 < sk_region_2 THEN sk_region_2 ELSE sk_region_1 END AS sk_region_2
FROM overlapping
)
SELECT
  o.*,
  o.overlap_area / o.overlap_perimeter as overlap_ratio,
  ST_MinimumClearance(o.the_geom) as min_clearance,
  ROW_NUMBER() OVER(ORDER BY o.sk_region_1) AS cartodb_id
FROM distinct_overlapping d
JOIN overlapping o ON d.sk_region_1 = o.sk_region_1 AND d.sk_region_2 = o.sk_region_2
WHERE o.overlap_perimeter > 0
ORDER BY overlap_ratio DESC