How do I calculate a streak of data and show the data that corresponds to it on MySQL?

I'm currently working on a project with NBA stats and I want to check a player's longest streak of his highest amount of points on MySQL. I'm open to a 5-10 game streak to simplify it.

For example: Kevin Durant scores 30, 32, 35, 36, 41 in 5 consecutive games and that's his highest one, how would I go about extracting the game stats, points, the length of the streak AND showing the total amount of points during this streak all together.

I've tried using multiple CTEs with window functions, but I confuse myself as I'm doing it. Is there an easy or simple way to do this at all?

• Please provide `SHOW CREATE TABLE` and indicate what indicates a "streak". Commented Jul 20, 2023 at 18:01

To find the longest streak of games where a player scored the highest amount of points, you can follow these steps:

First, you need to identify the games where the player scored the highest amount of points among all players in that game. You can do this by using a subquery with the MAX () function and a self-join. For example, if your table is called stats and has columns game_id, player_id, and points, you can use this query: SELECT s1.* FROM stats s1 JOIN (SELECT game_id, MAX (points) AS max_points FROM stats GROUP BY game_id) s2 ON s1.game_id = s2.game_id AND s1.points = s2.max_points WHERE s1.player_id = ‘Kevin Durant’;

This will return the games where Kevin Durant scored the highest amount of points among all players in that game.

Next, you need to assign a sequential number to each game based on the game date. You can do this by using the ROW_NUMBER () function over a partition ordered by game_date. For example, you can use this query: SELECT , ROW_NUMBER () OVER (ORDER BY game_date) AS rn FROM (SELECT s1. FROM stats s1 JOIN (SELECT game_id, MAX (points) AS max_points FROM stats GROUP BY game_id) s2 ON s1.game_id = s2.game_id AND s1.points = s2.max_points WHERE s1.player_id = ‘Kevin Durant’) t;

This will return the games where Kevin Durant scored the highest amount of points among all players in that game, along with a row number for each game.

Then, you need to calculate the difference between the row number and the game date for each game. This will help you identify consecutive games with the same difference value. You can do this by using the DATEDIFF () function and subtracting the row number from it. For example, you can use this query: SELECT *, DATEDIFF (game_date, rn) AS diff FROM (SELECT , ROW_NUMBER () OVER (ORDER BY game_date) AS rn FROM (SELECT s1. FROM stats s1 JOIN (SELECT game_id, MAX (points) AS max_points FROM stats GROUP BY game_id) s2 ON s1.game_id = s2.game_id AND s1.points = s2.max_points WHERE s1.player_id = ‘Kevin Durant’) t) u;

This will return the games where Kevin Durant scored the highest amount of points among all players in that game, along with a row number and a difference value for each game.

Finally, you need to find the longest streak of consecutive games with the same difference value. You can do this by using another subquery with the COUNT () function and a GROUP BY clause. You can also use the MAX () function to get the maximum length of the streak. For example, you can use this query: SELECT MAX (streak_length) AS longest_streak FROM (SELECT diff, COUNT (*) AS streak_length FROM (SELECT *, DATEDIFF (game_date, rn) AS diff FROM (SELECT , ROW_NUMBER () OVER (ORDER BY game_date) AS rn FROM (SELECT s1. FROM stats s1 JOIN (SELECT game_id, MAX (points) AS max_points FROM stats GROUP BY game_id) s2 ON s1.game_id = s2.game_id AND s1.points = s2.max_points WHERE s1.player_id = ‘Kevin Durant’) t) u) v GROUP BY diff) w;

This will return the longest streak of games where Kevin Durant scored the highest amount of points among all players in that game.