CODEWITHSUNDEEP
sqlsql-server-2017
user2974951
user2974951

Reputation: 10375

SQL Aggregation and cumulative summation

I have a user table which looks something like this

enter image description here

There are many users with unique id's, the table is first ordered by users and then by datetime. Each instance is either a training or a test session, test sessions have additional arguments, most importantly score.

I would like to aggregate the rows of this table, so that the time of all the training sessions before a test session are summed and only one instance is returned with the cumulative training time and the test score. Every additional test before a new training session is just a repeat instance with the new test score. This cumulative time summation continues until a new user (user id), when it is reset.

The resulting table would look something like this

enter image description here

Is there an SQL command that can achieve this? (self-join)?
The query doesn't have to be overly optimized, simple solutions are welcome.

Upvotes: 0

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Assign a group using a cumulative sum of the tests. Then aggregate to get the summaries you want within each group:

select grp, user_id, sum(time), max(case when task = 'test' then time end) as test_time, max(score)
from (select t.*,
             sum(case when task = 'test' then 1 else 0 end) over (partition by user_id order by datetime) as grp
      from t
     ) t
group by grp, user_id;

Upvotes: 1

Related Questions