WITH t AS (
SELECT min(a.timestamp_intersecting_date) d1, min(b.timestamp_intersecting_date) d2,
		a."DID",
		max(a.timestamp_intersecting_max) t1, min(b.timestamp_intersecting_min) t2,
		max(a.id) as a_id, min(b.id) AS b_id,
		RANK () OVER (
		  PARTITION BY a.timestamp_intersecting_date
		  ORDER BY a.timestamp_intersecting_max DESC
	   ) timestamp_d1_rank ,
		RANK () OVER (
		  PARTITION BY b.timestamp_intersecting_date
		  ORDER BY b.timestamp_intersecting_max ASC
	   ) timestamp_d2_rank
FROM
    location_signals_centroids_calculated_daily_excluding_orphans a
INNER JOIN location_signals_centroids_calculated_daily_excluding_orphans b ON (a."DID" = b."DID")
WHERE (b.timestamp_intersecting_date = a.timestamp_intersecting_date + INTERVAL '1 DAY')
AND a."DID" = b."DID"
GROUP BY a."DID"
)
SELECT *, c.coords_centroid AS coords_centroid_min ,d.coords_centroid AS coords_centroid_max
FROM t
INNER JOIN location_signals_centroids_calculated_daily_excluding_orphans c ON (c.id = t.a_id)
INNER JOIN location_signals_centroids_calculated_daily_excluding_orphans d ON (d.id = t.b_id)
WHERE timestamp_d1_rank = 1
AND timestamp_d2_rank = 1
ORDER BY "DID"