Longest On-Crew Relay Streak

Problem

A courier network moves parcels by relay: each `relay_hops` row records one courier handing a parcel to another at time `hop_at` (format `HH:MM`). Every courier belongs to a crew. A hop is **on-crew** when the receiving courier is on the same crew as the sending courier; otherwise the parcel left the crew and the streak resets. For each crew, ordering that crew's hops by time, find the length of the longest unbroken run of consecutive on-crew hops. Return `crew_name` and `longest_streak` for every crew that has at least one on-crew hop, ordered by `crew_name`.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns crew_name, longest_streak.

Starter code (SQL)

SELECT *
FROM crews;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard