Skip to content

Useful queries

Jump to bottom
Thomas Leese edited this page May 18, 2022 · 1 revision

Moves left in a non-final state

SELECT moves.id,
       moves.reference,
       moves.date,
       moves.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key
FROM moves
         JOIN locations lf on lf.id = moves.from_location_id
         JOIN locations lt on lt.id = moves.to_location_id
WHERE moves.date BETWEEN current_date - 600 AND current_date - 451
  AND moves.status IN ('requested', 'booked', 'in_transit')
  AND EXISTS(
        SELECT 1
        FROM generic_events
        WHERE eventable_id = moves.id
          AND eventable_type = 'Move'
          AND type IN ('GenericEvent::MoveComplete', 'GenericEvent::MoveCancel', 'GenericEvent::MoveReject')
    )
  AND moves.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

SELECT moves.date,
       COUNT(*) FILTER ( WHERE s.key = 'serco' )   AS serco,
       COUNT(*) FILTER ( WHERE s.key = 'geoamey' ) AS geo
FROM moves
         JOIN suppliers s on moves.supplier_id = s.id
WHERE moves.status IN ('requested', 'booked', 'in_transit')
  AND moves.date BETWEEN current_date - 120 AND current_date - 1
  AND EXISTS(SELECT 1
             FROM generic_events
             WHERE eventable_id = moves.id
               AND eventable_type = 'Move'
               AND type IN ('GenericEvent::MoveComplete', 'GenericEvent::MoveCancel', 'GenericEvent::MoveReject'))
GROUP BY moves.date
ORDER BY moves.date;

Moves without a final event

SELECT moves.id,
       moves.reference,
       moves.date,
       moves.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key
FROM moves
         JOIN locations lf on lf.id = moves.from_location_id
         JOIN locations lt on lt.id = moves.to_location_id
WHERE moves.date BETWEEN current_date - 620 AND current_date - 521
  AND NOT EXISTS(
        SELECT 1
        FROM generic_events
        WHERE eventable_id = moves.id
          AND eventable_type = 'Move'
          AND type IN ('GenericEvent::MoveComplete', 'GenericEvent::MoveCancel', 'GenericEvent::MoveReject')
    )
  AND moves.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Journeys without vehicle information

SELECT m.id, m.reference, m.date, journeys.state, journeys.id, lf.key, lt.key
FROM journeys
         JOIN moves m on journeys.move_id = m.id
         JOIN locations lf on lf.id = journeys.from_location_id
         JOIN locations lt on lt.id = journeys.to_location_id
    AND journeys.state IN ('completed', 'in_transit')
    AND vehicle ->> 'registration' IS NULL
    AND m.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Moves with a start event

SELECT m.id,
       m.reference,
       m.date,
       m.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM moves m
         JOIN locations lf on lf.id = m.from_location_id
         JOIN locations lt on lt.id = m.to_location_id
         JOIN profiles p on m.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE m.date BETWEEN current_date - 630 AND current_date - 601
  AND NOT EXISTS(SELECT 1
                 FROM generic_events
                 WHERE eventable_id = m.id
                   AND eventable_type = 'Move'
                   AND type = 'GenericEvent::MoveStart')
  AND EXISTS(SELECT 1
             FROM generic_events
             WHERE eventable_id = m.id
               AND eventable_type = 'Move'
               AND type = 'GenericEvent::MoveComplete')
  AND m.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Completed moves without a completed journey

SELECT journeys.id,
       journeys.state,
       journeys.move_id,
       journeys.client_timestamp,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key
FROM journeys
         JOIN locations lf on lf.id = journeys.from_location_id
         JOIN locations lt on lt.id = journeys.to_location_id
WHERE move_id IN (
    SELECT moves.id
    FROM moves
    WHERE moves.status = 'completed'
      AND moves.id IN (SELECT move_id FROM journeys WHERE journeys.state NOT IN ('completed', 'rejected', 'cancelled'))
      AND moves.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92'
)
  AND journeys.state NOT IN ('completed', 'rejected', 'cancelled');

Journeys that are still in progress

SELECT journeys.id,
       journeys.state,
       journeys.move_id,
       journeys.client_timestamp,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM journeys
         JOIN locations lf on lf.id = journeys.from_location_id
         JOIN locations lt on lt.id = journeys.to_location_id
         JOIN moves m on journeys.move_id = m.id
         JOIN profiles p on m.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE move_id IN (
    SELECT m.id
    FROM moves m
    WHERE supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92'
)
  AND journeys.state = 'in_progress';

## Moves with unrealistic durations

