Getting the first and the last id grouped by the date and the user-CodePudding

I have two tables, jobs and users

The example structure from jobs is

id	created_at
444	2022-12-12 08:00:00
333	2022-12-12 09:00:00
222	2022-12-12 10:00:00
555	2022-12-12 07:00:00
111	2022-12-12 12:00:00
888	2022-12-12 08:00:00

and users

id	user_id	job_id
1	2	111
2	1	222
3	1	333
4	1	444
5	2	555
6	2	888

I need to get the first and last job id for each day for each user in the same row. So the result should look something like this.

user_id	date	first_id	last_id
1	2022-12-12	444	222
2	2022-12-12	555	111

CodePudding user response：

select  distinct u.user_id
       ,date(created_at)                                                                        as date
       ,first_value(j.id) over(partition by user_id, date(created_at) order by created_at)      as first_id
       ,first_value(j.id) over(partition by user_id, date(created_at) order by created_at desc) as last_id
from   jobs j join users u on u.job_id = j.id

user_id	date	first_id	last_id
2	2022-12-12	555	111
1	2022-12-12	444	222

Fiddle