count the number of people who are suffering in a condition for a period of four to

A ‘successful’ student who at least once in the current month correctly done 10 exercises in an hour.

If I follow you correctly, you can use window functions and a range frame:

select count(distinct student_id) cnt_successful_students
from (
    select s.*,
        count(*) filter(where is_correct) over(
            partition by student_id 
            order by timest
            range between interval '1 hour' preceding and current row
        ) cnt
    from students s
    where timest >= date_trunc('month', current_date) 
      and timest <  date_trunc('month', current_date) + interval '1 month'
) s
where cnt >= 10

The subquery filters on the current month. For each row, the window function count how many successful exercice the same student took during the last hour. The outer query then filters on students that reached the threshold at least once.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top