How to filter on duration based on timestamp values

Question

I want to filter records based on on the time interval but I have timestamps. I have a table 'df' with the following column names:

id - int time - timestamp correctness - boolean subject - text

Every student (id) completes tasks on particular subject (subject). The system assigns "True" value in column "correctness" if the assignment is completed correctly and "False" if not. The time (time) when the student completes the task is also saved by the system.

I need to write an sql query that counts all students who completed 20 tasks successfully within an hour during March 2020. I need to count those who completed the tasks during the hour from the actual time they started and actual time the finished.

Thanks in advance!

Answer 1

You could use window functions and a range frame:

select distinct id
from (
    select 
        t.*, 
        count(*) filter(where correctness) over(
            partition by id 
            order by time 
            range between interval '1 hour' preceding and current row
        ) cnt
    from mytable t
    where time >= date '2020-03-01' and time < date '2020-04-01'
) t
where cnt > 20

The window function counts how many tasks where successfully performed by the same user within the last hour; you can then use this information to filter the resultset.

This gives you a list of users that satisfy the condition. If you want the count of such user, then replace select distinct id with select count(distinct id).