id    |       timestamp       |    status
-------------------------------------------
A     |   2018-01-01 03:00:00 |     GOOD
A     |   2018-01-01 04:00:00 |     BAD
A     |   2018-01-01 05:00:00 |     GOOD
A     |   2018-01-01 09:00:00 |     BAD
A     |   2018-01-01 09:15:00 |     BAD
A     |   2018-01-01 13:00:00 |     GOOD
A     |   2018-01-01 14:00:00 |     GOOD
B     |   2018-02-01 09:00:00 |     GOOD
B     |   2018-02-01 10:00:00 |     BAD

id    |       timestamp       |    status    | bad_status_count
----------------------------------------------------------------
A     |   2018-01-01 03:00:00 |     GOOD     |       0
A     |   2018-01-01 04:00:00 |     BAD      |       1
A     |   2018-01-01 05:00:00 |     GOOD     |       1
A     |   2018-01-01 09:00:00 |     BAD      |       1
A     |   2018-01-01 09:15:00 |     BAD      |       2
A     |   2018-01-01 13:00:00 |     GOOD     |       0
A     |   2018-01-01 14:00:00 |     GOOD     |       0
B     |   2018-02-01 09:00:00 |     GOOD     |       0
B     |   2018-02-01 10:00:00 |     BAD      |       1

SELECT
  id,
  timestamp,
  status
  count(status) over(partition by id order by timestamp range between interval '3' hour and current_row) as bad_status_count
from table

select t.*,
       sum(case when status = 'Bad' then 1 else 0 end) over
           (partition by id
            order by hours
            range between -3 and current row
           ) as bad_status
from (select t.*,
             date_diff(hour, '2000-01-01', timestamp) as hours
      from t
     ) t;