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 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
human
Find the average and maximum rating of all reviews.
assistant
SELECT avg(rating) , max(rating) FROM review
human
Find the highest rank of all reviews.
assistant
SELECT min(rank) FROM review
human
Find the number of items that did not receive any review.
assistant
SELECT count(*) FROM item WHERE i_id NOT IN (SELECT i_id FROM review)
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 number of reviews.
assistant
SELECT COUNT( * ) FROM review ;