Postgresql querying records where the previous record already in completed status

based on the following data in the database:

i would like to query for all the records where status = null, but the status of the previous step_no is "done" group by the order.

appreciate some guidance on what sql can do so.

>Solution :

You can use lag() function like below:

with cte as (
select 
*, 
lag(status,1) over ( order by "order", "step") "previous" 
from demo
)
select 
"order", 
step, 
status 
from cte where status is null and previous='done'

DEMO