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 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 the number of reviews.
assistant
SELECT count(*) FROM review
human
Find the highest rank of all reviews.
assistant
SELECT min(rank) FROM review
human
For each user, return the name and the average rating of reviews given by them.
assistant
SELECT T1.name , avg(T2.rating) FROM useracct AS T1 JOIN review AS T2 ON T1.u_id = T2.u_id GROUP BY T2.u_id
human
For each user, find their name and the number of reviews written by them.
assistant
SELECT T1.name , count(*) FROM useracct AS T1 JOIN review AS T2 ON T1.u_id = T2.u_id GROUP BY T2.u_id
human
Find the name of the user who gives the most reviews.
assistant
SELECT T1.name FROM useracct AS T1 JOIN review AS T2 ON T1.u_id = T2.u_id GROUP BY T2.u_id ORDER BY COUNT( * ) DESC LIMIT 1 ;