Longest Winning Streak

Longest run of consecutive wins per player

Matches(player_id, match_day, result) with result ∈ {Win, Draw, Lose}. Report each player's longest streak of consecutive Wins. The earlier query's CASE WHEN se=0 THEN COUNT(se) ELSE COUNT(se)-1 double-counts/under-counts across groups and its traced table contradicted that rule. The reliable method is the two-row-numbers islands trick:

WITH numbered AS (
    SELECT player_id, match_day, result,
           ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY match_day)               AS rn_all,
           ROW_NUMBER() OVER (PARTITION BY player_id, result ORDER BY match_day)        AS rn_win
    FROM Matches
),
win_streaks AS (
    SELECT player_id, COUNT(*) AS streak_len
    FROM numbered
    WHERE result = 'Win'
    GROUP BY player_id, rn_all - rn_win        -- constant within one uninterrupted win run
)
SELECT p.player_id,
       COALESCE(MAX(w.streak_len), 0) AS longest_streak
FROM (SELECT DISTINCT player_id FROM Matches) p
LEFT JOIN win_streaks w ON w.player_id = p.player_id
GROUP BY p.player_id;

Why rn_all - rn_win isolates a streak

Number all of a player's matches by day (rn_all) and, separately, number only the wins (rn_win). Inside an uninterrupted run of wins, both counters advance by 1 each row, so their difference is constant — one "island". A draw or loss bumps rn_all but not rn_win, so when wins resume the difference changes and a new island begins. Counting rows per island gives each streak's length; MAX per player gives the longest. COALESCE(…, 0) covers players who never won.

Takeaways

• Two row-numbers (all rows vs only-wins); their difference is constant across a consecutive win run.
• LEFT JOIN from the distinct players + COALESCE(MAX,0) so no-win players report 0.

---

Re-authored for correctness for this guide (the prior CASE rule contradicted its own traced table). Pattern: gaps-and-islands streaks. See also: High School Attendance, Window Functions.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Longest Winning Streak

Why this concept exists (judgment chain)

Streaks over event sequences use two row numbers: rn_all over all matches and rn_result over wins only; rn_all−rn_win is constant on an uninterrupted Win run. MAX streak per player with COALESCE 0 covers players who never won. This generalizes to any status-coded sequence, not only sports.

Worked example with numbers or traced steps

Player matches by day: W,W,L,W,W,W
rn_all: 1..6; rn_win only on W rows.
Win islands: lengths 2 and 3 → longest 3.
Draw/Loss bumps rn_all only → difference changes → new island after resume.
Players with zero wins: LEFT JOIN from distinct players + COALESCE(MAX,0)=0.
Filter WHERE result='Win' before grouping by (player_id, rn_all-rn_win).

When NOT to use / named alternative

Use calendar islands (date−rn) when the problem is about calendar days of activity, not match sequence. Do not use recursive CTEs for simple linear streaks unless asked. Avoid CASE hacks that double-count across groups.

Failure / ops fingerprint

Off-by-one from including Draws in win partition. Ordering by wrong column (result not match_day) shuffles sequence. Ops: define whether matches sharing a day need secondary order; index (player_id, match_day).

Hostile-panel Q&As (model answers)

Q1. Why two ROW_NUMBERs?
Model answer: Their difference is invariant only while both advance together — pure win runs — and jumps after interruptions.

Q2. Calendar vs sequence islands?
Model answer: Active-users uses dates; winning streak uses ordered events where "consecutive" means adjacent matches, not calendar days.

Q3. How do no-win players appear?
Model answer: Drive from all player_ids LEFT JOIN win_streaks and COALESCE longest to 0.

I just read a lesson on **Longest Winning Streak** (Databases) and want to truly understand it. Explain Longest Winning Streak from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Longest Winning Streak** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Longest Winning Streak** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Longest Winning Streak** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.