The Over in Which Each T20 Innings Passed 100 Runs
The scoring feed stores the runs of every over. The broadcaster's graphic shows when each innings reached 100: the first over at whose end the innings total…
- Difficulty: Hard
- Topics: Window Functions, Subqueries
- Dialect: MySQL
- Problem: #468
Problem statement
The scoring feed stores the runs of every over. The broadcaster's graphic shows when each innings reached 100: the first over at whose end the innings total was 100 or more.
For every innings that reached 100, return the columns match_id, innings_no, over_no (that over) and score_after_over (the innings total at the end of it). Innings that never reached 100 are left out. Order the rows by match_id, then innings_no.
Tables
Table: OverSummary
| Column | Type |
|---|---|
| match_id | int |
| innings_no | int |
| over_no | int |
| runs | int |
| wickets | int |
Primary key: match_id, innings_no, over_no.
One row per over bowled, over_no 1–20; an innings can end before its 20th over, and its overs are numbered without gaps.
Examples
Example 1
OverSummary
| match_id | innings_no | over_no | runs | wickets |
|---|---|---|---|---|
| 901 | 1 | 1 | 14 | 0 |
| 901 | 1 | 2 | 22 | 0 |
| 901 | 1 | 3 | 9 | 1 |
| 901 | 1 | 4 | 18 | 0 |
| 901 | 1 | 5 | 16 | 0 |
| 901 | 1 | 6 | 21 | 1 |
| 901 | 1 | 7 | 7 | 0 |
| 901 | 1 | 8 | 12 | 0 |
| 901 | 2 | 1 | 6 | 1 |
| 901 | 2 | 2 | 9 | 0 |
| 901 | 2 | 3 | 3 | 2 |
| 902 | 1 | 1 | 30 | 0 |
| 902 | 1 | 2 | 30 | 0 |
| 902 | 1 | 3 | 25 | 0 |
| 902 | 1 | 4 | 15 | 1 |
Output
| match_id | innings_no | over_no | score_after_over |
|---|---|---|---|
| 901 | 1 | 6 | 100 |
| 902 | 1 | 4 | 100 |
How to solve The Over in Which Each T20 Innings Passed 100 Runs
The total at the end of each over is a running total: SUM(runs) OVER (PARTITION BY match_id, innings_no ORDER BY over_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The partition restarts the sum for every innings, and the explicit ROWS frame makes the sum stop at the current over (over numbers are unique within an innings, so RANGE would agree, but saying ROWS removes any doubt).
From the running totals, the over that passed 100 is the earliest one with total >= 100: filter, number what is left per innings by over, and keep number 1. Because runs are never negative the running total only rises, so there is an even simpler test: the total after the over is at least 100 and the total before it (total - runs) is below 100 — exactly one over per innings satisfies that, which is the second alternative. An innings ending on exactly 100 qualifies, and one that never got there has no row.
Without windows, correlated sums compute the totals before and after each over; that rescans the innings per over, quadratic in its length, while the window plan is one sort per innings.
Reference solution (MySQL)
WITH running AS (
SELECT match_id, innings_no, over_no,
SUM(runs) OVER (PARTITION BY match_id, innings_no ORDER BY over_no
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total
FROM OverSummary
), reached AS (
SELECT match_id, innings_no, over_no, total,
ROW_NUMBER() OVER (PARTITION BY match_id, innings_no ORDER BY over_no) AS rn
FROM running
WHERE total >= 100
)
SELECT match_id, innings_no, over_no, total AS score_after_over
FROM reached
WHERE rn = 1
ORDER BY match_id, innings_noAnother way
SELECT o.match_id, o.innings_no, o.over_no,
(SELECT SUM(p.runs) FROM OverSummary p WHERE p.match_id = o.match_id AND p.innings_no = o.innings_no AND p.over_no <= o.over_no) AS score_after_over
FROM OverSummary o
WHERE (SELECT SUM(p.runs) FROM OverSummary p WHERE p.match_id = o.match_id AND p.innings_no = o.innings_no AND p.over_no <= o.over_no) >= 100
AND (SELECT SUM(p.runs) FROM OverSummary p WHERE p.match_id = o.match_id AND p.innings_no = o.innings_no AND p.over_no < o.over_no) < 100
ORDER BY o.match_id, o.innings_noAnother way
WITH running AS (
SELECT match_id, innings_no, over_no, runs,
SUM(runs) OVER (PARTITION BY match_id, innings_no ORDER BY over_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total
FROM OverSummary
)
SELECT match_id, innings_no, over_no, total AS score_after_over
FROM running
WHERE total >= 100 AND total - runs < 100
ORDER BY match_id, innings_no← Net Run Rate of Every Team in the T20 League · Median Fantasy Points per Gameweek by Player Role →