SELECT moves.id,
       moves.reference,
       moves.date,
       moves.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM moves
         JOIN locations lf on lf.id = moves.from_location_id
         JOIN locations lt on lt.id = moves.to_location_id
         JOIN profiles p on moves.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE moves.date BETWEEN current_date - 610 AND current_date - 561
  AND NOT EXISTS(SELECT 1 FROM journeys j WHERE j.move_id = moves.id AND j.vehicle ->> 'registration' = 'WALKTHROUGH')
  AND (
            AGE(
                    (SELECT occurred_at
                     FROM generic_events
                     WHERE eventable_id = moves.id
                       AND eventable_type = 'Move'
                       AND type = 'GenericEvent::MoveComplete'
                     LIMIT 1),
                    (SELECT occurred_at
                     FROM generic_events
                     WHERE eventable_id = moves.id
                       AND eventable_type = 'Move'
                       AND type = 'GenericEvent::MoveStart'
                     LIMIT 1)
                ) < '3 minutes'
        OR
            AGE(
                    (SELECT occurred_at
                     FROM generic_events
                     WHERE eventable_id = moves.id
                       AND eventable_type = 'Move'
                       AND type = 'GenericEvent::MoveComplete'
                     LIMIT 1),
                    (SELECT occurred_at
                     FROM generic_events
                     WHERE eventable_id = moves.id
                       AND eventable_type = 'Move'
                       AND type = 'GenericEvent::MoveStart'
                     LIMIT 1)
                ) > '12 hours'
    )
  AND moves.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Journeys with unrealistic durations

SELECT j.id,
       j.state,
       j.move_id,
       j.client_timestamp,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM journeys j
         JOIN locations lf on lf.id = j.from_location_id
         JOIN locations lt on lt.id = j.to_location_id
         JOIN moves on j.move_id = moves.id
         JOIN profiles p on moves.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE moves.date BETWEEN current_date - 610 AND current_date - 561
  AND j.vehicle ->> 'registration' != 'WALKTHROUGH'
  AND (
            AGE(
                    (SELECT occurred_at
                     FROM generic_events
                     WHERE eventable_id = j.id
                       AND eventable_type = 'Journey'
                       AND type = 'GenericEvent::JourneyComplete'
                     LIMIT 1),
                    (SELECT occurred_at
                     FROM generic_events
                     WHERE eventable_id = j.id
                       AND eventable_type = 'Journey'
                       AND type = 'GenericEvent::JourneyStart'
                     LIMIT 1)
                ) < '3 minutes'
        OR
            AGE(
                    (SELECT occurred_at
                     FROM generic_events
                     WHERE eventable_id = j.id
                       AND eventable_type = 'Journey'
                       AND type = 'GenericEvent::JourneyComplete'
                     LIMIT 1),
                    (SELECT occurred_at
                     FROM generic_events
                     WHERE eventable_id = j.id
                       AND eventable_type = 'Journey'
                       AND type = 'GenericEvent::JourneyStart'
                     LIMIT 1)
                ) > '12 hours'
    )
  AND j.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Journey events in the wrong order

SELECT journeys.id,
       journeys.state,
       journeys.move_id,
       journeys.client_timestamp,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM journeys
         JOIN locations lf on lf.id = journeys.from_location_id
         JOIN locations lt on lt.id = journeys.to_location_id
         JOIN moves on journeys.move_id = moves.id
         JOIN profiles p on moves.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE moves.date BETWEEN current_date - 600 AND current_date - 591
  AND (
        (SELECT occurred_at
         FROM generic_events
         WHERE eventable_id = journeys.id
           AND eventable_type = 'Journey'
           AND type = 'GenericEvent::JourneyStart'
         LIMIT 1) > (SELECT occurred_at
                     FROM generic_events
                     WHERE eventable_id = journeys.id
                       AND eventable_type = 'Journey'
                       AND type IN ('GenericEvent::JourneyComplete', 'GenericEvent::JourneyCancel',
                                    'GenericEvent::JourneyReject')
                     LIMIT 1)
    )
  AND moves.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Journeys missing a start event

SELECT journeys.id,
       journeys.state,
       journeys.move_id,
       journeys.client_timestamp,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key
FROM journeys
         JOIN locations lf on lf.id = journeys.from_location_id
         JOIN locations lt on lt.id = journeys.to_location_id
         JOIN moves m on journeys.move_id = m.id
