human
Schema:
item (i_id, title)
review (a_id, u_id, i_id, rating, rank)
useracct (u_id, name)
trust (source_u_id, target_u_id, trust)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the names of users who did not leave any review.
assistant
SELECT name FROM useracct WHERE u_id NOT IN (SELECT u_id FROM review)
human
Find the name and id of the good with the highest average rank.
assistant
SELECT T1.title , T1.i_id FROM item AS T1 JOIN review AS T2 ON T1.i_id = T2.i_id GROUP BY T2.i_id ORDER BY avg(T2.rank) DESC LIMIT 1
human
Find the name of the user who gave the highest rating.
assistant
SELECT T1.name FROM useracct AS T1 JOIN review AS T2 ON T1.u_id = T2.u_id ORDER BY T2.rating DESC LIMIT 1
human
Find each target user's name and average trust score.
assistant
SELECT T1.name , avg(trust) FROM useracct AS T1 JOIN trust AS T2 ON T1.u_id = T2.target_u_id GROUP BY T2.target_u_id
human
Find the name of the source user with the highest average trust score.
assistant
SELECT T1.name FROM useracct AS T1 JOIN trust AS T2 ON T1.u_id = T2.source_u_id GROUP BY T2.source_u_id ORDER BY avg(trust) DESC LIMIT 1
human
Find the name of the target user with the lowest trust score.
assistant
SELECT T1.name FROM useracct AS T1 JOIN trust AS T2 ON T1.u_id = T2.target_u_id ORDER BY trust LIMIT 1 ;