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
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 titles of items whose rating is higher than the average review rating of all items.
assistant
SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id = T2.i_id WHERE T2.rating > (SELECT avg(rating) FROM review)
human
Find the number of reviews.
assistant
SELECT count(*) FROM review
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 ;