Rapido mid CUME_DISTIIFHAVINGCTE

Compute each captain's total_earnings = SUM(earnings), keeping only captains whose total is not NULL (use HAVING). Then return captain_name, total_earnings, cume_dist = ROUND(CUME_DIST() OVER (ORDER BY total_earnings), 2), and bonus_tier = IIF(CUME_DIST() OVER (ORDER BY total_earnings) > 0.6, 'Bonus', 'No bonus'). Order by total_earnings descending.

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

📖 Story Rapido · Captain Streaks & Earnings

Rapido pays a weekly performance bonus to captains whose total settled earnings put them in the top 40% of the fleet. The analytics team uses CUME_DIST(), which tells you what fraction of captains earned the same or less than a given captain; anyone above 0.6 gets the bonus. The catch is Jaya, a new captain whose payouts are all still pending: her total is NULL — 'not settled yet', not zero — and she shouldn't be ranked at all this week. Leaving her in the ranking doesn't just affect her row; it quietly changes everyone else's percentile too.

🎯 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

Total up each captain's settled earnings

query.sql

SELECT captain_name,
       COUNT(*) AS shifts,
       COUNT(earnings) AS settled_shifts,
       SUM(earnings) AS total_earnings
FROM rapido_shifts
GROUP BY captain_name
ORDER BY total_earnings DESC;

💡 Explanation

Bhavana leads with 6290, then Deepa 6240 and Arun 6160 — three captains within 130 of each other, so a small change in how percentiles are computed can move someone across the bonus line.
COUNT(*) vs COUNT(earnings) shows how many shifts are still unsettled: Arun 7 vs 6, Bhavana 6 vs 5, Imran 4 vs 3 — their SUM covers only settled days, which is what a 'settled earnings' bonus should use.
Jaya has 3 shifts but 0 settled, so SUM(earnings) is NULL — SUM over only NULLs returns NULL, not 0 — and that NULL honestly means 'we don't know her total yet'.
ORDER BY total_earnings DESC puts Jaya's NULL last here, because SQLite sorts NULL as smaller than every number; the CUME_DIST in the next step sorts ascending, which puts that same NULL first.

The trap: ranking with the NULL total still in the set

query.sql

WITH totals AS (
  SELECT captain_name, SUM(earnings) AS total_earnings
  FROM rapido_shifts
  GROUP BY captain_name
)
SELECT captain_name, total_earnings,
       ROUND(CUME_DIST() OVER (ORDER BY total_earnings), 2) AS cume_dist,
       IIF(CUME_DIST() OVER (ORDER BY total_earnings) > 0.6, 'Bonus', 'No bonus') AS bonus_tier
FROM totals
ORDER BY total_earnings DESC;

💡 Explanation

CUME_DIST() = (number of rows with a value less than or equal to this row's) / (total rows in the window); with 6 captains the possible values are 0.17, 0.33, 0.5, 0.67, 0.83 and 1.
In ascending order NULL comes first, so Jaya is treated as the lowest earner and gets 0.17 — the query ranks a captain whose earnings are unknown as if they were known to be the smallest.
Worse, she fills a seat in the denominator and pushes everyone up one notch: Arun lands on 4/6 = 0.67, which is above 0.6, so he gets 'Bonus' — three captains out of five real ones, 60% of the fleet, in a 'top 40%' bonus.
IIF(condition, a, b) is SQLite's shorthand for CASE WHEN condition THEN a ELSE b END; it isn't causing the bug — it faithfully applies the 0.6 rule to a cume_dist that's already wrong.

Fix it: drop the NULL total with HAVING before ranking

query.sql

WITH totals AS (
  SELECT captain_name, SUM(earnings) AS total_earnings
  FROM rapido_shifts
  GROUP BY captain_name
  HAVING SUM(earnings) IS NOT NULL
)
SELECT captain_name, total_earnings,
       ROUND(CUME_DIST() OVER (ORDER BY total_earnings), 2) AS cume_dist,
       IIF(CUME_DIST() OVER (ORDER BY total_earnings) > 0.6, 'Bonus', 'No bonus') AS bonus_tier
FROM totals
ORDER BY total_earnings DESC;

💡 Explanation

HAVING SUM(earnings) IS NOT NULL removes Jaya after grouping — it has to be HAVING, not WHERE, because the NULL we're filtering is the group's total, which doesn't exist until GROUP BY has run.
With 5 captains the steps become 0.2, 0.4, 0.6, 0.8 and 1: Bhavana 1.0 and Deepa 0.8 get 'Bonus', while Arun lands on exactly 0.6, and since the rule is > 0.6, not >= 0.6, he correctly misses the top-40% line.
Two out of five is exactly 40%, so the bonus list now matches the policy; in the trap version one extra captain was paid only because of a row that should never have been ranked.
COALESCE(SUM(earnings), 0) would not fix this: Jaya would still occupy a seat in the denominator (now as 0 instead of NULL), Arun would still get 0.67 and the bonus — the only correct fix is to leave the unknown total out of the ranking entirely.

▶ Practice this live ← Browse all questions