WHERE move_id IN (
    SELECT moves.id
    FROM moves
    WHERE moves.created_at BETWEEN current_date - 150 AND current_date - 131
      AND moves.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92'
)
  AND EXISTS(SELECT 1
             FROM generic_events
             WHERE generic_events.type = 'GenericEvent::JourneyComplete'
               AND eventable_id = journeys.id
               AND eventable_type = 'Journey')
  AND NOT EXISTS(SELECT 1
                 FROM generic_events
                 WHERE generic_events.type = 'GenericEvent::JourneyStart'
                   AND eventable_id = journeys.id
                   AND eventable_type = 'Journey');

Moves without any journeys

SELECT moves.id,
       moves.reference,
       moves.date,
       moves.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM moves
         JOIN locations lf on lf.id = moves.from_location_id
         JOIN locations lt on lt.id = moves.to_location_id
         JOIN profiles p on moves.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE moves.status IN ('completed', 'in_transit')
  AND NOT EXISTS(SELECT 1 FROM journeys WHERE journeys.move_id = moves.id)
  AND moves.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Moves with no destination location

SELECT m.id,
       m.reference,
       m.date,
       m.status,
       lf.location_type,
       lf.key,
       p.id,
       p2.first_names,
       p2.last_name
FROM moves m
         JOIN locations lf on lf.id = m.from_location_id
         JOIN profiles p on m.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
    AND m.status IN ('in_transit', 'completed')
    AND m.to_location_id IS NULL
    AND m.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Moves with events in the wrong order

SELECT moves.id,
       moves.reference,
       moves.date,
       moves.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM moves
         JOIN locations lf on lf.id = moves.from_location_id
         JOIN locations lt on lt.id = moves.to_location_id
         JOIN profiles p on moves.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE moves.date BETWEEN current_date - 620 AND current_date - 571
  AND (
            (SELECT occurred_at
             FROM generic_events
             WHERE eventable_id = moves.id
               AND eventable_type = 'Move'
               AND type = 'GenericEvent::MoveAccept'
             LIMIT 1) > (SELECT occurred_at
                         FROM generic_events
                         WHERE eventable_id = moves.id
                           AND eventable_type = 'Move'
                           AND type IN
                               ('GenericEvent::MoveComplete', 'GenericEvent::MoveCancel', 'GenericEvent::MoveReject')
                         LIMIT 1)
        OR
            (SELECT occurred_at
             FROM generic_events
             WHERE eventable_id = moves.id
               AND eventable_type = 'Move'
               AND type = 'GenericEvent::MoveStart'
             LIMIT 1) > (SELECT occurred_at
                         FROM generic_events
                         WHERE eventable_id = moves.id
                           AND eventable_type = 'Move'
                           AND type IN
                               ('GenericEvent::MoveComplete', 'GenericEvent::MoveCancel', 'GenericEvent::MoveReject')
                         LIMIT 1)
    )
  AND moves.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Moves with duplicate journeys

SELECT moves.id,
       moves.reference,
       moves.date,
       moves.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM moves
         JOIN locations lf on lf.id = moves.from_location_id
         JOIN locations lt on lt.id = moves.to_location_id
         JOIN profiles p on moves.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE (from_location_id, to_location_id, moves.id) IN (
    SELECT from_location_id, to_location_id, move_id
    FROM journeys
    WHERE state NOT in ('rejected', 'cancelled')
    GROUP BY from_location_id, to_location_id, move_id
    HAVING COUNT(*) > 1
)
  AND moves.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';
SELECT j.id,
       j.state,
       j.move_id,
       j.client_timestamp,
       j.vehicle,
       j.created_at,
       m.reference,
       m.date,
       m.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM journeys j
         JOIN moves m ON m.id = j.move_id
         JOIN locations lf on lf.id = m.from_location_id
         JOIN locations lt on lt.id = m.to_location_id
         JOIN profiles p on m.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE j.move_id IN (
    SELECT m.id
    FROM moves m
    WHERE (from_location_id, to_location_id, m.id) IN (
        SELECT from_location_id, to_location_id, move_id
        FROM journeys
        WHERE state NOT in ('rejected', 'cancelled')
        GROUP BY from_location_id, to_location_id, move_id
        HAVING COUNT(*) > 1
    )
      AND m.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92'
);

Moves with no events

SELECT m.id,
       m.reference,
       m.date,
       m.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p.id,
       p2.first_names,
       p2.last_name
FROM moves m
         JOIN locations lf on lf.id = m.from_location_id
         JOIN locations lt on lt.id = m.to_location_id
         JOIN profiles p on m.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE m.date BETWEEN current_date - 620 AND current_date - 541
  AND NOT EXISTS(SELECT 1 FROM generic_events WHERE eventable_id = m.id AND eventable_type = 'Move')
  AND m.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Moves which have been cancelled and then started

