Very nice and detailed post!

Small comment on `ST_CENTROID(ST_MAKELINE(ARRAY_AGG(location)))` - it was not clear how many points are in a group?

If there are more than two, the order of points in array returned by `ARRAY_AGG` is random, so the line is somewhat random too, as well as the result of `ST_CENTROID`.

If there are just two points in a group, this does not matter, but `ST_CENTROID_AGG(location)` is simpler expression giving same result.