Ordering Data Across Three Linked Database Tables

Data and Databases
1

I’m working with a database that has three tables: users, questionnaires, and responses. Here’s what they look like:

users
======
id, username

questionnaires
==============
id, company_id

responses
=========
id, user_id, questionnaire_id, response_text, response_date

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:

id | username | latest_response
---+----------+-----------------
4  | User4    | NULL
5  | User5    | NULL
3  | User3    | 2023-06-17
2  | User2    | 2023-06-18
1  | User1    | 2023-06-19

Users 4 and 5 haven’t responded to any questionnaires from company 1, but they’re still in the list.

What’s the best SQL query to get this result? I’m not sure how to handle the joins and sorting correctly. Any help would be great!

2

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;

what do you think? curious to hear if this helps!

3

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;

hope this helps!

4

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.