SQL to find records with last 3 same status

I have the following tasks table:

id  type   submit_time   status

I need to run a query that returns only the types which their last 3 tasks finished with status “FAILED”.

How can I do this? Should I use a window function?

Thanks

Answer

You can use aggregation and filtering after using row_number() to get the last three rows:

select type
from (select t.*,
             row_number() over (partition by type order by submit_time desc) as seqnum
      from t
     ) t
where seqnum <= 3
group by type
having min(status) = max(status) and min(status) = 'FAILED' and
       count(*) = 3;

Actually, a slightly simpler method is:

select type
from (select t.*,
             row_number() over (partition by type order by submit_time desc) as seqnum
      from t
     ) t
where seqnum <= 3 and status = 'FAILED'
group by type
having count(*) = 3;