SQL: High score progression by time

Question

Given a list of scores achieved in a game and the times at which they occurred, I'd like to retrieve a list of each score that was the highest when it was achieved and the time at which it did so: essentially, a high score progression. Consider this table (ordered by timestamp, with each score that was better than all previous scores marked with an asterisk):

Score	Timestamp	Notes
5*	00:12 2022-08-09
6*	01:15 2022-08-09
2	01:30 2022-08-09	worse than current high score of 6, so no asterisk
10*	02:01 2022-08-09
7	03:45 2022-08-09	worse than current high score of 10
12*	03:50 2022-08-09
1*	00:05 2022-08-10	note different day!
6*	01:01 2022-08-10

I'd like a query which returns only the asterisked scores (that is: only the score that was better than all previous scores), for a particular day, with their associated timestamp. So, if I run the query for 2022-08-09, the results should be:

Best Score	Timestamp
5	00:12
6	01:15
10	02:01
12	03:50

If this requires dialect-specific SQL, then the database engine in question is SQLite.

Answer 1

Assuming that your timestamps have the proper format YYYY-MM-dd HH:MM, you can use MAX() window function to check if the current score in every row is the current high score:

WITH cte AS (
  SELECT *, Score = MAX(Score) OVER (ORDER BY Timestamp) flag
  FROM tablename
  WHERE date(Timestamp) = '2022-08-09'
)
SELECT Score, time(Timestamp) Timestamp, Notes
FROM cte
WHERE flag;

See the demo.