MySQL: How to track a players total kills throughout his career

I have a table structure with 4 essential columns:

1. match_id

2. date

3. player_id

4. kills (of a player in the current game)

The table will have data for many different player_ids, and what I want to calculate is how many kills each player has generated in total (from all matches he has ever played at that given point in time) after each game ordered by date asc.

So for instance if he gets 20 kills in his first match, his career sum is 20. If he gets 15 kills in the next match his career kill is 35. I would like a query that shows each of these total career kills after each match.

Previously I solved this by looping through all the rows in python and keeping track of all the players kills, but I assume there is a much better way for this.

Answer

Well, this kind of query can be easily achieved by windows function which finally introduced in MySQL 8.0 :

select 
  player_id , 
  match_id , 
  date , 
  kills ,
  sum(kills) over (partition by player_id order by date asc , match_id asc) as career_kills
from `match`;

Here we rely on the default setting of window frame when order by is present in over , which is equivalent to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

DB Fiddle