I have the following tasks table:
x
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
Advertisement
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;