Query history of vehicle occupation

occupancy_status.sql

WITH -- Get information for shift in given schedules
 shift_info AS (
    SELECT
            DISTINCT s.id AS shift_id
            ,s.scheduled_at
            ,s.vehicle_license_plate AS license_plate
            ,s.organization_id
            ,s.operating_period_id
            ,s.region_id
        FROM
            drt.shift s
        WHERE
            s.scheduled_at BETWEEN '2020-05-20' AND '2020-05-22'
)
,timeline AS (
    SELECT
            'break' AS occupancy_status
            ,start_datetime AS "start"
            ,end_datetime AS "end"
            ,s. *
        FROM
            drt.break AS b JOIN shift_info AS s
                ON s.shift_id = b.shift_id -- look from start of shift
    UNION
    ALL SELECT
            (
                CASE
                    WHEN occupancy_status = 'on_route'
                    THEN 'empty'
                    ELSE occupancy_status
                END
            ) occupancy_status
            ,state_start_datetime AS "start"
            ,state_end_datetime AS "end"
            ,s. *
        FROM
            drt.shift_occupancy_status AS ss JOIN shift_info AS s
                ON s.shift_id = ss.shift_id
)
,timeline_unravelled AS (
    SELECT
            *
            ,LAG (
                occupancy_status
                ,1
            ) OVER (
                PARTITION BY shift_id
            ORDER BY
                "end"
            ) previous_occupancy_status
            ,LAG (
                "end"
                ,1
            ) OVER (
                PARTITION BY shift_id
            ORDER BY
                "end"
            ) previous_end
        FROM
            timeline
        ORDER BY
            "end"
) SELECT
        timeline_unravelled.organization_id
        ,timeline_unravelled.region_id
        ,timeline_unravelled.operating_period_id
        ,timeline_unravelled.license_plate
        ,timeline_unravelled.scheduled_at
        ,timeline_unravelled.shift_id
        ,CASE
            WHEN previous_occupancy_status = 'break'
            AND occupancy_status = 'empty'
            THEN previous_end -- break -> empty transition
 WHEN previous_occupancy_status = 'occupied'
            AND occupancy_status = 'empty'
            THEN previous_end -- occupied -> empty transition
 WHEN previous_occupancy_status = 'empty'
            AND occupancy_status = 'empty'
            THEN previous_end -- empty -> empty transition
            ELSE "start"
        END AS "start"
        ,timeline_unravelled. "end"
        ,occupancy_status
    FROM
        timeline_unravelled

occupancy_status_fix_gaps_and_islands.sql