SELECT m.id,
       m.reference,
       m.date,
       m.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key
FROM moves m
         JOIN locations lf on lf.id = m.from_location_id
         JOIN locations lt on lt.id = m.to_location_id
WHERE m.date BETWEEN current_date - 620 AND current_date - 551
  AND (
          SELECT occurred_at
          FROM generic_events
          WHERE eventable_id = m.id
            AND eventable_type = 'Move'
            AND type IN ('GenericEvent::MoveCancel', 'GenericEvent::MoveReject')
          LIMIT 1)
    < (SELECT occurred_at
       FROM generic_events
       WHERE eventable_id = m.id
         AND eventable_type = 'Move'
         AND type = 'GenericEvent::MoveStart'
       LIMIT 1)
  AND m.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Moves missing a profile ID

SELECT m.id,
       m.reference,
       m.date,
       m.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key
FROM moves m
         JOIN locations lf on lf.id = m.from_location_id
         JOIN locations lt on lt.id = m.to_location_id
WHERE m.profile_id IS NULL
  AND EXISTS(SELECT 1
             FROM generic_events
             WHERE eventable_id = m.id
               AND eventable_type = 'Move'
               AND type IN ('GenericEvent::MoveStart', 'GenericEvent::MoveComplete'))
  AND m.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Journeys with the same source and destination location

SELECT j.id,
       j.state,
       j.move_id,
       j.client_timestamp,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM journeys j
         JOIN locations lf on lf.id = j.from_location_id
         JOIN locations lt on lt.id = j.to_location_id
         JOIN moves m on j.move_id = m.id
         JOIN profiles p on m.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE j.from_location_id = j.to_location_id
  AND NOT EXISTS(SELECT id
                 FROM generic_events
                 WHERE eventable_id = m.id
                   AND eventable_type = 'Move'
                   AND type = 'GenericEvent::MoveRedirect')
  AND j.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

Moves with the same source and destination location

SELECT m.id,
       m.reference,
       m.date,
       m.status,
       lf.location_type,
       lf.key,
       lt.location_type,
       lt.key,
       p2.first_names,
       p2.last_name
FROM moves m
         JOIN locations lf on lf.id = m.from_location_id
         JOIN locations lt on lt.id = m.to_location_id
         JOIN profiles p on m.profile_id = p.id
         JOIN people p2 on p.person_id = p2.id
WHERE m.from_location_id = m.to_location_id
  AND NOT EXISTS(SELECT id
                 FROM generic_events
                 WHERE eventable_id = m.id
                   AND eventable_type = 'Move'
                   AND type = 'GenericEvent::MoveRedirect')
  AND m.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92';

All move and journey events

SELECT g.eventable_id, g.id, g.type, g.occurred_at, g.recorded_at, g.created_by
FROM generic_events g
WHERE eventable_type = 'Move'
  AND eventable_id IN (
    SELECT id
    FROM moves
    WHERE supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92'
      AND date BETWEEN '2021-11-01' AND '2021-12-31'
);
SELECT g.eventable_id, g.id, g.type, g.occurred_at, g.recorded_at, g.created_by
FROM generic_events g
WHERE eventable_type = 'Journey'
  AND eventable_id IN (
    SELECT id
    FROM journeys
    WHERE supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92'
      AND client_timestamp BETWEEN '2021-11-01' AND '2021-12-31'
);

Events useful for suppliers to investigate

SELECT id, eventable_id, type, occurred_at, recorded_at, created_at, created_by
FROM generic_events g
WHERE supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92'
  AND eventable_type IN ('Move', 'Journey')
  AND type IN (
               'GenericEvent::MoveStart',
               'GenericEvent::MoveAccept',
               'GenericEvent::MoveComplete',
               'GenericEvent::MoveCancel',
               'GenericEvent::MoveReject',
               'GenericEvent::JourneyStart',
               'GenericEvent::JourneyCancel',
               'GenericEvent::JourneyReject',
               'GenericEvent::JourneyComplete'
    )
  AND recorded_at BETWEEN current_date - 150 AND current_date - 60;

Duplicate moves

SELECT COUNT(*), m.date, m.profile_id, m.to_location_id, m.from_location_id
FROM moves m
WHERE m.status NOT IN ('proposed', 'cancelled')
  AND m.supplier_id = '3ef88a47-6f1f-5b9b-b2fc-c0fe42cb0c92'
  AND m.profile_id IS NOT NULL
  AND m.date > '2020-12-02'
GROUP BY m.date, m.profile_id, m.to_location_id, m.from_location_id
HAVING COUNT(*) >= 2;
Clone this wiki locally