I need to sort the users based on their most recent response_date for questionnaires from a specific company. This should include users who haven’t responded to any questionnaires from that company.
For example, if I want to sort users based on their latest response to questionnaires from company_id = 1, the result should look like this:
hey ava! have you tried using a lateral join? it might be a cool way to tackle this. something like:
SELECT u.id, u.username, r.latest_response
FROM users u
LEFT JOIN LATERAL (
SELECT MAX(response_date) AS latest_response
FROM responses
WHERE user_id = u.id
AND questionnaire_id IN (SELECT id FROM questionnaires WHERE company_id = 1)
) r ON true
ORDER BY r.latest_response DESC NULLS FIRST;
hey ava, i’ve dealt with similar stuff before. you’ll need a left join to include all users, even those without responses. then use a subquery or cte to get the latest response date per user. something like:
SELECT u.id, u.username, MAX(r.response_date) AS latest_response
FROM users u
left join responses r on u.id = r.user_id
left join questionnaires q on r.questionnaire_id = q.id
WHERE q.company_id = 1 OR q.company_id IS NULL
GROUP BY u.id, u.username
ORDER BY latest_response DESC NULLS FIRST;
To achieve the desired result, you’ll need to use a combination of LEFT JOINs and a subquery. Here’s an efficient SQL query that should work:
SELECT u.id, u.username, lr.latest_response
FROM users u
LEFT JOIN (
SELECT r.user_id, MAX(r.response_date) AS latest_response
FROM responses r
JOIN questionnaires q ON r.questionnaire_id = q.id
WHERE q.company_id = 1
GROUP BY r.user_id
) lr ON u.id = lr.user_id
ORDER BY lr.latest_response DESC NULLS FIRST, u.id;
This query first creates a subquery to find the latest response date for each user for the specific company. Then it LEFT JOINs this result with the users table to include all users, even those without responses. The ORDER BY clause ensures the correct sorting, including NULL values at the top.