reusing subquery in postgresql

Question

I have a query which works just fine, however I use the same subquery twice in it. I would like to know if there is a way to reuse this subquery. Here is the query:

SELECT DISTINCT homeworks.*
FROM homeworks
INNER JOIN homework_messages ON homeworks.id = homework_messages.homework_id
WHERE homework_messages.message_type = 'submit'
  AND homework_messages.created_at::date <= (CURRENT_DATE - '5 days'::interval)
  AND (
    (
      SELECT MAX(homework_messages.created_at) FROM homework_messages
      WHERE homework_messages.homework_id = homeworks.id AND homework_messages.message_type != 'submit'
    ) < homework_messages.created_at 
    OR 
    (
      SELECT MAX(homework_messages.created_at) FROM homework_messages
      WHERE homework_messages.homework_id = homeworks.id AND homework_messages.message_type != 'submit'
    ) IS NULL
  )
GROUP BY homeworks.id

As you can see I repeat these lines twice:

(
  SELECT MAX(homework_messages.created_at) FROM homework_messages
  WHERE homework_messages.homework_id = homeworks.id AND homework_messages.message_type != 'submit'
)

I tried to use WITH clause and it seems it doesn't suit this problem, or maybe I'm using it wrong. Anyway thanks for you help.

Answer 1

Here is a cross join lateral suggestion. I have not delved into your business logic and just tried to keep it equivalent.

SELECT DISTINCT homeworks.*
FROM homeworks
INNER JOIN homework_messages ON homeworks.id = homework_messages.homework_id
cross join lateral ( -- your subquery follows
  SELECT MAX(created_at) as created_at FROM homework_messages
  WHERE homework_id = homeworks.id AND message_type != 'submit'
) as lat
WHERE homework_messages.message_type = 'submit'
  AND homework_messages.created_at::date <= (CURRENT_DATE - '5 days'::interval)
  AND (lat.created_at < homework_messages.created_at OR lat.created_at IS NULL)
GROUP BY homeworks.id;