Rapido hard Gaps and IslandsROW_NUMBERdate() modifiersCTE

Treat a day as active when rides > 0 (regardless of whether earnings has settled). For every captain, find the longest streak of consecutive calendar days that are all active, using the gaps-and-islands technique: date(shift_date, '-' || ROW_NUMBER() ... || ' days') gives every day in the same unbroken run the same island key. Return captain_name and longest_streak, ordered by longest_streak descending, then captain_name.

Rapido · Captain Streaks & Earnings — practice this real-world SQL scenario live in your browser.

📖 Story Rapido · Captain Streaks & Earnings

Rapido is launching a 'Streak Star' badge for bike-taxi captains who ride on many consecutive days. The shifts table has one row per day a captain logged in: rides is how many trips they completed that day (0 means they were online but got no trips), and earnings is that day's payout. Payouts settle a day or two late, so a day with real rides can still have earnings = NULL — meaning 'payout not settled yet', not 'earned nothing'. Days a captain didn't log in at all simply have no row. The badge team needs each captain's longest run of consecutive calendar days with at least one ride.

🎯 Your Mission

📋 Table Structure

🗂 rapido_shifts

captain_name TEXT Arun

city TEXT Bengaluru

shift_date TEXT 2026-04-01

rides INTEGER 12

earnings INTEGER 1450

⚡ Step-by-Step Walkthrough

Look at every captain's shifts day by day

query.sql

SELECT captain_name, shift_date, rides, earnings
FROM rapido_shifts
ORDER BY captain_name, shift_date;

💡 Explanation

29 rows across 6 captains; a missing date means the captain never logged in — Bhavana has no row for 04-02 or 04-07, and Imran has none for 04-02 or 04-03 — so a streak can break on a day that simply isn't in the table.
Two rows have rides = 0 (Arun on 04-05, Chetan on 04-04): they logged in but got no trips, so those days are in the table yet are not active, and they also break a streak.
Six rows have earnings = NULL with real rides — Arun 04-03, Bhavana 04-06, Imran 04-04, and all three of Jaya's days — because those payouts haven't settled yet; Jaya is a new captain and none of her payouts have come through.
That gives three different reasons a row can look 'empty': no row at all (logged out), rides = 0 (online, no trips) and earnings NULL (rides happened, money pending) — only the first two should break a streak.

The trap: defining 'active' as earnings > 0

query.sql

WITH active AS (
  SELECT captain_name, shift_date,
         date(shift_date, '-' || ROW_NUMBER() OVER (PARTITION BY captain_name ORDER BY shift_date) || ' days') AS island_key
  FROM rapido_shifts
  WHERE earnings > 0
),
streaks AS (
  SELECT captain_name, island_key, COUNT(*) AS streak_days
  FROM active
  GROUP BY captain_name, island_key
)
SELECT captain_name, MAX(streak_days) AS longest_streak
FROM streaks
GROUP BY captain_name
ORDER BY longest_streak DESC, captain_name;

💡 Explanation

The island trick: ROW_NUMBER counts 1, 2, 3... over a captain's active days, and subtracting that many days from each date gives a constant while the dates are consecutive (04-01 minus 1, 04-02 minus 2 and 04-03 minus 3 all land on 03-31) and jumps to a new value right after a gap.
The logic is correct, but the filter is not: earnings > 0 is NULL for an unsettled day, and WHERE keeps only rows that are TRUE, so every pending payout day drops out and is treated as if the captain had stayed home.
Arun's real four-day run (04-01 to 04-04) is cut in two by his NULL on 04-03, so he shows 2 instead of 4; Bhavana drops from 4 to 3 and Imran from 3 to 2 for the same reason.
Jaya disappears from the result completely — all three of her days are NULL, so not a single row survives the filter — and a captain who rode three days straight gets no badge at all, simply because her money is still in transit.

Fix it: define active by rides > 0 and measure each island

query.sql

WITH active AS (
  SELECT captain_name, shift_date,
         date(shift_date, '-' || ROW_NUMBER() OVER (PARTITION BY captain_name ORDER BY shift_date) || ' days') AS island_key
  FROM rapido_shifts
  WHERE rides > 0
),
streaks AS (
  SELECT captain_name, island_key, COUNT(*) AS streak_days
  FROM active
  GROUP BY captain_name, island_key
)
SELECT captain_name, MAX(streak_days) AS longest_streak
FROM streaks
GROUP BY captain_name
ORDER BY longest_streak DESC, captain_name;

💡 Explanation

rides is never NULL, so WHERE rides > 0 removes exactly the two zero-ride days and nothing else — the unsettled-payout days stay in, because whether money has arrived has nothing to do with whether the captain worked.
date(shift_date, '-' || n || ' days') builds the modifier text like '-3 days' on the fly; the zero-ride day and the logged-out days are both missing from the active set, so each one shifts the island key and starts a new run, exactly as a real break should.
Result: Deepa 5, Arun 4, Bhavana 4, Imran 3, Jaya 3, Chetan 2 — Arun's 04-01 to 04-04 run is whole again, and Jaya is back with a genuine 3-day streak.
Grouping by captain_name and island_key turns each island into one row with its length, and the outer MAX picks the longest per captain; Arun has two islands (4 days, then 2 days after his zero-ride 04-05) and only the 4 is reported.

▶ Practice this live ← Browse all questions