ArenaCricket Performance: Consecutive Matches Above AverageHard

Cricket Performance: Consecutive Matches Above Average

HardSQLPractice library · +30 XPwindow functionssqlstreaks

UptoSkills Arena is tracking cricket player statistics for the upcoming season. We need to identify players who have demonstrated consistent high performance by calculating the longest consecutive sequence of matches where they scored more than the season's average.

Tables:

matches (match_id INT PRIMARY KEY, season_year INT, match_date DATE)
player_stats (stat_id INT PRIMARY KEY, match_id INT, player_id INT, runs_scored INT)

Return the player_id and the max_consecutive_above_avg_matches for each player, representing the longest streak of matches where their runs_scored in that match was strictly greater than the average runs_scored for that season_year. If a player has no matches where they scored above the season average, their streak should be 0.

Order the results by player_id.

Tables

CREATE TABLE matches (
    match_id INTEGER PRIMARY KEY,
    season_year INTEGER NOT NULL,
    match_date DATE NOT NULL
);
CREATE TABLE player_stats (
    stat_id INTEGER PRIMARY KEY,
    match_id INTEGER REFERENCES matches(match_id),
    player_id INTEGER NOT NULL,
    runs_scored INTEGER NOT NULL
);

Example data

INSERT INTO matches (match_id, season_year, match_date) VALUES
(1, 2023, '2023-01-10'),
(2, 2023, '2023-01-15'),
(3, 2023, '2023-01-20'),
(4, 2023, '2023-02-05'),
(5, 2023, '2023-02-10'),
(6, 2024, '2024-03-01'),
(7, 2024, '2024-03-05'),
(8, 2024, '2024-03-10'),
(9, 2024, '2024-03-15'),
(10, 2024, '2024-03-20'),
(11, 2024, '2024-03-25');

INSERT INTO player_stats (stat_id, match_id, player_id, runs_scored)
VALUES
(1, 1, 101, 50), -- 2023 avg = (50+60+70+40+30)/5 = 48
(2, 2, 101, 75), -- P101: 75 > 48 (streak 1), 40 < 48, 30 < 48
(3, 3, 101, 80), -- P101: 80 > 48 (streak 2)
(4, 4, 101, 40),
(5, 5, 101, 30),
(6, 6, 101, 90), -- 2024 avg = (90+100+80+120+110)/5 = 100
(7, 7, 101, 110),
(8, 8, 101, 80),
(9, 9, 101, 130),
(10, 10, 101, 140),
(11, 11, 101, 95),
(12, 1, 102, 30), -- P102: 30 < 48
(13, 2, 102, 45), -- P102: 45 < 48
(14, 3, 102, 50), -- P102: 50 > 48 (streak 1)
(15, 4, 102, 60), -- P102: 60 > 48 (streak 2)
(16, 5, 102, 70), -- P102: 70 > 48 (streak 3)
(17, 6, 102, 80), -- P102: 80 < 100
(18, 7, 102, 110), -- P102: 110 > 100 (streak 1)
(19, 8, 102, 120); -- P102: 120 > 100 (streak 2)

Your query is also checked on hidden datasets with different data.

Cricket Performance: Consecutive Matches Above Average

Tables

Example data

Hints

Similar problems