WITH priority_utilization AS (
    SELECT
            drt.utilization.organization_id AS organization_id
            ,drt.utilization.region_id AS region_id
            ,drt.utilization.operating_period_id AS operating_period_id
            ,drt.utilization.shift_id AS shift_id
            ,drt.utilization.bookings_in_shuttle AS bookings_in_shuttle
            ,drt.utilization.task_type AS task_type
            ,drt.utilization.task_id AS task_id
            ,drt.utilization.state_start_datetime AS start_date
            ,CASE
                WHEN (
                    drt.utilization.task_type = 'shift_start'
                )
                THEN 1 WHEN (
                    drt.utilization.task_type = 'op_period_start'
                )
                THEN 2 WHEN (
                    drt.utilization.task_type = 'dropoff'
                )
                THEN 3 WHEN (
                    drt.utilization.task_type = 'break_start'
                )
                THEN 4 WHEN (
                    drt.utilization.task_type = 'break_end'
                )
                THEN 5 WHEN (
                    drt.utilization.task_type = 'pickup'
                )
                THEN 6 WHEN (
                    drt.utilization.task_type = 'op_period_end'
                )
                THEN 7 WHEN (
                    drt.utilization.task_type = 'shift_end'
                )
                THEN 8
            END AS priority
            ,drt.shift.vehicle_license_plate AS license_plate
            ,drt.shift.scheduled_at AS scheduled_at
        FROM
            drt.utilization JOIN drt.shift
                ON drt.utilization.shift_id = drt.shift.id
        WHERE
            drt.shift.scheduled_at IN ('2020-05-20 12:00')
)
,timeline AS (
    SELECT
            priority_utilization.organization_id AS organization_id
            ,priority_utilization.region_id AS region_id
            ,priority_utilization.operating_period_id AS operating_period_id
            ,priority_utilization.shift_id AS shift_id
            ,priority_utilization.bookings_in_shuttle AS bookings_in_shuttle
            ,priority_utilization.task_type AS task_type
            ,priority_utilization.task_id AS task_id
            ,priority_utilization.start_date AS start_date
            ,priority_utilization.priority AS priority
            ,priority_utilization.license_plate AS license_plate
            ,priority_utilization.scheduled_at AS scheduled_at
            ,LEAD (
                priority_utilization.task_type
                ,1
            ) OVER (
                PARTITION BY priority_utilization.shift_id
            ORDER BY
                priority_utilization.start_date
                ,priority_utilization.priority
                ,priority_utilization.task_id
            ) AS next_task_type
            ,LEAD (
                priority_utilization.start_date
                ,1
            ) OVER (
                PARTITION BY priority_utilization.shift_id
            ORDER BY
                priority_utilization.start_date
                ,priority_utilization.priority
                ,priority_utilization.task_id
            ) AS end_date
        FROM
            priority_utilization
)
,occupancy_transitions AS (
    SELECT
            timeline.organization_id AS organization_id
            ,timeline.region_id AS region_id
            ,timeline.operating_period_id AS operating_period_id
            ,timeline.shift_id AS shift_id
            ,timeline.bookings_in_shuttle AS bookings_in_shuttle
            ,timeline.task_type AS task_type
            ,timeline.task_id AS task_id
            ,timeline.start_date AS start_date
            ,timeline.priority AS priority
            ,timeline.license_plate AS license_plate
            ,timeline.scheduled_at AS scheduled_at
            ,timeline.next_task_type AS next_task_type
            ,timeline.end_date AS end_date
            ,'break' AS status
        FROM
            timeline
        WHERE
            timeline.task_type = 'break_start'
    UNION
    (
        SELECT
                timeline.organization_id
                ,timeline.region_id
                ,timeline.operating_period_id
                ,timeline.shift_id
                ,timeline.bookings_in_shuttle
                ,timeline.task_type
                ,timeline.task_id
                ,timeline.start_date
                ,timeline.priority
                ,timeline.license_plate
                ,timeline.scheduled_at
                ,timeline.next_task_type
                ,timeline.end_date
                ,'empty' AS status
            FROM
                timeline
            WHERE
                timeline.bookings_in_shuttle = 0
        UNION
        SELECT
                timeline.organization_id
                ,timeline.region_id
                ,timeline.operating_period_id
                ,timeline.shift_id
                ,timeline.bookings_in_shuttle
                ,timeline.task_type
                ,timeline.task_id
                ,timeline.start_date
                ,timeline.priority
                ,timeline.license_plate
                ,timeline.scheduled_at
                ,timeline.next_task_type
                ,timeline.end_date
                ,'occupied' AS status
            FROM
                timeline
            WHERE
                timeline.bookings_in_shuttle > 0
    )
)
,prev_statuses AS (
    SELECT
            occupancy_transitions.organization_id
            ,occupancy_transitions.region_id
            ,occupancy_transitions.operating_period_id
            ,occupancy_transitions.shift_id
            ,occupancy_transitions.bookings_in_shuttle
            ,occupancy_transitions.task_type
            ,occupancy_transitions.task_id
            ,occupancy_transitions.start_date
            ,occupancy_transitions.priority
            ,occupancy_transitions.license_plate
            ,occupancy_transitions.scheduled_at
            ,occupancy_transitions.next_task_type
            ,occupancy_transitions.end_date
            ,occupancy_transitions.status
            ,LAG (status) OVER (
                PARTITION BY shift_id
            ORDER BY
                start_date
                ,priority
            ) AS prev_status
        FROM
            occupancy_transitions
        ORDER BY
            occupancy_transitions.start_date
)
,islands AS (
    SELECT
            *
            ,SUM (
                CASE
                    WHEN prev_status = status
                    THEN 0
                    ELSE 1
                END
            ) OVER (
                PARTITION BY shift_id
                ,status
            ORDER BY
                start_date
                ,priority ROWS BETWEEN unbounded preceding AND CURRENT row
            ) AS group_id
        FROM
            prev_statuses
        ORDER BY
            start_date
            ,priority
) SELECT
        organization_id
        ,region_id
        ,operating_period_id
        ,shift_id
        ,license_plate
        ,status
        ,MIN (start_date) AS start_date
        ,MAX (end_date) AS end_date
        ,scheduled_at
    FROM
        islands
    GROUP BY
        organization_id
        ,region_id
        ,operating_period_id
        ,shift_id
        ,license_plate
        ,group_id
        ,status
        ,scheduled_at
    ORDER BY
        MIN (start_date)
        ,MIN (end_date)

occupancy_status_using_utilization.sql

WITH utilization_w_priority AS (
    SELECT
            *
            ,CASE
                WHEN task_type = 'shift_start'
                THEN 1 WHEN task_type = 'op_period_start'
                THEN 2 WHEN task_type = 'dropoff'
                THEN 3 WHEN task_type = 'break_start'
                THEN 4 WHEN task_type = 'break_end'
                THEN 5 WHEN task_type = 'pickup'
                THEN 6 WHEN task_type = 'op_period_end'
                THEN 7 WHEN task_type = 'shift_end'
                THEN 8
            END AS priority
            ,LEAD (
                task_type
                ,1
            ) OVER (
                PARTITION BY shift_id
            ORDER BY
                state_start_datetime
                ,priority
            ) AS next_task_type
            ,LEAD (
                state_start_datetime
                ,1
            ) OVER (
                PARTITION BY shift_id
            ORDER BY
                state_start_datetime
                ,priority
            ) AS next_task_datetime,
            s.vehicle_license_plate AS license_plate,
            s.scheduled_at AS scheduled_at
        FROM
            drt.utilization 
            JOIN drt.shift AS s
        ON
            s.id = drt.utilization.shift_id
        WHERE s.scheduled_at BETWEEN '2020-05-20' AND '2020-05-22'
)
    SELECT
            *
        FROM
            (
                SELECT
                        organization_id
                        ,region_id
                        ,shift_id
                        ,'break' AS status
                        ,state_start_datetime AS start_date
                        ,next_task_datetime AS end_date
                        ,license_plate
                        ,scheduled_at
                    FROM
                        utilization_w_priority
                    WHERE
                        task_type = 'break_start'
                UNION
                SELECT
                        organization_id
                        ,region_id
                        ,shift_id
                        ,'occupied' AS status
                        ,state_start_datetime AS start_date
                        ,next_task_datetime AS end_date
                        ,license_plate
                        ,scheduled_at
                    FROM
                        utilization_w_priority
                    WHERE
                        bookings_in_shuttle > 0
                UNION
                SELECT
                        organization_id
                        ,region_id
                        ,shift_id
                        ,'empty' AS status
                        ,state_start_datetime AS start_date
                        ,next_task_datetime AS end_date
                        ,license_plate
                        ,scheduled_at
                    FROM
                        utilization_w_priority
                    WHERE
                        bookings_in_shuttle = 0
            )
        ORDER BY
            start_date