Rapido hard RANGE BETWEENWindow FrameSUM OVERCTE

For every row, compute earnings_3day = the sum of settled earnings over that day and the two calendar days before it, using SUM(earnings) OVER (PARTITION BY captain_name ORDER BY julianday(shift_date) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW). Then return captain_name and best_3day_earnings (the MAX of earnings_3day) per captain, ordered by best_3day_earnings descending. A captain with no settled earnings at all should show NULL.

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

📖 Story Rapido · Captain Streaks & Earnings

Rapido's weekend-surge planners want to know each captain's best 3-day earning stretch, to set realistic incentive targets. 'Three days' here means three calendar days — the current day plus the two days before it — not 'the last three shifts', because a captain who took a day off didn't earn anything that day. The obvious window function uses ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, which quietly reaches back across days off. Earnings that haven't settled yet are NULL, and SUM skips them; a captain with nothing settled in any window should come out as NULL ('no settled earnings yet'), not 0.

🎯 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

See each captain's daily earnings and the gaps between shifts

query.sql

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

💡 Explanation

Bhavana's dates go 04-01, then 04-03 — she took 04-02 off — and later skip 04-07; Imran jumps from 04-01 straight to 04-04, a two-day gap; those gaps are where 'last 3 rows' and 'last 3 days' stop meaning the same thing.
Bhavana's 04-01 shift paid 1900, the biggest single day in the table (a run of long airport trips), which makes it very visible when a window wrongly drags that day into a stretch it doesn't belong to.
Imran's 04-04 earnings is NULL (payout pending), and all three of Jaya's days are NULL — so some 3-day windows will contain no settled money at all.
Deepa worked five days straight with no gaps and no NULLs, so for her every possible window definition gives the same answer; she's the control case to compare the others against.

The trap: ROWS counts shifts, not calendar days

query.sql

SELECT captain_name, shift_date, earnings,
       SUM(earnings) OVER (PARTITION BY captain_name ORDER BY shift_date
                           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rows_3day,
       SUM(earnings) OVER (PARTITION BY captain_name ORDER BY julianday(shift_date)
                           RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS range_3day
FROM rapido_shifts
ORDER BY captain_name, shift_date;

💡 Explanation

On Bhavana's 04-04 row, rows_3day = 3960 because ROWS just takes the two previous rows (04-01 and 04-03), even though 04-01 is three days back; range_3day = 2060 because RANGE keeps only rows whose julianday is within 2 of the current day (04-02 to 04-04).
Imran on 04-04 shows the gap even more clearly: rows_3day = 700, money he earned on 04-01, while range_3day = NULL, because the only row in his real 3-day window is today's unsettled payout and SUM over only NULLs is NULL.
RANGE with a numeric offset needs a numeric ORDER BY — julianday(shift_date) turns '2026-04-04' into a day number so '2 PRECEDING' means two days; with ORDER BY shift_date on TEXT, SQLite runs without an error but the frame shrinks to just the current day, another silent wrong answer.
For Deepa and Arun the two columns match on every row because neither has a missing calendar day; ROWS and RANGE only disagree where there's a gap — which is exactly why this bug survives testing on 'clean' captains.

Fix it: RANGE over julianday, then each captain's best stretch

query.sql

WITH rolling AS (
  SELECT captain_name, shift_date,
         SUM(earnings) OVER (PARTITION BY captain_name ORDER BY julianday(shift_date)
                             RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS earnings_3day
  FROM rapido_shifts
)
SELECT captain_name, MAX(earnings_3day) AS best_3day_earnings
FROM rolling
GROUP BY captain_name
ORDER BY best_3day_earnings DESC;

💡 Explanation

Result: Deepa 3900, Bhavana 3310, Arun 2650, Imran 1830, Chetan 1570, Jaya NULL — with ROWS instead of RANGE, Bhavana would wrongly lead with 3960, a 'three-day' total that actually spans four calendar days.
Bhavana's real best is 04-03 to 04-05 (1100 + 960 + 1250 = 3310), and Deepa's is 04-01 to 04-03 (1300 + 1420 + 1180 = 3900), so the honest leaderboard puts Deepa first.
The window function has to run inside the CTE first and MAX afterwards, because a window function can't be nested inside an aggregate in the same SELECT — the CTE gives each row its earnings_3day, then GROUP BY collapses them to one best value per captain.
Jaya's NULL is a true answer: MAX ignores NULLs, and every one of her windows is NULL, so the result is NULL — 'nothing settled yet'; wrapping it in COALESCE(..., 0) would claim she earned nothing in three days of riding.

▶ Practice this live ← Browse all questions