I want to count certain Esports events rolling up results by tournament, tournament stage, last 5 and last 10 games, all matches.
I'd like to get a single query that I could put into materialised view - new events happen not too often, so even a few sec query would be perfectly fine for our db.
The picture of what I want to get:
Metrics are very different (tower kills, dragon kills, first tower kills, etc) and I would like avoid copy-paste as much as I could.
This logic could be easily implemented within application code, but I'd like to give the database a try.
Unfortunately, I don't have any working code yet, as the only solution I'm aware of today - create unions with kind of copy-pasted select statements.
I'm looking for something like GROUP BY ROLLUP tournamentId, {limit 5}, {limit 10}, but it seems there is no a very straightforward way to do this.
Any hints on how this could be implemented?
Thanks!