The Over in Which Each T20 Innings Passed 100 Runs

Hard
468.

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 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

ColumnType
match_idint
innings_noint
over_noint
runsint
wicketsint

Primary key (together): 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.

Example

The rows must come back in the order shown.

OverSummary

match_idinnings_noover_norunswickets
90111140
90112220
9011391
90114180
90115160
90116211
9011770
90118120
9012161
9012290
5 more rows

Output

match_idinnings_noover_noscore_after_over
90116100
90214100
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
match_idint
innings_noint
over_noint
runsint
wicketsint

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_idinnings_noover_norunswickets
90111140
90112220
9011391
90114180
90115160
90116211
9011770
90118120
9012161
9012290
9012332
90211300
90212300
90213250
90214151

Output

match_idinnings_noover_noscore_after_over
90116100
90214100

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_no

Another 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_no

Another 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 →