Filter and rank (using row_partition) with a filter inside the row_partition

user_14874462 0
Viewed 48

I have a table Jobs that stores a bunch of Jobs every User from *Users posts. Each Job has a status. My first goal is to identify the first completed (status = completed) job for each user. I was able to do so using:

SELECT
    user_id AS user_id,
    starts_time AS starts_time,
    id AS job_id
FROM (
    SELECT
        user_id,
        starts_time,
        id,
        --sort by starts time, and rank ascending
        Row_number() OVER (PARTITION BY User_id ORDER BY Starts_time ASC) AS Rn
    FROM
        jobs
    WHERE
        --status 2 is completed
        status = 2
    GROUP BY
        user_id,
        assignment_id,
        id ORDER BY
            user_id) AS jobs
WHERE
    rn = 1

Here is what it returns:

user_id   | starts_time             |  job_id |
-----------------------------------------------
 123      | 2016-04-18 14:30:00+00  |   1292  |
 124      | 2016-04-18 19:00:00+00  |   2389  |
 128      | 2016-04-16 13:00:00+00  |   3201  |

Just as some context, there are a lot of cases where a User's first job isn't a job with the status "completed". For example they'll post a list of jobs that go have any one of the following status' before they see a completed job: ("Unfilled", "Voided", "Cancelled")

For every user I want to establish which jobs came before that user saw their first completed job. I was hoping the query above would be a starting point, and from that I can just say return me any job for every user that has a starts_time preceding that of the first job completed

*Sorry if this is confusing, this is my first time posting for help on Stack Overflow, any constructive criticism is appreciated!

gaps-and-islands datetime postgresql sql
1 Answers

For every user I want to establish which jobs came before that user saw their first completed job.

For each user, you want all the records the first status "2". You can use window functions:

select *
from (
    select j.*,
        bool_or(status = 2) over(partition by user_id order by starts_time) as flag
    from jobs j
) t
where not flag

bool_or checks if the current row or any preceding row satisfies the condition.

If you want to retain the first status 2, then you can just change the over() clause of the window function to not consider the current row:

select *
from (
    select j.*,
        bool_or(status = 2) over(
            partition by user_id 
            order by starts_time rows between unbounded preceding and 1 preceding
        ) as flag
    from jobs j
) t
where flag is distinct from true
user_106767160
Related