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.

![[pii_email_da536d41ce343ebf2a5f] Strategies For The Entrepreneurially Challenged [pii_email_da536d41ce343ebf2a5f] Strategies For The Entrepreneurially Challenged](https://cdn.images.express.co.uk/img/dynamic/59/590x/secondary/hotmail-login-how-to-log-in-old-hotmail-account-access-email-2320527.jpg?r=1611040981204)
