There are two tables, I need to get a list of client_id whose last requests (title) have not been processed. We believe that if there was a call after the request was created (created_datetime), then it has been processed.
tasks | ||
---|---|---|
client_id | created_datetime | title |
calls | ||
---|---|---|
manager_id | client_id | call_datetime |
I think that something needs to be done with time, but I don't understand what.
select client_id, max(calls.call_datetime) - max(tasks.created_datetime) as time
from tasks join calls on tasks.client_id = calls.client_id
group by client_id, call_datetime
CodePudding user response:
with maxes as (select client_id, max(calls.call_datetime) latest_call
from calls
group by client_id)
select tasks.client_id, title
from tasks
inner join maxes on maxes.client_id = tasks.client_id
where created_datetime > maxes.latest_call
If I understood correctly, this should be it