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 number of reviews.
assistant
SELECT count(*) FROM review
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 name and id of the item with the highest average rating.
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.rating) DESC LIMIT 1
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
Find the highest rank of all reviews.
assistant
SELECT min(rank) FROM review
human
Find the average and maximum rating of all reviews.
assistant
SELECT avg ( rating ) , MAX( rating ) FROM review ;