Design relationships (tables of messages and agents) and write an SQL query that returns the top 5 agents whose responses are liked by users. The reaction field takes values -1, 0, 1.

To store messages and agents, you can design two tables:

agents

• agent_id (PK)
• name

messages

• message_id (PK)
• agent_id (FK)
• content
• reaction (INT, takes values -1, 0, 1)

SQL query to get the top 5 agents with the highest average reaction value (i.e., whose responses are most liked by users):

SELECT a.agent_id, a.name, AVG(m.reaction) AS avg_reaction
FROM agents a
JOIN messages m ON a.agent_id = m.agent_id
GROUP BY a.agent_id, a.name
ORDER BY avg_reaction DESC
LIMIT 5;

This query calculates the average reaction value for each agent's messages and sorts them in descending order, returning the top 5.