Exclude first record associated with each parent record in Postgres

Question

There are 2 tables, users and job_experiences.

I want to return a list of all job_experiences except the first associated with each user.

users

id 
---
1
2
3

job_experiences

id | start_date | user_id
--------------------------
1  | 201001     | 1
2  | 201201     | 1
3  | 201506     | 1
4  | 200901     | 2
5  | 201005     | 2

Desired result

id | start_date | user_id
--------------------------
2  | 201201     | 1
3  | 201506     | 1
5  | 201005     | 2

Current query

select 
   * 
from job_experiences
order by start_date asc
offset 1

But this doesn't work as it would need to apply the offset to each user individually.

Answer 1

You can do this with a lateral join:

select je.*
from users u cross join lateral
     (select je.*
      from job_experiences je
      where u.id = je.user_id
      order by id
      offset 1  -- all except the first
     ) je;

For performance, an index on job_experiences(user_id, id) is recommended.