 -- Find out-of-order stops for the most recent feed added.
 -- Change the first subquery to SELECT different feeds.
 \C 'Out-of-order stop geometries'
-WITH
-  f AS (SELECT MAX(feed_index) AS feed_index FROM :schema.feed_info),
-  d AS (
+WITH f AS (SELECT MAX(feed_index) AS feed_index FROM :schema.feed_info)
+SELECT feed_index,
+  trip_id,
+  route_id,
+  stop_id,
+  stop_sequence,
+  lag::numeric(9, 3),
+  dist::numeric(9, 3), 
+  coalesce(lead, length)::numeric(9, 3) as lead,
+  length::numeric(9, 3)
+FROM (
     SELECT
       feed_index,
       trip_id,

  where b.feed_index is null or b.agency_id is null
-- :schema.calendar_dates
UNION
SELECT DISTINCT a.feed_index, 'calendar_dates', 'service_id', a.service_id
  FROM :schema.calendar_dates a
    LEFT JOIN :schema.calendar b USING (feed_index, service_id)
  WHERE b.feed_index is null or b.service_id is null
UNION
SELECT DISTINCT a.feed_index, 'fare_attributes', 'agency_id', a.agency_id
  FROM :schema.fare_attributes a
    LEFT JOIN :schema.agency b USING (feed_index, agency_id)
  WHERE b.feed_index is null or b.agency_id is null
-- :schema.fare_rules
UNION
SELECT DISTINCT a.feed_index, 'fare_rules', 'service_id', a.service_id
  FROM :schema.fare_rules a
    LEFT JOIN :schema.calendar b USING (feed_index, service_id)
  WHERE b.feed_index is null or b.service_id is null
UNION
SELECT DISTINCT a.feed_index, 'fare_rules', 'fare_id', a.fare_id
  FROM :schema.fare_rules a
    LEFT JOIN :schema.fare_attributes b USING (feed_index, fare_id)
  WHERE b.feed_index is null or b.fare_id is null
UNION
SELECT DISTINCT a.feed_index, 'fare_rules', 'route_id', a.route_id
  FROM :schema.fare_rules a
    LEFT JOIN :schema.routes b USING (feed_index, route_id)
  WHERE b.feed_index is null OR b.route_id is null
-- :schema.trips
UNION
SELECT DISTINCT a.feed_index, 'trips', 'route_id', a.route_id
  FROM :schema.trips a
    LEFT JOIN :schema.routes b USING (feed_index, route_id)
  WHERE b.feed_index is null OR b.route_id is null
UNION
SELECT DISTINCT a.feed_index, 'trips', 'service_id', a.service_id
  FROM :schema.trips a
    LEFT JOIN :schema.calendar b USING (feed_index, service_id)
  WHERE b.feed_index is null OR b.service_id is null
-- :schema.stop_times
UNION
SELECT DISTINCT a.feed_index, 'stop_times', 'trip_id', a.trip_id
  FROM :schema.stop_times a
    LEFT JOIN :schema.trips b USING (feed_index, trip_id)
  WHERE b.feed_index is null OR b.trip_id is null
UNION
SELECT DISTINCT a.feed_index, 'stop_times', 'stop_id', a.stop_id
  FROM :schema.stop_times a
    LEFT JOIN :schema.stops b USING (feed_index, stop_id)
  WHERE b.feed_index is null OR b.stop_id is null
-- :schema.frequencies
UNION
SELECT DISTINCT a.feed_index, 'frequencies', 'trip_id', a.trip_id
  FROM :schema.frequencies a
    LEFT JOIN :schema.trips b USING (feed_index, trip_id)
  WHERE b.feed_index is null OR b.trip_id is null
-- :schema.transfers
UNION
SELECT DISTINCT a.feed_index, 'transfers', 'from_stop_id', a.from_stop_id
  FROM :schema.transfers a
    LEFT JOIN :schema.stops b ON (a.feed_index, a.from_stop_id) = (b.feed_index, b.stop_id)
  WHERE b.feed_index is null OR stop_id is null
UNION
SELECT DISTINCT a.feed_index, 'transfers', 'to_stop_id', coalesce(a.to_stop_id, 'NULL')
  FROM :schema.transfers a
    LEFT JOIN :schema.stops b ON (a.feed_index, a.to_stop_id) = (b.feed_index, b.stop_id)
  WHERE b.feed_index is null OR stop_id is null
UNION
SELECT DISTINCT a.feed_index, 'transfers', 'from_route_id', coalesce(a.from_route_id, 'NULL')
  FROM :schema.transfers a
    LEFT JOIN :schema.routes b ON (a.feed_index, a.from_route_id) = (b.feed_index, b.route_id)
  WHERE (b.feed_index is null OR route_id is null)
UNION
SELECT DISTINCT a.feed_index, 'transfers', 'to_route_id', coalesce(a.to_route_id, 'NULL')
  FROM :schema.transfers a
    LEFT JOIN :schema.routes b ON (a.feed_index, a.to_route_id) = (b.feed_index, b.route_id)
  WHERE (b.feed_index is null OR route_id is null)
UNION
SELECT DISTINCT a.feed_index, 'transfers', 'service_id', coalesce(a.service_id, 'NULL')
  FROM :schema.transfers a
    LEFT JOIN :schema.calendar b USING (feed_index, service_id)
  where (b.feed_index is null OR b.service_id is null);

    FROM :schema.stop_times
      INNER JOIN f USING (feed_index)
    WINDOW trip AS (PARTITION BY feed_index, trip_id ORDER BY stop_sequence)
  ) AS d
  LEFT JOIN :schema.trips trip USING (feed_index, trip_id)
  LEFT JOIN :schema.shape_geoms shape USING (feed_index, shape_id)
WHERE COALESCE(lead, length) > lag
  AND (dist > COALESCE(lead, length) OR dist < lag);
